SQL Server Index Hint

SQL Server query optimizer decides which index to use, when a query is requested. SQL Server optimizer is a cost based optimizer so it picks the index which has low cost based on performance. When we design an index for a query, optimizer will utilize the defined index to generate the plan for requested query if the index has low cost based on performance of query. But for some special scenarios or very rare scenarios where SQL Server will not use the most appropriate index for a given query and impact query performance.

If SQL Server optimizer is not using the expected index and you wanted your query to use that specific index, then it can be done using Index hint. Be aware most of the time Query Optimizer chooses the right index out of available indexes.

I usually use Index hint while designing index to compare between indexes to know which one is best for my query.

Demo 1: Use Index hint to force SQL server optimizer to use a specific Index

USE [AdventureWorks]
GO
SELECT ProductID, ReviewerName, [Comments]
FROM [Production].[ProductReview] WITH (INDEX = IX_ProductReview_ProductID_Name)

demo1

Demo 2: Use Index hint to force SQL server optimizer to use a specific Index

USE [AdventureWorks]
GO
SELECT PR.ProductID, PR.ReviewerName, PR.Comments, PP.Name
FROM [Production].[ProductReview] PR
WITH (INDEX = IX_ProductReview_ProductID_Name)
INNER JOIN [Production].[Product] PP
WITH (INDEX = [AK_Product_Name]) ON PR.ProductID = PP.ProductID
demo2
Multiple Index Hint

 

NOTE: Hints can prevent the query optimizer from choosing a better execution plan

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *