Index Hints : query using dropped index should fail gracefully
If I create an index, and then use a hint in a query to use that index, the query fails with:
Msg 308, Level 16, State 1, Line 1
Index 'abc' on table 'dbo.abc' (specified in the FROM clause) does not exist.
With tools like DTA, the dropping of indexes is going to become much more common, but common sense of inspecting code for index hints is not necessarily going to get any better, until people get bitten... and even then they will probably react by only fixing the one query.
[Filed on behalf of Greg Linwood.]
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 11
<=-=May 20 2008 10:09AM=-=>Having SQL Server behave in predictable ways is important. Improvements like this one sort of bring the db engine into a fuzzier area of behaviour. Maybe good in this case, but a slippery slope.
Consider this situation. A dba sees a query with terrible performance that is holding locks on resources everywhere. The dba fixes the query with a query hint and the index suddenly gets dropped. What’s worse? An error on the query? Or a poorly performing query (with a warning) that is once again locking resources everywhere.
<=-=May 20 2008 4:32PM=-=>I agree with the sentiment of this DCR. Failing queries when an INDEX hint can’t be creates excessive dependence between the physical structure of the data and the application code. But we must find a way to provide a warning to users, e.g. through the SQL error log or a DMV or showplan, if the index hint is not honored. We’ll consider this for the next release.
Note that you get the kind of behavior I described if you use USE PLAN in a plan guide. That is a possible workaround for now.
Thanks! -Eric
<=-=May 20 2008 4:54PM=-=>This is a major issue for ISVs that have third-parties customizing their software. Even if the core application doesn’t use index hints, some customized code may. Dropping indexes becomes unmanageable risk. Unfortunately we cannot use aliases to solve this problem (at least in 2005). That would be another solution if we could create the alias for existing index that will have the same name as the index that has been dropped. Or SQL Server can simply ignore the hint which is even better.
3 comments
-
Fabiano Lira
commented
For the situations stated above,
what if i could specify the behavior (fail gracefully or not) in the index hint? -
Josh Ross
commented
It would be nice if you couldn’t drop the Index, if it was used in a hint.
-
Brent Ozar
commented
Now that Azure SQL DB's automatic index tuning will drop indexes, this needs to be removed from unplanned status. If you're going to remove indexes from us, then you need to handle failures gracefully.