Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a general question about advanced issues regarding index behaviour.

In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using the same name as before. Our tests seemed to run ok, but we later found out that our testing environments resolved to using the plans according to the old index definitions, while our production environment used the new ones. The tests had therefore produced the wrong results, and we changed these indexes back to the old versions in production, where the old filter definitions were immediately applied to the plans.

This worked fine for the past 6 months. Now however, we have the opposite problem. Our production environment has suddenly fallen back to using the plans for these 6 months old, falsely filtered indexes, where until a few weeks back it was still using the ones the existing indexes are supposed to use.

We've tested fixing this by again dropping these problem indexes, and this time creating them with a different name entirely. This seems to be working fine.

But my question is this: Considering that the indexes have been dropped (not just renamed), then created with the same name, and the query plan cache has been cleared AND the statistics updated several times, how is it possible that SQL Server seems to have a mind of its own and now has resolved to using ancient plans that I didn't even know could have existed any more?

Basically, how exactly does SQL Server store and use the data associated with indexes and their plans? How can you force SQL Server to clear that cache, wherever it may be, completely so that it can absolutely not simply decide to use ancient detrimental plans simply based on the same index name? How does all of this work, so that we can understand it and will never have to deal with this issue again?

Thanks!

EDIT:

It's now all but confirmed that these 6 months old filtered indexes were the reason. I restored the DB to a testing environment and ran problem queries against it, providing the wrong execution plans compared to another ancient testing environment. Checking each and every one of the indexes used by the older, functional environment, every single index definition was identical. I updated the stats, reorganized and rebuilt the indexes so none of them had fragmentation above 35, cleared the query plan cache, and still the problem persisted.

I then proceeded to find specifically those indexes involved in the query that were briefly filtered 6 months ago, dropped them and recreated them (the first time they still didn't work right, after the second attempt after another restore, they DID start working right). After this, I dropped and created the index with a different name that hasn't been used before, otherwise using the same definitions. This fixed it every time, and the execution planner would now use the correct indexes. Then I dropped these, created the indexes again using the filter definitions from 6 months back, again with a new name to ensure the query planner would use the new definitions instead of some ghost statistics from older ones. The failed plan produced after these indexes was identical to the ones initially used when all definitions fragmentation, statistics etc had been cleared and checked. Proving once and for all that despite all the seemingly available metadata, the execution planner was working under the assumption that the indexes were filtered and thus not usable, all along.

Do any of you know what could be going on? Or is this something that should be reported as a bug, regardless of how rare it might be. Because the implications of this behavior and the effects it's already had, are severe enough that currently I'm considering logging every index name just so none of them will ever be reused. Otherwise this paints a grim picture in that SQL Server may store ancient statistics in the background and start using them at any time, completely nullifying the structure and purpose of new indexes which may be completely business-critical. While it seems extremely likely that the failover had something to do with this, I still can't understand how perfectly working indexes suddenly be replaced by outdated and completely wrong definitions and statistics to the point where no manner of rebuilding them or updating the applicable metadata would help. With no real way to even diagnose that this has happened, other than a sudden decrease in performance, and quirky behaviour on the query planner's part.

share|improve this question
    
Are the queries you are executing run as stored procedures? Have you ruled out parameter sniffing? –  Adam Haines Aug 19 '13 at 13:48
    
No, they are standard queries coming from our software code. Typically it's cleartext SQL sent to the DB as is. While some of them do use parameters, trying the queries out by hand with hard-coded values was one of the first thing we did, didn't help. –  Kahn Aug 19 '13 at 16:06
    
You say that SQL Server behaves as if the filtered indexes where currently in place. That is a very wage statement. Can you describe the exact behavior you are concerned about? Are you observing that a particular existing index is not used? Or is it something entirely different? Your current question cannot be answered without wild speculation. –  Sebastian Meine Aug 19 '13 at 18:36
    
@SebastianMeine, please take a look at the updated OP after edits. Thanks! –  Kahn Aug 19 '13 at 20:32
    
Please supply examples of the problematic execution plans. –  Martin Smith Aug 19 '13 at 20:55

1 Answer 1

SQL Server does not save previous index definitions for reuse at a later time. So query plans are based on the most recent statistics that were used for a compile.

Although stored procedures do not recompile constantly, they will in time recompile. You can also individually recompile procedures. The "sp_recompile" can be used to make your stored procedures recompile the next time they are used.

I see that you mention recreating statistics and recreating indexes, both of which help to get a correct set of statistics. You do not mention doing index reorganization, which also can be part maintaining the health of the indexes.

However, if the definition of the index has been changed to an earlier version, then it means that someone or some process changed them.

share|improve this answer
    
The problem here is that their behaviour is exactly what it would be, if the indexes were filtered. This has been verified by testing the same queries against filtered indexes and comparing the execution plans. However, checking the index definitions from sys.indexes, we can see that the indexes themselves have NOT changed at all, and are in fact not filtered. –  Kahn Aug 19 '13 at 12:59
    
One theory we currently have, is if it's possible that moving the DB to a failover cluster that could in theory have the old statistics, and then back to the primary cluster, could have somehow altered the underlying statistics to a point where even updating them wouldn't fix the plans. Because this would seem to correlate with the timing when our problems began. –  Kahn Aug 19 '13 at 13:19
    
@Kahn, thanks for adding some details on the circumstances. If I understand you correctly, this occurs after a failover. Remember that a failover restarts SQL Server, which means that cached executions plans are lost, so new plans are created. If the statistics are not up-to-date, particularly for an ascending value index, then the new execution plan may not have the needed information. That is why I would update statistics and reorganize or rebuild suspicious indexes. –  RLF Aug 19 '13 at 13:48
    
The failover is a theory. Still, we have updated the statistics, removed cached plans, and dropped & recreated the indexes involved, to no avail. It was only after dropping the indexes and recreating them with the same definitions, but a different name, when we started seeing healthy behaviour again. As said, we have witnessed this weird behaviour where SQL Server resolves to planning index-usage based on obsolete, removed index definitions simply because of reusing old names, before. But to go back there after this much time, is new. And either way, we don't understand how this could happen? –  Kahn Aug 19 '13 at 16:09
    
Just curious about your drop / recreate process. Do you (1) drop the old index and (2) create the new index with the same name? Or do you (1) create a new index, (2) drop the old index, and (3) rename the new index to the old index name? Just musing on how to get a similar symptom. –  RLF Aug 19 '13 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.