SQL is ignoring my index hint and using a clustered index.

alt text The part of the proc which is using the most IO is an insert into a variable.

Part of this gets information from a table where an index hint has been included.

Looking at the execution plan, SQL is ignoring the index hint and choosing to use the clustered index on the table, which is not efficient.

The fragmentation on the table <=8%. I have come across this before and rebuilding the indexes does work, but this will require downtime as the table is in constant use and SQL is not enterpirse edition.

Is there any way to force SQL to use the index hint provided to it?

1.png (174.2 kB)

Plan.queryanalysis (649.3 kB)

By DaliMorse 1 asked Jul 15, 2014 at 09:40 AM

(comments are locked)

Edward Norris Jul 15, 2014 at 11:41 AM

Based on the plan, I see that there are a lot of places where small tweaks can improve performance (tweaking an include here or there). But, I think we need to see the actual query structure of the SP to understand how the hint is being used. In general, index hints should not be used, so looking at the actual query will be helpful in understanding what it is attempting to do to cause it to not use the index. One of the more common reasons that an index is not used is: Statistics are out of date, so update those. If the NC doesn't cover the required values (either through the indexed fields or with the includes) and the number of seeks + key lookups > scan, then the clustered is often used. Also, although not as good as a rebuild, you can do an index organize. It is an online operation that can help in such situations.

Aaron Bertrand ♦ Jul 15, 2014 at 11:49 AM

Also if you upload a non-anonymized plan, could you make sure it's an actual plan? Much more information there than in just the estimated.

DaliMorse Jul 15, 2014 at 11:53 AM

I know the overall fix for this would be a re-work of the proc and that a temporary fix would be to reorganise/rebuild the index and update the stats before a recompile, I was more interested in getting a bit more information about why SQL ignores index hints and if there is any way to force the hint?

I know that when I do a reorg/rebuild the query will pickup the correct index, but I would have thought that the hint would have been enough to make it do this without having to do anything to the stats.

Any general information on this would be useful as I am not having issues with this particular proc, I have just seen this before and wanted to know why SQL does what it does, not the 'fix' for it.

Thanks

Edward Norris Jul 15, 2014 at 12:06 PM

There are several reasons that a hint can be ignored. If you are using an indexed view, then you need to add the (Expand View) hint. You may also want to give it an extra kick by using with (forceseek,index(xx)) on the index instead of just the general index (also a forcescan option, but still trying to figure out why!).

DaliMorse Jul 15, 2014 at 12:13 PM

That's great Edward.

I'll have a read up on these! I agree that the hints should only be used as a last resort, but it still seems odd that hints can be ignored in the execution plan for a more inefficient index.

Thanks for your help, Dali

Aaron Bertrand ♦ Jul 15, 2014 at 01:36 PM

Also, is IX_PlannedJob_ActioningLocationID a filtered index?

DaliMorse Jul 15, 2014 at 01:49 PM

Hi Aaron,

The db is SQL2005 and I don't believe filtered indexes are available until 2008.

Sorry I didn't mention that before.

1 answer:

Also, in looking at the query (I must have missed the image, or it was added later), what you have highlighted is the key lookup to get all of the fields that are not in the index (that is hidden behind the popup). I am guessing that the index behind the pop-up is the one in the hint. To get rid of the highlighted item, alter the index used behind the pop-up with: includes(EarliestActionTime,IsActive,LatestActionTime, PlannedJobID,PlannedJobStatusID). That will remove the key lookup completely.

By Edward Norris 96 answered Jul 15, 2014 at 12:13 PM

(comments are locked)

DaliMorse Jul 15, 2014 at 01:55 PM

I agree with Ed that the index could be amended to work better with the query. Unfortunately it is not as simple as amending the index in this case as it will be used by a number of other procs which could be made worse by amending it.

Re-factoring the proc is the best solution but, as previously mentioned, it is just interesting to understand why SQL ignores hints.

In this particular case, I re-organised the index which resolved the issue. However, as the fragmentation of the index was only around the 8% mark, it is interesting that SQL decided to ignore the index hint and use another index of similar levels of fragmentation (0.03% difference in fragmentation between indexes).

Edward Norris Jul 15, 2014 at 02:11 PM

That 8% fragmentation may be enough to cause drive thrashing, which would also be considered in the final query plan. set statistics io on and see how the physical reads are. SQL reads by extents, so depending on how the fragmentation existed, there could have been a bit of extra reads. Of course, you should always have your data drive read 64k blocks and make sure it's aligned... Typically, adding includes to your NC indexes has minimal impact to existing items. It makes the leaf larger, so it does write a few extra fields, but that impact is nearly invisible.

Your answer

ask a question

Where to?

We are Moving!

Follow this question

Topics:

query plan x692
plan explorer upload x503
index x31
clustered x1
hint x1

asked: Jul 15, 2014 at 09:40 AM

Seen: 571 times

Last Updated: Jul 15, 2014 at 02:11 PM

SQL is ignoring my index hint and using a clustered index.

Where to?

We are Moving!

Follow this question

Related Questions