DBCC FREEPROCCACHE

  • I have a server on sql server 2005 sp3 running a query/stored procedure intensive application. They complained of slow performance and one of my fellow DBAs did a DBCC FREEPROCCACHE on the server and after that they were ok. This has happened a few times now. What could be the reason and how do I fix it? Thanks.

  • Well, that "solution" is pretty dangerous. You just forced every query to go through a recompile, which can be pretty costly, depending on the query. It's likely that you're experiencing bad execution plans, caused by out of date statistics or parameter sniffing, hard to know. Forcing the queries to recompile gets a better plan, in some cases, and you see performance go back up.

    But this is just speculation. Instead of nuking your cache over & over, I'd suggest you start evaluating wait states and queues to identify what is running slow and start figuring out which procedures are causing the slowdown. If you haven't set up a monitoring using a server-side trace, you can do spot checks using DMV's. I'd try sys.dm_exec_query_stats to see what the longest running queries are.

    Then, figure out what's happening with the longest running queries and fix them.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thank you Grant. The problem usually happens during peak times and we do not get enough time to troubleshoot in detail. This appears to help them so we have to do it until we find the issue. I have the list of stored procs i need to look into but there is a lot and i have no idea which one is the bad one. We update statistics on a daily basis. I also find a lot of Deferred Task Worker wait. Would you know what that would mean?

  • No, I don't recognize that wait state. Sorry.

    Have you run this DMV, sys.dm_os_wait_stats, and collected stats from it? Before, during & after your peak times will tell you quite a lot about where things are slowing down.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I will do that next time it happens. We use a monitoring tool, Quest Performance Analysis, perhaps it calls the wait state by a differetn name. I will ask Quest. thank you.

  • dma-669038 (8/10/2010)


    I have a server on sql server 2005 sp3 running a query/stored procedure intensive application. They complained of slow performance and one of my fellow DBAs did a DBCC FREEPROCCACHE on the server and after that they were ok. This has happened a few times now. What could be the reason and how do I fix it? Thanks.

    This is usually caused by the procedure cache becoming too large. This can easily happen if there are many ad-hoc queries, or if things aren't as parameterised as they might be.

    This is a pretty common problem, so rather than repeat common knowledge, I'll link to it:

    http://blogs.msdn.com/b/bartd/archive/2010/05/26/finding-procedure-cache-bloat.aspx

    http://sqlblogcasts.com/blogs/maciej/archive/2008/09/21/clearing-your-ad-hoc-sql-plans-while-keeping-your-sp-plans-intact.aspx

  • Paul, this is invaluable info!! Thanks very much. I used this as guidance to weed out the adhoc queries and it worked great. Thank you both Paul and Grant, appreciate your help.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply