Do Index Changes Remove Execution Plans from Cache?

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.

6 thoughts on “Do Index Changes Remove Execution Plans from Cache?

  1. Kendra,

    Excellent post, but I had one question. How often does SQL clear the procedure cache of old plans. Reason I ask is after I removed an index and re-ran the sample query both the new and old plans remain in cache.

    1. Hi Eric,

      If you still see the old plan there after removing the index and re-running the query, then the query didn’t re-use the plan. (If it had re-used it, it would replace it.) If you ran the query in SSMS, then the most likely cause of this is having different spacing — like highlighting an empty line before or after the query in one case, and not highlighting it in another. That is technically a different query and gets a new plan.

      Plans that aren’t reused will be aged out over time. There’s no default time for this: if you have no memory pressure and there’s no reason for SQL Server to get rid of old plans, it could hang out a very long time! But if you have a lot of contention for memory and aggressive plan creation, it would be a very short time. It’s very dynamic.

      1. Thanks

  2. What about renaming indexes? I’ve seen a post where it was said that unless there are index hints that it will not cause any issues but then I read that someone renamed an index and it invalidated multiple plans causing performance issues on their production server. Logically thinking through it, I’m thinking Microsoft wouldn’t use the index name inside the execution plan but perhaps an ID value and if using sp_rename it should not affect the plan cache.

    1. Index names are in the XML of execution plans. I can’t recall the last time I tested, but index renames should require schema modification locks and should invalidate related plans.

      1. Gotcha! Thanks Kendra for the quick reply 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.