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

indexhint1 SQL SERVER   Introduction to Force Index Query Hints   Index Hint

Example 2: SQL Server using forced index

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

indexhint2 SQL SERVER   Introduction to Force Index Query Hints   Index Hint

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

indexhint3 SQL SERVER   Introduction to Force Index Query Hints   Index Hint

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

6 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

  4. SK: I would check the indexes to see levels of fragmentation to see if you have to rebuild or reorganize the indexes. My guess is that the issue is the indexes are being negatively affected by the deletes. In some of my old databases, I would add an Inactive data field as NULL smalldatetime so that if I wanted to consider it deleted, i would just set that value and have an index on that field. At the end of the week, I could purge them all out when I’m doing my maintenance and index checks. Keep in mind that this is without seeing your tables or indexes, but if you wanted to try it, you could add this and then even use a view to make it so nothing else can see those deleted entries.

    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