Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server box, and its CPU usage is at 100%. What steps should I take other than looking at the long running queries to determine what's causing this?

share|improve this question

migrated from stackoverflow.com Aug 17 '09 at 4:03

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

Check the activity monitor to make sure that you don't have any large rollbacks/transactions going on.

Secondly, run SQL Server Profiler to find out what the heck is hitting your box.

Thirdly, check the memory allotted to SQL Server, as a choked memory causes page file usage which ups CPU consumption.

Fourthly, check to make sure there isn't another process that's running on the server chewing up memory or CPU.

After that, just do some query optimization (indexes, etc) if there are long-running queries hitting the box.

share|improve this answer
    
when running Profiler I'd suggest limiting the results to anything taking a lot of CPU at first, if it's a busy box there could be a lot of noise in there. After that saving the profiler output to a table on another machine so you can run some queries to see if the same query is occurring frequently and consuming all that cpu in lots of small chunks Also, I'd suggest doing number 4 first, in case there is a real doh moment in there. Someone zipping a backup to transfer to another machine has caught me out in the past. :) –  Robin Aug 15 '09 at 9:43
    
You can use a query like this instead of profiler on SQL Server 2005 + statisticsio.com/Home/tabid/36/articleType/ArticleView/… –  SuperCoolMoss Aug 24 '09 at 21:44
add comment

Perfmon -> CPU consumption per process, to be sure SQL is the offender

Perfmon -> Batches and Compilations per second, to see if you have a few nasty queries or a whole lot of small ones.

Generally (not having seen your setup) if you have a lot of CPU action and not a lot of disk activity, then it means that the data SQL Server is using fits in memory BUT is not indexed effectively. This means that queries expend a lot of CPU cycles scanning in-memory data pages, because the index structures to simplify and accelerate that process are missing. If the data were bigger, or the RAM smaller, this would reveal itself as an i/o bottleneck. But with plenty of RAM and a smaller data set, you get a CPU bottleneck instead.

So the next stop is a query to the Missing Index DMV, where you might find a lot of high-cost queries that are begging for better indexes. Take the results with a grain of salt, though, and implement selectively.

share|improve this answer
add comment

Well, what is it doing?

If it's doing a select, you need to add indices. Or remove a Cartesian from your joins.

If it's adding an index, you need to get a coffee and wait. ;)

If it's doing an update check that the where clause only includes rows that should be updated.

If it's doing an insert (or update), check for nasty triggers, or too many indices, or (if you're updating a key) a clustered index.

Or, possibly, you need to do a commit, or dump the logs.

share|improve this answer
add comment

Your Answer

 
discard

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