Index Changes And Recompilation

Kendra Little explains how index modifications affect the plan cache:

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!

Read on for more, including what happens when you run the query again, what happens when you rebuild indexes, and what happens when you drop an index.

Related Posts

Deletes And Indexes

Jeff Schwartz looks at the performance cost of indexes when it comes to deleting rows: Many articles concerning SQL Server discuss how record insertion overhead increases with each additional index. They discuss b-tree manipulations and page splits in addition to leaf and non-leaf levels. However, few discuss the fact that deletion overhead increases as well, […]

Read More

Rebuilding Versus Reorganizing Rowstore Indexes

Paul Randal explains the difference between rebuilding and reorganizing rowstore indexes: Rebuilding an index requires building a new index before dropping the old index, regardless of the amount of fragmentation present in the old index. This means you need to have enough free space to accommodate the new index. Reorganizing an index first squishes the index […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

August 2017
MTWTFSS
« Jul  
 123456
78910111213
14151617181920
21222324252627
28293031