on August 9, 2017
When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious:
- Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.
- This is true whether or not the index is used by the query.
- Even if the index is on columns in the table which are not referenced by the query, the query will recompile on the next run.
To see this in action, you can play along with this sample script.
A drama of a query plan and changing indexes
Here’s how our story goes…
Creating an index doesn’t remove a plan from cache
We run our Demo Query five times. Querying sys.dm_exec_query_stats and related DMVs, we see five executions, plan_generation_num = one.
Then we create an index on a table referenced by the query.
Querying the DMVs we still see five executions, plan_generation_num = one, and the query plan in cache. It hasn’t been removed!
Running the query again causes a recompile
We run our Demo Query again, this time four times.
Note: It’s important to highlight the query exactly, and not highlight spaces before the query on one run, and highlight differently the next run. Leading spaces are considered part of the query and inconsistent use of spaces impacts plan reuse.
Querying the DMVs, we now see four executions, and a new time for the plan creation_time. Plan_generation_num = two.
If the index is relevant to the query and SQL Server decides to use it, we’ll see a new execution plan.
An index rebuild doesn’t remove the plan from cache, either
After running ALTER INDEX REBUILD, we still see four executions in the cache, and plan_generation_num=two.
But if we rerun Demo Query three times, the first run triggers a recompile. We get a fresh creation_time for the plan, and now plan_generation_num has incremented to three.
The same thing holds for dropping an index
This plays by the exact same rules. When you drop an index, it doesn’t remove the plan from the cache immediately.
Put another way, you can have query plans in cache which refer to indexes that have been dropped, and do not currently exist. The query plan will be recompiled the next time the query is executed – if it’s still in cache. (Maybe it could be removed in the meanwhile due to memory pressure, a restart, an administrator’s command, etc.)
This may seem weird, but I think it’s a good thing
I like this behavior in SQL Server. This means that if you run nightly index maintenance jobs which hit a lot of indexes with rebuilds, your execution plan cache isn’t largely cleared out. If you’re looking at sys.dm_exec_query_stats the next morning, you’re more likely to see relevant information.