7

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.

0

2 Answers 2

4

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

1

I answer this one myself because I got the right direction to do it. I read most of the articles CleanFill refer to, have rebuilt the statistics and FT, in addition the "new" DB have some wack options (that was the term Jon Seigel used) for example Auto shrink was true. The combination of both things did the work, my server is back to normal with the "new" DB.

Thanks.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.