Do you also think that an INDEX REBUILD triggers a FREEPROCCACHE? Things always get a little quieter between Christmas and New Year and you do things that you don’t have as much time for during the rest of the year. For example, you might watch a few SQL sessions on YouTube or read interesting blog posts. I chose a video from a well-known speaker and was a little surprised by one statement.
DBCC FREEPROCCACHE is the same when you REBUILD indexes
The only difference is that DBCC FREEPROCCACHE is executed only once, while INDEX REBUILD clears the query cache with every execution.
DBCC FREEPROCCACHE
Using DBCC FREEPROCCACHE clears all execution plans from the plan cache. This means that SQL Server will need to recompile queries the next time they are executed, which can temporarily decrease query performance due to the increased number of compilations. The question is – does REBUILD of an index always result in the plan cache being flushed? The answer is short and precise: NO!
In general, an INDEX REBUILD does not initially lead to any changes to the plan cache! It is not the reorganization of the data caused by REBUILD that invalidates an execution plan! It is the statistics. When a REBUILD is performed, this ALWAYS involves updating the statistics of the index. A REBUILD implicitly leads to an UPDATE STATISTICS WITH FULLSCAN. It is the statistics objects that lead to a recompilation of ONE query. Only the affected execution plan is rebuilt (recompile) but not all plans are deleted from the plan cache!
Quod Esset Demonstrandum – Demo
For demonstration purposes, I use my demo database, which I use for my workshops and conference sessons.
Preparation
First, three indexes are created for the table [dbo].[customers], each using the attributes [c_custkey] (primary key), [c_mktsegment] and [c_nationkey].
USE ERP_Demo;
GO
/* Primary key on c_custkey */
ALTER TABLE dbo.customers ADD CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (c_custkey);
CREATE NONCLUSTERED INDEX nix_customers_c_mktsegment ON dbo.customers (c_mktsegment);
CREATE NONCLUSTERED INDEX nix_cusotmers_c_nationkey ON dbo.customers (c_nationkey);
GO
I also use an extended event session to record when a statement-level recompilation is required by any kind of batch. This includes stored procedures, triggers, ad hoc batches and queries. Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods.
CREATE EVENT SESSION [recompile] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
ACTION
(
sqlserver.session_id,sqlserver.sql_text
)
WHERE sqlserver.session_id = 72 /* dedicated session_id */
)
WITH
(
MAX_MEMORY=4096 KB,
VENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE
)
GO
Execution
/* Execute three different queries on the indexes */
SELECT * FROM dbo.customers WHERE c_custkey <= 10 AND 1 = (SELECT 1);
GO
SELECT * FROM dbo.customers WHERE c_nationkey = 47;
GO
SELECT * FROM dbo.customers WHERE c_mktsegment = 'AUTOMOBILE';
GO
/* check the cached execution plans */
SELECT dest.text,
deqs.execution_count,
deqs.creation_time,
deqs.last_execution_time,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan (deqs.plan_handle) AS deqp
WHERE dest.text LIKE N'SELECT *%dbo.customers%'
AND dest.text NOT LIKE N'%sys.dm_exec_query_stats%';
GO
The sample code executes three different queries, each generating a separate execution plan for execution. Please note the creation time before moving on to the next step!
In the next step, all indexes of the table [dbo].[customers] are rebuild. According to the „expert“, there should no longer be any execution plan in the cache, since a DBCC FREEPROCCACHE is used for/after each INDEX REBUILD.
/* now we rebuild the indexes which includes update of the statistics! */
ALTER INDEX ALL on dbo.customers REBUILD WITH (SORT_IN_TEMPDB = ON);
GO
/* and check the plan cache again! */
SELECT dest.text,
deqs.execution_count,
deqs.creation_time,
deqs.last_execution_time,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan (deqs.plan_handle) AS deqp
WHERE dest.text LIKE N'SELECT *%dbo.customers%'
AND dest.text NOT LIKE N'%sys.dm_exec_query_stats%';
GO
The result does not surprise; all execution plans with the same timestamp as the first execution are preserved in the plan cache. Why should they be deleted?
Before executing the three queries again, you should consider when an execution plan becomes invalid. Statistics are the be-all and end-all for the query optimizer when creating execution plans! Using statistics, the query optimizer can decide which query strategy is best based on the number of expected records.
Each execution plan is based on the metadata of the statistics objects. This can be clearly seen in the XML schema of an execution plan.
After the indexes were rebuilt, the statistics were also updated. They get a new timestamp for „LastUpdate“; the metadata has therefore been changed.
/* Let's run the same queries again and check the plan cache */
SELECT * FROM dbo.customers WHERE c_custkey <= 10 AND 1 = (SELECT 1);
GO
SELECT * FROM dbo.customers WHERE c_nationkey = 47;
GO
SELECT * FROM dbo.customers WHERE c_mktsegment = 'AUTOMOBILE';
GO
/* Now we must have a new time stamp for the first execution */
SELECT dest.text,
deqs.execution_count,
deqs.creation_time,
deqs.last_execution_time,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan (deqs.plan_handle) AS deqp
WHERE dest.text LIKE N'SELECT *%dbo.customers%'
AND dest.text NOT LIKE N'%sys.dm_exec_query_stats%';
GO
A look at the data of the extended event shows that for all three queries the Query Optimizer generated a new execution plan through a recompile. Schema changes are given as the reason for the new plan. Although the data itself has not changed and the histogram data of the statistics is identical, it has nevertheless been updated and thus a schema change (date of update) has taken place. The query optimizer creates a new execution plan.
Conclusion
Don’t be put off by expert statements that an INDEX REBUILD empties the plan cache! That’s not the case; The query optimizer creates a new execution plan because of the changed statistics object and not the REBUILD action. The same behavior can also be observed when you execute a simple UPDATE STATISTICS.
Thank you very much for reading and have a successful 2025.
