October 5, 2010 at 10:47 am
We just recently upgraded and already less then optimal web application from SQL2000 to SQL2005. Parts of the web site have absolutely no problems with performance, but one section designed in .NET has horrible performance compared to exact same equipment and SQL2000. I have dropped and recreated all indexes, updated all statistics. I have upgrade multiplel 2000 servers to 2005 and have always seen at least none or some improvement performance wise, but never a performance decrease. Any suggestions?
October 5, 2010 at 11:39 am
I can't point to specifics, but I've seen anecdotal evidence that suggestions in some edge cases, the changes to the optimizer between 2000 and 2005 can product sub-optimal execution plans.
Basically, I'd look at the areas where it's running slow and then see which queries are causing the problem. See if they can be refactored.
"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
October 5, 2010 at 12:01 pm
Check your parallelism. You may have been doing something like maxdop 1 on 2000, and 0 on 2005, which can cause huge performance swings (in either direction).
Also do a full typical maintenance on your indexes and statistics...make sure you reorg/rebuild everything, and then update stats with fullscan, and then possibly run a recompile on everthing so you generate new execution plans.
Failing all of that, you'll need to trace the actual queries being executed and do more granular tuning by finding out exactly what part of the query is slow.
October 5, 2010 at 12:52 pm
We had performance issues after upgrading to 2005. Some of them were resolved after closely looking at the joins and finding we needed to modify or add the CONVERT functions between dissimilar data types. Not sure why, but it seemed as though 2000 was more forgiving of data type differences in joins. Look at some executions plans for areas to tune.
Here's a previous thread on this topic:
http://www.sqlservercentral.com/Forums/Topic809627-149-1.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply