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 (https://blog.sqlauthority.com)
8 Comments. Leave new
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
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.
Você é o cara! (The Best of The Best!!)
Hi Pinal,
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
For above created index how do i list it using Select statement.
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.
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.
“recommended to use query hints by experienced developers”
And now, watch me write an article with zero commentary, to confuse newbies!
Hi, Pinal. I seem to recall that there was a way to use XML to force a query to use a particular index if I don’t have the ability to edit the query. I can’t find the reference, though. Can you help me remember what these were called? Thanks!