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.
My response on DBCC FREEPROCCACHE:
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).
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.
Here is a post that I wrote a while back on FREEPROCCACHE:
http://stevestedman.com/2013/05/dbcc-freeproccache/
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.
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.
Related Links
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!