January 26, 2011 at 3:28 pm
Hi,
We have a SQL server 2008 installed on a cluster and has about 40 databases.
Recently we discovered that it is using up 100% CPU. Although some SQL agent jobs and some other user queries were killed, it did not make more than 10% difference on the CPU usage. Some critical applications obviously were failing to perform as a result of the SQL CPU problem. Since then we have noticed that SQL Server uses up 100% CPU once or twice a day everyday.
The SQL server is installed on a win server 2008 enterprise SAN which has 24GB of RAM and uses an Intel Xeon Processor.
Can someone tell me how to diagnose this problem? How can I dig deeper?
January 26, 2011 at 3:47 pm
You need to find out what the processes are that are running on your SQL Server during the time when it is 100% (if possible). Use sp_who2 or even better use a query like the one described in this link
http://www.sqlservercentral.com/articles/DMV/64425/
Once you see what is running you can see what sort of resources are being used, how many threads are being used, etc... By seeing that data you should be able to determine what the cause is and hopefully from there you can either tune it (if it is a query) or find a better time to run it (if it is a maintenance process).
February 17, 2011 at 3:34 am
Thank you. I will give that a go and report back.
February 18, 2011 at 7:36 am
Does the sql server service account have the lock pages in memory user right?
FisherDad
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply