Database Reference
In-Depth Information
Although the performance aspect of indexes is explained in detail in Chapters 8, 11, and 12, I'll reiterate a short
list of recommendations for easy reference here:
•
Choose narrow columns for indexes.
•
Ensure that the selectivity of the data in the candidate column is very high (that is, the column
must have a large number of unique values).
•
Prefer columns with the integer data type (or variants of the integer data type). Also, avoid
indexes on columns with string data types such as
VARCHAR
.
Consider listing columns having higher selectivity first in a multicolumn index.
•
•
Use the
INCLUDE
list in an index as a way to make an index cover the index key structure
without changing that structure. Do this by adding columns to the key, which enables you to
avoid expensive lookup operations.
•
When deciding which columns to index, pay extra attention to the queries'
WHERE
clauses and
JOIN
criteria columns and
HAVING
clause. These can serve as the entry points into the tables,
especially if a
WHERE
clause criterion on a column filters the data on a highly selective value or
constant. Such a clause can make the column a prime candidate for an index.
•
When choosing the type of an index (clustered or nonclustered), keep in mind the advantages
and disadvantages of clustered and nonclustered index types.
Be extra careful when designing a clustered index because every nonclustered index on the table depends on the
clustered index. Therefore, follow these recommendations when designing and implementing clustered indexes:
•
Keep the clustered indexes as narrow as possible. You don't want to widen all your
nonclustered indexes by having a wide clustered index.
•
Create the clustered index first and then create the nonclustered indexes on the table.
•
If required, rebuild a clustered index in a single step using the
DROP_EXISTING = {ON|OFF}
command in the
CREATE INDEX
command. You don't want to rebuild all the nonclustered
indexes on the table twice: once when the clustered index is dropped and again when the
clustered index is re-created.
•
Do not create a clustered index on a frequently updated column. If you do so, the nonclustered
indexes on the table will create additional load by remaining in sync with the clustered index
key values.
•
Where applicable, such as when you need aggregations across large data sets, consider using
columnstore indexes.
To keep track of the indexes you've created and determine others that you need to create, you should take
advantage of the dynamic management views that SQL Server 2014 makes available to you. By checking the data
in
sys.dm_db_index_usage_stats
on a regular basis—say once a week or so—you can determine which of your
indexes are actually being used and which are redundant. Indexes that are not contributing to your queries to help
you improve performance are just a drain on the system. They require both more disk space and additional I/O to
maintain the data inside the index as the data in the table changes. On the other hand, querying
sys.dm_db_missing_
indexes_details
will show potential indexes deemed missing by the system and even suggest
INCLUDE
columns. You
can access the DMV
sys.dm_db_missing_indexes_groups_ stats
to see aggregate information about the number
of times queries are called that could have benefited from a particular group of indexes. Just remember to test these
suggestions thoroughly and don't assume that they will be correct. All these suggestions are just that: suggestions.
All these tips can be combined to give you an optimal method for maintaining the indexes in your system over the
long term.
