This article gives an overview of the SQL Server DBCC FREEPROCCACHE command and its usage with different examples.
To learn more about DBCC commands in SQL Server, I would recommend going through this in-depth article Concept and basics of DBCC Commands in SQL Server
Introduction to Execution plan and Procedural cache
Once we execute a query, SQL Server prepares the optimized execution plan and stores that execution plan into the plan cache. Next time, if the same query is run, SQL Server uses the same execution plan instead of generating a new one. It is a good thing to reuse an existing execution plan because SQL Server does not need to do full optimization for the query. SQL Server might create another execution plan if query optimizer thinks that it can improve query performance. I have seen the scenarios, in which the new execution plan starts causing performance issues such as high CPU and memory utilization. You want to get rid of that particular execution plan so that query optimizer can prepare a new execution plan.
You might think of restarting SQL Services, but it might be a costly affair. You require application downtime. In the production instance, it might be challenging to get downtime and that too for removing a specific execution plan from the cache.
We might want to clear the entire cache to resolve performance issues. Below are a few examples.
- Due to long-running queries, your server might face memory pressure
- In the case of high recompilations
- A large number of ad-hoc query workloads
- Dynamic t-SQL
Let’s explore to clear buffer cache without taking the restart of SQL Services.
Overview of DBCC FREEPROCCACHE command
We can use the DBCC FREEPROCCACHE command to clear the procedural cache in SQL Server. We might drop a single execution plan or all plans from the buffer cache. SQL Server needs to create new execution plans once the user reruns the query.
Let’s use the demo to create a stored procedure and view the execution plan in the cache.
CREATE PROCEDURE [usp_GetCustomerInfo] @CustomerName NVARCHAR(100)
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp ON s.PrimaryContactPersonID = pp.PersonID
WHERE CustomerName = @CustomerName;
SQL Server do not create the execution plan during execution plan creation. The execution plan gets created during first execution of the stored procedure.
Example 1: Using DBCC FREEPROCCACHE to clear a specific execution plan
Let’s run the stored procedure with different parameters.
Exec [usp_GetCustomerInfo] @CustomerName='Abel Spirlea'
Exec [usp_GetCustomerInfo] @CustomerName='Shah Alizadeh'
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t].text LIKE '%usp_GetCustomerInfo%';
In the output, we can see two different execution plans for this stored procedure. We get the plan handle as well for both the execution plans.
You can click on the hyperlink in the ‘query_plan’ column to check the execution plan of this stored procedure in both the graphical and XML format.
Suppose we diagnosed the performance issue in this stored procedure due to this change in the execution plan and we want to remove a specific plan from the cache.
We can drop a single execution plan using the following DBCC FREEPROCACHE command. Specify the plan handle of the procedure that we want to remove.
DBCC FREEPROCCACHE (plan_handle_id_)
Here plan handle uniquely identifies a query execution plan in the plan cache.
We can also specify the SQL handle of the batch in the DBCC FREEPROCACHE command.
Let’s execute this query for the plan handle from my example.
DBCC FREEPROCCACHE (0x05000900996DB224D002DAFF3802000001000000000000000000000000000000000000000000000000000000)
Execute this command with the plan cache, and you get the following informational message.
The output is a generic DBCC execution message. If we do not want this message, we can run the DBCC FREEPROCCACHE command with ‘WITH NO_INFOMSGS’ clause. It suppresses the information message as an output of this query.
DBCC FREEPROCCACHE(0x060009007F4272304099DAFF3802000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS;
It removes the specific execution plan from the cache. You can validate this by re-running the earlier query.
Example 2: Using DBCC FREEPROCCACHE to clear all execution plan cache
Suppose we do not want to remove a specific plan handle from the cache. Instead, we want to clear all plans cached for the stored procedures. In this case, we do not need to pass the plan handle. We can run the following command.
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
It also logs an entry in the SQL Server error logs. Go to the Management folder of the SQL instance and view the current SQL Server error logs.
Example 3: Using DBCC FREEPROCCACHE to clear a specific resource pool
We can clear the cache at the resource pool level as well. First, check the resource pool cache and used memory using the DMV sys.dm_resource_governor_resource_pools.
SELECT name AS 'Pool Name',
cache_memory_kb/1024.0 AS [cache_memory_MB],
used_memory_kb/1024.0 AS [used_memory_MB]
Let’s say we want to clear the procedural cache for the internal resource pool. Specify resource pool name with the DBCC FREEPROCCACHE command.
DBCC FREEPROCCACHE ('internal');
Maximum Degree of Parallelism and DBCC FREEPROCCACHE command
Usually, experienced DBA with performance troubleshooting skills, modify the SQL Server max degree of parallelism (MAXDOP) setting to control the number of processors in the parallel query execution plan. The default value of MAXDOP is 0, and it allows SQL Server to use all available CPU for the parallel execution plan.
This article does not cover the detailed information of MAXDOP; you can refer to the article Max Degree of Parallelism in SQL Server.
Let’s say we modify the max degree of parallelism value to 6 for our workload. We do it using the following SQL query.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', 6;
RECONFIGURE WITH OVERRIDE;
Once we have modified the max degree of parallelism value, SQL Server invalidates all stored procedure plan cache. It behaves similar to a DBCC FREEPROCCACHE command.
Alternatively, we can use the following methods to resolve performance issues and clear the plan cache.
- We can use sp_recompile to recompile a stored procedure. SQL Server also requires generating a new execution plan upon the next execution of the code
In SQL Server 2016 onwards, we can use database scoped configuration option to clear the procedural cache in a specific database. Execute the following query under database context to clear the database-specific procedural cache1ALTER SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
Quick Summary of DBCC FREEPROCCACHE command
Below is the quick summary of what we learned about DBCC FREEPROCCACHE command in this article:
- It clears out the plan cache in SQL Server for a specific plan hash or all plan as per the parameters used
- SQL Server forces to generate a new execution plan of each stored procedure on the next execution
- It might increase the utilization of system processes such as CPU, memory
- It might be good for the development environment, but try to use caution in executing this command in the production environment
- You should clear the cache only when it is crucial to do so
- We can use an alternative method sp_recompile to generate a new plan for the stored procedure
- It is a better approach than restarting SQL Server instance to clear the cache however we should not do it frequently
In this article, we explored the DBCC FREEPROCCACHE command to clear the procedural cache along with the consequences of it. You should know this command and use it only in case of any urgent requirements.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at firstname.lastname@example.org
View all posts by Rajendra Gupta