From SQL 2000 to SQL 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

From SQL 2000 to SQL 2005

Hi Guys, I’ve encounter the situation like this. I did a SP on SQL 2000, The SP runs pretty well as in performance. But when i upgrade from SQL 2000 to SQL 2005, the SP performs really bad. I detached the DB from 2000 and copy the MDF files to the new machine and attach it to the 2005 to restore the DB, the machine that the SQL 2005 is running on is better than the one 2000 is running. i’m very new in SQL 2005, so i was wondering if there is any setting or configuration that i need to set in order to achieve the same performance previously. Thanks in advance.. Regards
Jimmy
Did you run update stats after the upgrade?
as thomas told run SP_updatestats to update statistics and DBCC DBREINDEX for each table to rebuild indexes. Madhu
After doing all maintenance check the query plan and i/o stats and compare them with 2000 plan/stats.. Mohammed U.
I’ve update all statistic and rebuilt the indexes. As for the query plan, is it the execution plan?? as for I/O statistic seems like the 2000 has more scan count than the 2005, then what does it mean by this??
btw what is parallelism things in the 2005?? cos i’ve read the forum, some say there is a bug in parallelism bug in 2005, when working on multiple CPU machine whice is exactly what the machine that i’m running on.. can someone advice me??? thanks.
Is the data partitioned on SQL 2000 and SQL 2005 when upgraded?
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275646&Culture=en-US fyi and look for troubleshooting performance problems webcast, a good one to follow. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
When generating an execution plan for a query, the SQL Server optimizer attempts to choose the plan that provides the fastest response time for that query. If the query#%92s cost exceeds the value specified in the cost threshold for parallelism option and parallelism has not been disabled, then the optimizer attempts to generate a plan that can be run in parallel. A parallel query plan uses multiple threads to process the query, with each thread distributed across the available CPUs and concurrently utilizing CPU time from each processor. The maximum degree of parallelism can be limited server wide using the max degree of parallelism option or on a per-query level using the OPTION (MAXDOP) hint.
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#E1BAG
Mohammed U.
]]>