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.
This question is related to
sql-server
performance
cpu-usage
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.
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.
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
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:
But normally I start with the Activity Monitor or sp_who2.
For a GUI approach I would take a look at Activity Monitor under Management and sort by CPU.
Source: Stackoverflow.com