Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite

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.

share|improve this question
I have asked the dba.se mods if they would like this question migrated. However, your question is somewhat vague and is going to be hard to answer without knowing more about the application that consumes this database and about the internals of the database itself. – Kev Aug 26 '12 at 16:42
1  
Sounds like you did not manage to trace all statements being executed. 100 SP calls per hour is far too low. – usr Aug 26 '12 at 22:42
The only filter I have is the database ID and CPU above or eq. 1. That part of the site is not very active, so 100 SP per hour is ok and is the reason I asked the question What kind of info do you need? That server is a loaded one. no mem-cache on the "new" DB. That amount of SP execution should not spike our machine (4 core Xenon 32GB mem), at first I thought it was my Imagination, so I took the DB offline and all the sever parameter in the perfmon where lowered. I'm not necessarily asking for a solution, some other methods or Ideas to what more do I need to check will be useful as well. – Avi Aug 27 '12 at 6:19
2  
Does the new database have some wack options set? Maybe auto-close is on? – Jon Seigel Aug 27 '12 at 15:33

migrated from stackoverflow.com Aug 26 '12 at 22:25

2 Answers

up vote 4 down vote

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

share|improve this answer
up vote 1 down vote accepted

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.

share|improve this answer
Glad you were able to solve this! – Jon Seigel Aug 29 '12 at 1:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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