Rajendra Gupta

DBCC FREEPROCCACHE command introduction and overview

July 23, 2019 by

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.

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.

Now, we will use the dynamic management views sys.dm_exec_query_plan, sys.dm_exec_sql_text and sys.dm_exec_query_stats to get the execution statistics, query plan , last execution time.

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.

check procedural cache and execution plan

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.

Execute this command with the plan cache, and you get the following informational message.

DBCC command output

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.

It removes the specific execution plan from the cache. You can validate this by re-running the earlier query.

removed the specific execution plan from the cache

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.

Or

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.

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.

Using DBCC FREEPROCCACHE to clear a specific resource pool

Let’s say we want to clear the procedural cache for the internal resource pool. Specify resource pool name with the DBCC FREEPROCCACHE command.

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.

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

Conclusion

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Top SQL Server Books
  2. SQL Query Optimization Techniques in SQL Server: Parameter Sniffing
  3. Searching the SQL Server query plan cache
  4. Saving your SQL Execution Plan
  5. Importance of SQL Server Max Degree of Parallelism
168 Views