|
I have a question regarding the migration from SQL Server 2000 to 2005. I moved the database, switched the compatibility mode to 9.0, recalculated statistics, rebuilded indexes. Everything done like it is written in BOL. However most of the queries are 50% slower than in case of SQL Server 2000 (for example, a query takes 30 instead of 20 minutes. Simple select from a large table). I googled, and find out that some people had similiar problems, but could not find any solutions. Any hints? | |||||
feedback
|
This question came from our site for professional and enthusiast programmers.
|
Are you using table variables? If so read this, we found some queries to run 10-20x slower on 2005, rewriting to use temp tables instead solved it. | |||
|
feedback
|
|
Have you updated your statistics? Do the execution plans differ between the two instances? | |||
feedback
|
|
me again, but the post was migrated from stackoverflow.com. Yes the execution plans are very, very similar (only percantage differs). | |||||||||
feedback
|
|
Just to rule out the obvious, but ... when you state that "I moved the database", is it now sitting on different hardware? If so, the specs of the SQL Server 2000 server and the 2005 server are the same, yes? | |||
|
feedback
|
|
If the execution plans are similar then it is most likely that the difference is in the available Hardware capacity and performance. That is, either the CPU, Memory or Disks aren't as fast (could be network also, but that is less likely). OR, the HW's the same, but there is something else on them that is competing with your SQL Server for the capacity (this is a notorious problem with SAN/NAS). | |||
|
feedback
|
|
How do it look when you run the most common set of counters in performance monitor, any suspicious values? Compare current performance counter values with most resent baseline from the old database setup then you should see what is differ. | |||
|
feedback
|
|
Did you change the collation ( on migration) to be case insensitive from being case sensitive? that might have an effect on speed of indexes... | |||
|
feedback
|
|
Ensure that the SQL 2005 server is completely up to date on all service packs. There are a fair amount of performance improvements. As others have asked: Is this on the exact same hardware or did you actually move it to a "new" server? If a new server, are the disks the same? Have you installed the right drivers? Does one of the disks have a problem? I recently ran into an issue where a SQL 2005 database was running dog slow. Found out the drives were topping out at 10MB/sec... Considering they were RAID 5, 10K RPM SAS drives, that wasn't normal. Upon installing the right drivers we found out one of the disks in the array was bad and the array itself had throttled down. When the drive was replaced, performance jumped. Drastically. | |||
|
feedback
|
|
I had/have this same issue, and used Database Engine Tuning Advisor, which comes with SQl Server 2005. I ran the advisor on a query which took about 15 minutes on 2005 and 6 seconds in 2000. After I ran the Advisor, it gave recommendations, one being to add an index (note the table already had three indexes). After I applied the recommendations, the query took less than a second on 2005. My question now becomes, how can I add this same index to all tables and/or on all databases in one script? I have yet to find the answer on that! | |||
|
feedback
|
