Sub 2 second query sometimes takes 30 seconds plus

Question

Sub 2 second query sometimes takes 30 seconds plus

webtekkie

SSCommitted

Points: 1901
More actions
May 9, 2007 at 2:52 am

#176337

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ian Yates SSCoach Points: 19738 More actions · Answer 2

An article on the front page of this site might be right up your alley! http://qa.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.

webtekkie SSCommitted Points: 1901 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software