Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE

The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution time of the query.

The obvious disadvantage of clearing out the plan cache is that all execution plans for your SQL Server instance are recompiled upon execution which may slow things down temporarily until the cache is re-populated. Great for development, give thought before executing in production.

You may be under pressure to quickly get performance back to normal but it’s better to laser target the offending queries if you have time and address accordingly. You can clear out the procedure cache for specific queries as we will see further down the post.

Running DBCC FREEPROCCACHE

[sourcecode language=’sql’]
DBCC FREEPROCCACHE;
[/sourcecode]

…and you should be presented with the following informational message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is optional – WITH NO_INFOMSGS and will simply suppress the informational message above.
[sourcecode language=’sql’]
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
[/sourcecode]

Upon executing, messages with be written to the SQL Server error log for each cache store in the plan cache.

Here is an example:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using DBCC FREEPROCCACHE to clear specific execution plans from the cache

You first need to pinpoint the identifier of the execution plan which you want to clear out of the cache. This is known as a “plan handle” and you can find the plan handles and the cached SQL by issuing a query against sys.dm_exec_cached_plans and sys.dm_exec_sql_text

[sourcecode language=’sql’]
SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE N'{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}ThePlanYouAreLookingFor{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}’
[/sourcecode]

So here I will clear a plan from the cache, firstly by running this query to get the plan inserted to the plan cache.

[sourcecode language=’sql’]
SELECT TOP 1 * FROM Person.Person;
[/sourcecode]

Now I will attempt to find the plan_handle for the execution plan.

[sourcecode language=’sql’]
SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE ‘{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}Person.Person{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}’
[/sourcecode]

So I can see that I want to clear out the cache for the second row as displayed in the screenshot.

So add the plan_handle and say bye to that query plan. 🙂 (The plan handle has been deliberately shortened in the code below otherwise my CMS has trouble displaying it 🙂 )

[sourcecode language=’sql’]
DBCC FREEPROCCACHE (0x060006002FE61B1D40FDAFF501000000010000….);
[/sourcecode]