Best Practices on Managing SQL Server 2008 Indexes

Best Practices on Managing SQL Server 2008 Indexes

■ Managing and optimizing indexes is an ongoing process because performance can suffer both with the lack of indexes and poorly implemented and managed indexes.
■ Create clustered indexes on columns that are frequently used and are lean data types. For example, the clustered index could be an identity column. However, it is not uncommon to create the clustered index on a column used in WHERE, ORDER BY, and GROUP BY clauses and in joins.
■ Nonclustered indexes are dependent on the clustered index. Be careful when disabling a clustered index because the nonclustered indexes are also automatically disabled.
■ A table can have only one clustered index. Take care to ensure that the key length of the clustered index doesn’t exceed 900 bytes.
■ Use nonclustered indexes to cover frequent or costly queries that are not covered by the clustered index. As many as 999 nonclustered indexes can be created on a table.
■ Take care to ensure the key length of the nonclustered index doesn’t exceed 900 bytes. Add columns as nonkey include columns to place additional data into the index.
■ In order to alleviate performance degradation caused by fragmentation, maintain indexes by either updating or rebuilding indexes.
■ If the tempdb is stored in a different filegroup or on a different set of physical disks, use the option to sort the index in the tempdb for a performance improvement.
■ Leverage the Enterprise Edition’s online indexing feature to eliminate downtime when removing fragmentation from indexes.
■ When deleting all indexes on a table, remember to remove the clustered index last. If the clustered index is removed first, any nonclustered indexes are unnecessarily maintained as part of the removal process.
■ Spatial indexes should be created to increase the response time of spatial queries.
■ Leverage Filtered indexes to improve performance and reduce maintenance costs on columns that have nulls.

From book "Microsoft SQL Server 2008 Management and Administration".