How do I find out what is hammering my SQL Server?

Question

My SQL Server CPU has been at around 90% for the most part of today.

I am not in a position to be able to restart it due to it being in constant use.

Is it possible to find out what within SQL is causing such a CPU overload?

I have run SQL Profiler but so much is going on it's difficult to tell if anything in particular is causing it.

I have run sp_who2 but am not sure what everything means exactly and if it is possible to identify possible problems in here.

To pre-empt any "it's probably just being used a lot" responses, this has only kicked in today from perfectly normal activitly levels.

I'm after any way of finding what is causing CPU grief within SQL.

Answer 1

This query uses DMV's to identify the most costly queries by CPU

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 
    qs.total_worker_time DESC

For a complete explanation see: How to identify the most costly SQL Server queries by CPU

Answer 2

I assume due diligence here that you confirmed the CPU is actually consumed by SQL process (perfmon Process category counters would confirm this). Normally for such cases you take a sample of the relevant performance counters and you compare them with a baseline that you established in normal load operating conditions. Once you resolve this problem I recommend you do establish such a baseline for future comparisons.

You can find exactly where is SQL spending every single CPU cycle. But knowing where to look takes a lot of know how and experience. Is is SQL 2005/2008 or 2000 ? Fortunately for 2005 and newer there are a couple of off the shelf solutions. You already got a couple good pointer here with John Samson's answer. I'd like to add a recommendation to download and install the SQL Server Performance Dashboard Reports. Some of those reports include top queries by time or by I/O, most used data files and so on and you can quickly get a feel where the problem is. The output is both numerical and graphical so it is more usefull for a beginner.

I would also recommend using Adam's Who is Active script, although that is a bit more advanced.

And last but not least I recommend you download and read the MS SQL Customer Advisory Team white paper on performance analysis: SQL 2005 Waits and Queues.

My recommendation is also to look at I/O. If you added a load to the server that trashes the buffer pool (ie. it needs so much data that it evicts the cached data pages from memory) the result would be a significant increase in CPU (sounds surprising, but is true). The culprit is usually a new query that scans a big table end-to-end.

Answer 3

You can find some useful query here:

Investigating the Cause of SQL Server High CPU

For me this helped a lot:

SELECT s.session_id,
    r.status,
    r.blocking_session_id 'Blk by',
    r.wait_type,
    wait_resource,
    r.wait_time / (1000 * 60) 'Wait M',
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) 'Elaps M',
    Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
    ((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
    Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
    Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    s.login_time,
    r.open_transaction_count
FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
    CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

In the fields of status, wait_type and cpu_time you can find the most CPU consuming task that is running right now.

Answer 4

Run either of these a few second apart. You'll detect the high CPU connection. Or: stored CPU in a local variable, WAITFOR DELAY, compare stored and current CPU values

select * from master..sysprocesses
where status = 'runnable' --comment this out
order by CPU
desc

select * from master..sysprocesses
order by CPU
desc

May not be the most elegant but it'd effective and quick.

Answer 5

You can run the SQL Profiler, and filter by CPU or Duration so that you're excluding all the "small stuff". Then it should be a lot easier to determine if you have a problem like a specific stored proc that is running much longer than it should (could be a missing index or something).

Two caveats:

• If the problem is massive amounts of tiny transactions, then the filter I describe above would exclude them, and you'd miss this.
• Also, if the problem is a single, massive job (like an 8-hour analysis job or a poorly designed select that has to cross-join a billion rows) then you might not see this in the profiler until it is completely done, depending on what events you're profiling (sp:completed vs sp:statementcompleted).

But normally I start with the Activity Monitor or sp_who2.

Answer 6

For a GUI approach I would take a look at Activity Monitor under Management and sort by CPU.