SQL SERVER – Introduction to Force Index Query Hints – Index Hint

This article, I will start with disclaimer instead of having it at the end of article.

“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”

When any query is ran SQL Server Engine determines which index has to be used. SQL Server makes uses Index which has lowest cost based on performance. Index which is the best for performance is automatically used. There are some instances when Database Developer is best judge of the index used. DBA can direct SQL Server which index to be used to execute query.

Example 1 : SQL Server using default index

USE AdventureWorks
GO
SELECT *
FROM Person.Contact
GO


Example 2: SQL Server using forced index

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


Example 3: SQL Server using different index for different/same tables

USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO


Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Introduction to Force Index Query Hints – Index Hint

  1. Hi Dave,
    Can you please explain in what case do i need to use the Hints.
    seeing the execution plan how will i know if I have to use the Hints or no.
    Thank You in Advance.

    Thanks
    Venkat

    Like

  2. You do not want to see “Table Scan” in the execution plan. If you see it and feel like that the index that you have created is not picked up by SQL Server, then try telling the SQL Server by using the HINT.

    Like

  3. hello,
    I have an issue with a application which selects data from my table. The table has ID and time as PK. Every hour, I delete ALL IDs’ data by the oldest hour. For this, I created a time,ID index to facilitate this. May be I should just have only time. anyways, this is what it is.
    The issue is this: VEry frequenctly (sometimes within 2-3 days) the application gets very very very slow in retrieving data. When I drop the new IX I created, it works really fast. It’s search criteria is the ID, for a given time.
    My thought is soehow the app is trying to use my Index (which I created to facilitate deletes) instead of the clustered Ix of the PK.
    Any thoughts ? Very frustrated.

    Like

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