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

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.

Ed

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.

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

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!).

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

Also, is IX_PlannedJob_ActioningLocationID a filtered index?

Hi Aaron,

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

Sorry I didn't mention that before.

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).

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.