Do Index Changes Remove Execution Plans from Cache?

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

Continue reading

How to Find Queries Using an Index (and Queries Using Index Hints)

Sometimes you know a query is out there, but it’s hard to find the exact query.

SQL Server stores query execution plans in cache, but it can be difficult to query the XML it stores. And there’s always a chance that the query plan won’t be there, due to memory pressure, recompile hints, or the plan cache being cleared by setting changes or other administrative actions.

Continue reading