Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index hint on query Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 5:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 3,017, Visits: 3,105
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
www.seavus.com
Post #1514229
Posted Thursday, November 14, 2013 5:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1514232
Posted Thursday, November 14, 2013 8:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 4,410, Visits: 6,281
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 at GMail
Post #1514308
Posted Thursday, November 14, 2013 12:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 3,017, Visits: 3,105
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
www.seavus.com
Post #1514450
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse