Laufende Abfragenund deren CPU-Auslastung:
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
r.logical_reads,
s.program_name,
s.host_name,
r.start_time,
s.login_name,
t.text
FROM
sys.dm_exec_requests r
JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE
r.cpu_time > 0
ORDER BY
r.cpu_time DESC;
Abfragen mit der höchsten CPU-Auslastung seit dem letzten Neustart des SQL Servers:
SELECT TOP 10
qs.total_worker_time AS CPU_Time,
qs.execution_count,
qs.total_worker_time / qs.execution_count AS Avg_CPU_Time,
qs.total_physical_reads,
qs.total_logical_writes,
qs.total_logical_reads,
qs.creation_time,
qs.last_execution_time,
st.text AS QueryText,
qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
qs.total_worker_time DESC;
|