DBCC FREEPROCCACHE is a DBCC command in Microsoft SQL Server that can be used to clear the procedure cache, which is a region of memory that stores the execution plans for stored procedures, triggers, and ad hoc Transact-SQL batches. Clearing the procedure cache can be useful for troubleshooting performance issues or testing the effects of changes to the database schema on query performance.
Running DBCC FREEPROCCACHE with a parameter tells it to just dump one plan and recompile it. This is very low impact, and is not that different that if you had just changed the query slightly causing it to recompile.
If you suspect that the query is being really slow because of parameter sniffing issues. Using FREEPROCCACHE to dump the bad plan can help with the performance if you are lucky enough that the next time the query is run that it has the “right” parameters to create a plan that runs faster. If that fixes things, then you might think you are in good shape, but if that plan gets pushed out of the cache later and is recompiled with the “bad” parameters then you may end up in the bad performance place.
To use DBCC FREEPROCCACHE, you do not need to specify any arguments. Simply execute the command in a query window connected to the database you want to clear the procedure cache for.
Here is an example of how to use DBCC FREEPROCCACHE to clear the procedure cache in a database:
All that being said, even after freeing the plan from the cache, it may still perform horribly. In that case, it may just be an inefficient query, missing index, out of date statistics, or one of the other common issues that causes queries to be slow.
-- Clear the procedure cache in the "MyDatabase" database
DBCC FREEPROCCACHE ('MyDatabase');
The output of DBCC FREEPROCCACHE will be a message indicating whether the operation was successful or not. If the operation was successful, the procedure cache will be cleared and all stored procedures, triggers, and ad hoc Transact-SQL batches will be removed from the cache.
It’s important to note that clearing the procedure cache can have an impact on the performance of the database server, as the execution plans for the stored procedures and other objects will need to be regenerated the next time they are executed. As a result, it is generally best to avoid using DBCC FREEPROCCACHE in production environments, or to use it sparingly and only when necessary.
DBCC FREEPROCCACHE is a useful tool for troubleshooting and optimizing query performance in SQL Server, but it should be used with caution.
Here is a question that I received from a friend today and I thought it would be a good post explaining the details:
Steve,
I have a question on DBCC FREEPROCCACHE. I used sp_blitzcache from Brent and it shows that one particular query is horrendous ( I already knew that as I ran it and cancelled it after 10 minutes or so).
It gives me a DBCC FREEPROCCACHE (0x03000700FA15020D5EAA560063A7000001000000000000000000000000000000000000000000000000000000);
What will be the impact of running this?
The application was updated an hour or so before I ran this.
So DBCC FREEPROCCACHE takes the parsed plans and dumps them out of memory forcing SQL Server to recompile the plan the next time a query is run that would have used the plan that was just dumped. The danger is if you run DBCC FREEPROCCACHE with no parameters, it will dump the entire plan cache forcing every plan to be recompiled, similar to if you had just restarted SQL Server (DON’T DO THAT).
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
While I fully agree with the warnings about clearing the cache, then it also depends on the use of your SQL Server database. I’m working with BI, and when you deal with datawarehouse and staging databases, it’s often not so big a problem to clear the cache. For this type of environments, the queries are typically not executed so often, and in many cases they run so long, that the time it takes to recompile the plan etc, is negligible.
Good point. If you know your plan cache and your environment well enough then yes it may be fine to clear the cache like you said if the time is negligible. The general OLTP case I would say don’t do it.
thanks for the info.
-Steve Stedman