SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index hint on query


Index hint on query

Author
Message
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5948 Visits: 5084
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89141 Visits: 45284
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12754 Visits: 8564
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
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5948 Visits: 5084
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search