Index hint on query

Question

Index hint on query

Igor Micev

SSC-Dedicated

Points: 33110
More actions
November 14, 2013 at 5:22 am

#286237

Hi All
There is a query which uses 5 columns. It's a part of a very big query which is not viewable to post here.
SELECT TOP 1 docnum
FROM IdentityDocs idd1 with (index (IIdentityDocsDocIssued))
WHERE c2.ClientId=idd1.ClientId AND idd1.DocStatusId=1 AND (idd1.DocTypeId=7 OR idd1.DocTypeId=8)
ORDER BY idd1.DocIssued DESC
It wasn't covered with index, and there was Index spool and Sort operation in the execution plan. Since we couldn't change the query, we had to implement a new index.
CREATE NONCLUSTERED INDEX [IIdentityDocsDocIssued] ON [dbo].[IdentityDocs]
(
[ClientId] ASC,
[DocIssued] ASC,
[DocStatusId] ASC,
[DocTypeId] ASC
)
INCLUDE ( [DocNum])
Now that we have the new index, I want to know if it's better to have the index hint (with (index (IIdentityDocsDocIssued)) ) or not to have it in the query.
In case we remove the hint, and if the statistics get out of date, will the Query optimizer use the new index? Currently the statistics are updated and there is no difference in the execution plan with and without the index hint.
There is a Clustered index on Clients table, and before the new index "IIdentityDocsDocIssued" was created, the optimizer was using the clustered index introducing index spool and sort in the execution plan of the sp.
I want to know your thinking for using or not the hint.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Remove the hint unless there is no other way to get the optimiser to choose the index that you know to be best, you know why the index you're hinting is best, why the optimiser isn't picking it and you will test on a regular basis to ensure it's still the best index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

GilaMonster (11/14/2013)
Remove the hint unless there is no other way to get the optimiser to choose the index that you know to be best, you know why the index you're hinting is best, why the optimiser isn't picking it and you will test on a regular basis to ensure it's still the best index.

Definitely agree with Gail (as is almost always the case). I will add that you should be doing scheduled statistics updates. You need to modify 20% of the rows for a particular stat to get auto-refreshed, which is very often WAY too long!!

I HIGHLY recommend Ola.Hallengren.com for all of your SQL Server maintenance needs!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 3

Thank you both!

I know that usage of hints should be avoided as much as possible. My thoughts were if in case the stats got out of date the option with the index hint is better.

Finally I managed to reconstruct a scenario when the stats are not updated and the query optimizer got to use the newly created index again regardless of the outdated stats.

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com