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