Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot.

This feels like a rather quick and dirty approach - is there a more conclusive, definite way of ascertaining whether clearing the plan cache is the best approach? Making a mistake with this decision could result in a performance issue.

Apologizes if this question is too vague :)

Thanks!

share|improve this question
    
It's hard to define the scope of your question - was the performance problem isolated to a specific query, or was it a lot more general than that? –  Aaron Bertrand Aug 21 '13 at 15:56
    
@Aaron Apologises - it was queries coming from one particular application. This is what made me question the logic behind a complete purge of plans. –  DBAWaffle Aug 21 '13 at 16:35
add comment

2 Answers

Clearing the plan cache is never a good idea. You're starting with a cold cache then, and you'll see a performance hit as SQL Server will have to compile everything from that moment on until plans are then cached to be reused.

He came to this conclusion after noticing performance improved after a reboot.

Oftentimes when I see DBAs reboot, restart the service, and/or clear the procedure cache that gives a really misleading sense of the "fix". Sometimes, the poor performance can be caused by a poorly chosen execution plan. By doing any one of the three ways I mentioned above to get an empty cache, you're just forcing SQL Server's hand to compile everything. Sometimes (although not always) the first query in after that is a typical workload and appears to be better performance....for the typical workload. This is all rooted in bad parameter sniffing.

Parameter sniffing is a good thing. But, if the first query that comes into SQL Server uses a parameter set to "Costa Rica", then SQL Server will compile with that parameter value and make an optimal plan for that parameter value. But if 99.9% of the workload coming in uses the parameter set to "United States", then 99.9% of the workload will be reusing the plan that "Costa Rica" generated.

Fast forward and clear the cache (don't clear the cache, please, I'm just using this as an example). Now you have a cold cache, and the next query that comes in uses the parameter "United States", and voila! SQL Server compiles with that parameter value and now 99.9% of the workload is using an optimal plan.

The point I'm trying to get at is DBCC FREEPROCCACHE is like using a bulldozer to shovel snow off of a walkway. You really should be finding out where the poor performance is and what is causing it. And then, if it is a particular query that is giving you issues, and you've narrowed it down to a poorly selected plan, there are many potential routes to get around bad parameter sniffing (OPTIMIZE FOR hint, recompile, and a few others).

share|improve this answer
add comment

Yes, it is possible to wind up with a poor plan. One common cause of this is called "parameter sniffing". This is usually helpful, but if the stored procedure can be called with parameters that cause widely varying result sets, then the procedure can be stuck on a 'poor plan' for many executions. (But it might have been a fine plan for the execution that caused the plan to be created.)

Using DBCC FREEPROCCACHE is quite a heavy gun, since it frees up all the plans in the server, which means that each one has to recompile the next time it is used.

If you need to force a recompile, you can use sp_recompile to do so for a particular procedure, table, etc.

If a stored procedure is likely to be widely varying in results you might find it useful to offer the OPTION (OPTIMIZE FOR UNKNOWN) hint. This will still optimize for your database statistics, but it will not optimize for a particular parameter.

Benjamin Nevarez posted a useful explanation at: http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.