Databases Reference
In-Depth Information
Chapter 4: Index Selection
Index selection is one of the most important techniques used in query optimization. By
using the right indexes, SQL Server can speed up your queries and dramatically improve
the performance of your applications. In this chapter, I will show you how SQL Server
selects indexes, how you can use this knowledge to provide better indexes, and how you
can verify your execution plans to make sure these indexes are correctly used.
This chapter also includes sections about the Database Engine Tuning Advisor and the
Missing Indexes feature, which will show how you can use the Query Optimizer itself to
provide index tuning recommendations. However, it is important to emphasize that, no
matter what index recommendations these tools give, it is ultimately up to the database
administrator or developer to do their own index analysis, and finally decide which of
these recommendations to implement. Also, since we'll be covering these tools mainly
from the point of view of the Query Optimizer, you should use Books Online to obtain
more in-depth information regarding using these features.
Finally, the
sys.dm_db_index_usage_stats
DMV will be introduced as a tool to
identify existing indexes which your queries may not be using. Indexes that are not being
used will provide no benefit to your databases, but
will
use valuable disk space and slow
your update operations, and so they should be considered for removal.
Introduction
As mentioned in
Chapter 2
,
The Execution Engine
, SQL Server can use indexes to perform
seek and scan operations. Indexes can be used to speed up the execution of a query by
quickly finding records without performing table scans; by delivering all the columns
requested by the query without accessing the base table (i.e. covering the query, which
