November 26, 2008 at 2:10 am
Hello,
I have very strange problem. We've migrated from SQL 2k to 2k5 and one of our SP runs 6 time slower than in old environment.
Here output from 10 mins trace:
SQL 2k5
Exec_Count Exec_Coun_10sec+
588 588
Average_Duration Average_Reads Average_Writes Average_CPU
35173 7927 66 30
SQL 2k
Exec_Count Exec_Coun_0-1sec 1-2_sec 2-5_sec
543 489 44 10
Average_Duration Average_Reads Average_Writes Average_CPU
656 12611 59 355
As you can see even than we have 10 times better CPU duration is 60 times slower. Any idea what can be wrong?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
November 26, 2008 at 6:47 am
I assume you already updated the statistics with a full scan?
After doing that... Can you post the execution plan & query? Some functions that worked well enough in 2k don't work as well in 2005. Usually a little adjustment to the query logic can get you over the hump.
----------------------------------------------------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
November 27, 2008 at 4:22 am
you'll excuse my perverse sense of humour but when you add all those certifications and such to your post and ask what essentially is a really basic SQL question you're setting yourself up for a bit of a fall !!
I figure we've known plans are not the same due to a rewrite of the optimiser since 2004 - all you need to do is compare query plans to spot the difference.
Tuning by duration is a bit hit and miss unless you know you have exclusive use of a box: I assume your two hardware platforms are also identical in every single way?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 27, 2008 at 6:46 am
In addition to the update all statistics with fullscan, what about the configuration? Is the hardware identical and identially configured? Are sql server settings the same? Did you run sprocs multple times to ensure the cache was warm?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 28, 2008 at 4:34 pm
On top of any other suggestions, you've got to understand that there have been numerous Cumulative Updates where hundreds of fixes to SQL Server 2005 were made, some that that could apply to your situation. Whenever you ask such a question, you should state what version of SQL Server you are running. Then perhaps someone might know about a fix or two that would apply.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply