SQL Server suddenly using only a small portion of CPU

Solution 1:

Check the sys.dm_os_waiting_tasks and see what the wait resources are. Basically look at the wait_type and see what's in there. Run this query and post the results back.

select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits
from sys.dm_os_waiting_tasks
group by wait_type

You might be suffering from a similar problem to what I talked about this morning on my blog.

Solution 2:

You can't manage CPU usage but you can manage CPU Affinity. That is, has someone restricted SQL Server to using a single CPU?

In the same vein, has someone changed the global maxdop setting? This limits all query to one CPU but any single query will run on one of the available CPUs

Solution 3:

Assuming there hasn't been a configuration change to CPU affinity or MAXDOP as mentioned by gbn, there are a couple of possibilites.

The first is that the query plan for your query has changed because the distribution of the indexes or underlying table data have changed significantly. Try to optimize or rebuild indexes on the underlying tables.

Secondly, you might now be I/O-bound, either reading data from your main database file or working in tempdb (where SQL will store intermediate parts of the query if it is too big for RAM). Use perfmon, and monitor avg. disk queue length. It should average less than your number of physical disk spindles in the server. If it shoots up during your "heavy query" while CPU remains low, the CPU is simply waiting for disk IO, and therefore can't run at 100% doing useful work. If this is the case, you have a few options: more RAM (to reduce the need to use disk), faster disk (SSD?), or optimize queries, indexes, and schema to reduce disk IO. The last option can have by far the greatest impact (literally improving things by a factor of 100 or more). But it can also be the most difficult, depending on your data structure and queries. Read up on SQL execution plans; buy some books.

Solution 4:

One thing you can do is to see exactly what is happening to the process running the query. If you keep Monitoring the spids activity and see what is its most common wait type. You will probably find that there is a resource such as disk io that the spid is waiting on meaning the cpu is idling for the query until disk read/ writes completing.