I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% connections on 1600, after restoring the DB from my side and changing the connection string so that the site will work with the active server hell has broken loose, my connections have stayed on the avg. 1600 but my CPU spiked to an avg. of 70%.
I thought ok, I have some tuning problems, so I have rebuilt all the indexes, and started a profiler on the "new" DB, but alas I was wrong on an avg. hour had 100 SP runs and all of them with less than 50 ms CPU and the highest number of reads were 2.2k (no writes and duration less the a sec for most, highest duration 3 sec).
I would love to get directions on what should I check next, SQL Server side or Web side.
Best Answer
Read this article from Paul Randal on wait stats. You find what sql server is waiting on using the sys.dm_os_wait_stats DMV(Data Management View). It sounds like it MIGHT be a query plan change between versions. But there are a lot of things it could be. Could be storage too. Wait stats should help. Check to see if the MAXDOP setting was different between the versions if possible, you stated the original server died, but not sure if you have the server config saved in a repository for DR purposes. If it's a highly transactional system, parallelism can slow you down. Make sure you know what you are doing before making any changes to MAXDOP.
Also check this article from this thread on SO , it will help you find the worst performing queries. And for more information, check Brent Ozar's Blitz scipt Hope this helps, Good luck. Chris