Indexing Best Practices in SQL Server 2008

26 Oct 2010

For best results, adhere to the following best practices when creating and optimizing indices in SQL Server 2008 (caveats apply):

Create indices on columns that join tables, including primary and foreign keys to improve query performance
Place clustered indices on primary key columns that consist of integer data with an Identity constraint to keep the size of the index small
Use nonclustered indices to facilitate search arguments within queries
Use composite nonclustered indices to cover the most common queries
Use the CREATE STATISTICS statement on columns that are not defined first in a composite index to generate more efficient execution plans
Ensure that the table data and the index data is backed up at the same time, if your indices are on a separate filegroups to the table data
Drop any indices that are no longer used. Use the Database Engine Tuning Advisor tool to help identify unused indices
Use the FILLFACTOR option to optimise performance by reducing page splits
Use the DBCC SHOWCONTIG statement to assess the level of fragmentation of databases
Rebuild indices if fragmentation is greater than 30 percent, reorganize indices if fragmentation is below 30 percent or there is little time to rebuild indices
Use the DROP_EXISTING option to rebuild indices quickly
Remember to recompile stored procedures to take advantage of indices that have been rebuilt
Use spatial indices to facilitate searches on geospatial data
Ensure a primary key exists on the table on which a spatial index is defined
Monitor queries that retrieve spatial data to ensure that the spatial index is being used. If not, add index hints to queries to force the use of a spatial index