December 15, 2016 at 12:35 pm
I would like to know which user is running a top CPU query. I have queried sys.dm_exec_query_stats to identify the top CPU consuming queries but I am unable to tie the query hash or sql text or plan handle to a session ID or SPID.
Please assist
December 15, 2016 at 2:29 pm
That dynamic management view shows you aggregated execution statistics for cached queries. SQL Server doesn't store information about each individual execution of the query. If someone is currently running it you can see that by looking at sys.dm_exec_requests which has sql_handle and plan_handle in it.
e.g.
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status, s.memory_usage / 128.0 AS memory_meg,
mg.used_memory_kb, mg.granted_memory_kb, mg.requested_memory_kb, mg.ideal_memory_kb,
db_name(er.database_id) AS database_name,
er.status AS request_status, er.command, er.percent_complete,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
er.transaction_id, er.open_transaction_count,
er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
OBJECT_NAME(st.objectid, st.dbid) AS object_name, qp.query_plan, er.sql_handle, er.plan_handle, st.[text]
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
Who ran the query may not be as important as why the query was slow, looking at the execution plan should help you determine if there's a bad join condition, bad where clause condition, missing index to consider, etc.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply