Jeff Schwartz gives us some guidance on finding good candidate indexes for tuning:
Index usage and tuning metrics became available on SQL Server 2005 with Dynamic Management Views and Functions, which will be discussed later. However, the meanings and significance of index DMV/DMF metrics are still not well understood by many despite only minor additions over the years. Specifically, the following list contains a synopsis of the topics that the author has observed to be the most salient index-related issues:
Identifying
- Queries that need an index to function efficiently
- Which indices, if any, are used by a query (and how they are used, e.g., randomly or sequentially)
- Tables (and their indices) that merit evaluation and potential adjustment
- Indices that duplicate functionality of others
Understanding when
- A new index is truly needed and what improvement can be anticipated
- An index can be deleted without harming performance
- An index should be adjusted or added despite the query not producing any missing index warnings
Understanding why having too many indices results in
- Inserts and updates taking too long and/or creating blocking
- Suboptimal query plans being generated because there are too many index choices
Knowing Database Engine Tuning Advisor (DTA) pros & cons
Jeff starts with the basics of indexes, followed by some general strategy. This promises to be the first of several posts on the topic.