How can you tell when a query plan reuse is causing performance problems 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!
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).