August 10, 2010 at 11:42 am
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.
August 10, 2010 at 12:33 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2010 at 12:44 pm
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?
August 10, 2010 at 1:28 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2010 at 1:33 pm
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.
August 10, 2010 at 3:32 pm
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 11, 2010 at 1:12 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply