Database Reference
In-Depth Information
Summary
An ideal clustered index is narrow, static, and unique. Moreover, it optimizes most important queries against the
table and reduces fragmentation. It is often impossible to design a clustered index that satisfies all of the five design
guidelines provided in this chapter. You should analyze the system, business requirements, and workload, and choose
the most efficient clustered indexes—even when they violate some of those guidelines.
Ever-increasing clustered indexes usually have low fragmentation because the data is inserted at the end of the
table. A good example of such indexes are identities, sequences, and ever-incrementing date/time values. While such
indexes may be a good choice for catalog entities with thousands or even millions of rows, you should consider other
options in the case of huge tables, with a high rate of inserts.
Uniqueidentifier columns are rarely good candidates for indexes due to their high fragmentation. You should
consider implementing composite indexes or byte-masks rather than uniqueidentifiers in these cases when you need
to have uniqueness across multiple database servers.
SQL Server rarely uses index intersection, especially in an OLTP environment. It is usually beneficial to have
a small set of wide composite nonclustered indexes with included columns rather than a large set of narrow one-
column indexes.
In OLTP systems, you should create a minimally required set of indexes to avoid index update overhead. In Data
Warehouse systems, the number of indexes greatly depends on the data refresh strategy.
It is important to drop unused and inefficient indexes and perform index consolidation before adding new
indexes to the system. This simplifies the optimization process and reduces data modification overhead. SQL Server
provides index usage statistics with
sys.dm_db_index_usage_stats
and
sys.dm_db_index_operation_stats
DMOs.
You can use SQL Server Profiler, Extended Events, and DMVs, such as
sys.dm_exec_query_stats
and
sys.dm_
exec_procedure_stats
, to detect inefficient queries. Moreover, there are plenty of tools that can help in monitoring
and index tuning. With all that being said, you should always consider query and database schema refactoring as an
option. It often leads to much better performance improvements when compared to index tuning by itself.
