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?
migrated from stackoverflow.com Aug 17 '09 at 4:03
This question came from our site for professional and enthusiast programmers.
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.
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.
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.