May 9, 2007 at 2:52 am
Hi All,
A week ago, I upgraded a SQL 2000 database from a slow Windows 2000 server, to a shiny new SQL 2005 quad processor cluster running Windows Server 2003 enterprise.
Everything went according to plan, with the exception of one query which, 95% of the time returns results in less than two seconds, but every now and then takes over thirty seconds. This results in the clients web application timing out. At the same time, I have found that the CPU on the server sits at 100% for the duration of the query.
I have managed to reproduce this once or twice by running the procedure with the same parameters in Management Studio, and checked for blocking processes at the same time, but my process was not blocking, or being blocked.
The stored procedure is complex and badly written, (uses the same view twice for instance) and hits 11 tables (some of them twice). Also, some of the key tables it hits are badly fragmented. But the fact remains that this did not time out when it was on the SQL 2000 server.
Can anyone suggest what the cause could be?
Many thanks,
Martin
May 9, 2007 at 6:32 am
I have seen differences in how the query optimizer behaves in 2005. Overall, I think it's much better, but it acts choosy. Is it possibly going into recompile? Or is there a particular set of parameters that are causing the problem? You could force the query to use the good execution plan vs. the obviously problematic one (assuming the "good" plan works well every time). And, of course, you should get the fragmentation reduced, update the statistics and clean up the query to avoid the issue in general.
Try running it through Profiler and capture the individual statements instead of simply the whole query to see where the greatest cost is occurring.
"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
May 9, 2007 at 7:48 am
An article on the front page of this site might be right up your alley! http://www.sqlservercentral.com/columnists/mdillon/2863.asp
Recompilation due to different query plans for different circumstances seemed to be the best way to solve the issue. You can test further by using the dbcc commands to drop the data cache and the procedure cache.
May 9, 2007 at 9:01 am
Thanks for your replies Ian and Grant,
I think the author of this article must have written this just for me - because it all fits. I will add the Recompile line at the top tomorrow morning and see if the nasty timeouts go away.
Thanks again,
Martin
May 9, 2007 at 9:18 am
Keep an eye on it. We once had a query, so badly written, that recompiles took over two minutes. We'd get time outs about once every 17 or 18 minutes because the statistics were updating so frequently that they spawned recompiles that killed the system. In addition to rewriting the query, we went the other way and put a KEEP_FIXED_PLAN on the proc. Now, in 2005, we'd probably capture the good execution plan and force it to use that.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply