February 4, 2008 at 3:23 am
We are having a new server which has SQL 2005 standard edition SP2 installed on it (OS windows 2003 server)
On migrating from SQL 2000 to SQL 2005 , we observe certain procedures taking almost 10 times the normal run times.
However certain procedures in SQL 2005 run much quicker.
On observation , it is found that the procedures having loops , cursors take more time in SQL 2005 . Is there some setting to be done to enable SP’s having loops to run faster.
Any help in this regards would be much appreciated.
February 4, 2008 at 4:43 am
Most of the time, cursors just flat out run too slow. Rewriting the procedures to avoid the use of cursors is usually the best bet. Other than that, I'm not aware of a major slowdown in cursor processing between 2000 & 2005. But, since I try to avoid them most of the time, there's probably something out there that I'm not aware of.
"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
February 4, 2008 at 5:03 am
Hi
Have you updated the statistics and space usage. These need to be done after migration.
"Keep Trying"
February 6, 2008 at 5:47 am
Even better, ensure you rebuild indexes and gain the benefit of a 'full scan' for the stats too... 🙂
February 6, 2008 at 8:38 am
Both of the last two suggestions are what I'd recommend. Rebuild indexes, if that doesn't help, consider using a higher sample rate with update statistics.
February 6, 2008 at 8:43 am
Keeping Grant's recommendation in mind - there are also some options like FAST_FORWARD you might care to turn on. That being said - it's likely that the slowness is due to what the cursor is doing rather than the cursor itself.
as to the indexes - for better or for worse - SQL 2005 doesn't always seem to use the same indexes, so you may need to revisit your indexing scheme. It's not at all out of the question that the optimizer has decided NOT to use an index you used to have for some portion of the process.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply