SQLcommitted

Committed with SQL

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.

Demo1: 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)

 

image

 

Demo2: Use multiple Index hint for multiple tables

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

image

 

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

 

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

About these ads

January 1, 2014 - Posted by | Index | , , ,

2 Comments »

  1. nice one Sir ,
    Thanks

    Comment by Avinash Reddy Munnangi | January 2, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: