|
Using the Right Indexes for Optimal Performance
Query optimization is a complex game with its own rules.
Let’s look at three examples to discover when SQL Server Query Optimizer uses clustered indexes and non-clustered indexes to retrieve data and when to use the primary key (PK) to influence performance.
Example 1: Default Index Usage
Let’s look first at Query Optimizer’s default use of indexes.
Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (relative to the batch).
In this example, no query hint is specified, so Query Optimizer can use any index it wants to use, which results in optimal performance.
Notice that even though ContactID (the primary key [PK] of the Contact table) is retrieved in Query 1, Query Optimizer does not use a primary key clustered index; instead, it uses a non-clustered index. In Query 2, on the other hand, Query Optimizer uses a clustered index on PK where all columns (*) are retrieved.
This may be surprising. It is a common belief that when PK columns are used and no other condition or joins are used, Query Optimizer will use a PK clustered index to return the results of a SELECT statement. However, this is not always true.
Example 2 : Forcing a Primary Key (PK) Clustered Index
Let’s see how performance changes when a primary key clustered index is used to
retrieve data.
Query 1’s query cost (relative to the batch) is equals to Query 2’s query cost (also relative to the batch).
In this example, we are using a primary key clustered index to retrieve data. The same execution plan is created whether we retrieve only one column or all the columns.
Example 3: Forcing Non-Clustered Index
Now consider two queries in which a non-clustered index is used to retrieve data.
Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (also relative to the batch).
In this example, we are using a non-clustered index to retrieve data. From the execution plan, it is very clear that retrieving only one column is much faster than retrieving all the columns from the Contact table. When all the columns from a table are selected, a PK clustered index is clearly the best option.
Author Credits
This wiki article was adapted from a blog post by Pinal Dave.
Pinalkumar Dave is Microsoft SQL Server MVP and author of over 900 SQL Server articles. He has over six years experience as Sr. Project Manager and Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NET) and ColdFusion Advanced MX Certifications.
His online presences include:
Related Reading
For more information about index performance tuning, check out these articles:
|
