SQL SERVER – Interesting Observation – Use of Index and Execution Plan

Previously I wrote article about SQL SERVER – Interesting Observation about Order of Resultset without ORDER BY and I have received tremendous response from my readers by emails and comments. Readers demanded that I should have written little more for the same subject. As I really liked the subject myself very much, I have decided to write more about the same again. Those readers who have not read my previous article, I request them to go over my previous article one time before reading this article as that will give them history. Read my previous article here.

Let us see three examples which describes how Query Optimizer works with Indexes when Index are forced on query.

Example 1 : Default Index usage

USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact
SELECT *
FROM Person.Contact
GO

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 there is no query hint specified. Due to not using any query hint SQL Server Query Optimizer can use any index it want to use which gives optimal performance. One thing which is worth noticing is that even though ContactID which is Primary Key (PD) of the table is only retrieved in first query it does not use Primary Key Clustered Index instead it uses non clustered index.

This is surprising to many users. It is common belief that in SELECT statement columns which are PK are used and no other condition or joins are used it will use PK clustered index to return the results. This is not always true. In second query where all columns (*) are retrieved it uses clustered index on PK.

Example 2 : Forcing Primary Key Clustered Index

USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact WITH (INDEX(PK_Contact_ContactID))
SELECT *
FROM Person.Contact WITH (INDEX(PK_Contact_ContactID))
GO

Query 1’s query cost (relative to the batch) is equals than Query 2’s query cost (relative to the batch).

In this example we are using Primary Key Clustered Index to retrieve data. From actual execution plan it is very clear that retrieving only one column or all the column creates same execution plan.

Example 3 : Forcing Non-Clustered Index

USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))
SELECT *
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))
GO

Query 1’s query cost (relative to the batch) is way lower than Query 2’s query cost (relative to the batch).

In this example we are using non clustered Index to retrieve data. From actual execution plan it is very clear that retrieving only one column is quite faster than retrieving all the columns from table. We can clearly observe that when all the columns from table are selected PK clustered index is the best option.

Summary:
Query Optimization is complex game and it has its own rules. From above example we have discovered that Query Optimizer does not use clustered index to retrieve data, sometime non clustered index provides optimal performance for retrieving Primary Key. When all the rows and columns are selected Primary Key should be used to select data as it provides optimal performance.

There can be many different queries can be created using above example, I suggest my readers to participate here and provide their opinion about this article. I totally understand I have not gone much deep to explain the concept but I am sure that I have given good enough idea about what I am trying to convey.

I encourage reader’s participation/idea/blog/comments/emails which are related to this article.

Reference : Pinal Dave (https://blog.sqlauthority.com)