--Last Worker Time > Last Elapsed Time = Query went Parallel
SELECT
DB_Name(q.dbid) as 'Database',
qs.Execution_Count,
q.ObjectID,
object_name(q.objectid, q.dbid) as 'ObjectName',
qs.Last_Execution_Time,
qs.Last_Worker_Time,
qs.Last_Elapsed_Time,
qs.total_worker_time,
qs.total_elapsed_time,
q.TEXT,
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS q
WHERE qs.total_worker_time > qs.total_elapsed_time
order by
--qs.total_worker_time desc
qs.execution_count desc
SELECT
DB_Name(p.dbid) as 'Database',
cp.UseCounts,
p.ObjectID,
object_name(p.objectid, p.dbid) as 'ObjectName',
p.Query_Plan,
q.TEXT,
cp.Size_in_Bytes,
cp.Plan_Handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE cp.cacheobjtype = 'Compiled Plan' AND p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0
order by cp.usecounts desc
--How many CPUs is my running query is using
select
ost.session_id,
ost.scheduler_id,
ost.task_address,
w.worker_address,
ost.task_state,
wt.wait_type,
wt.wait_duration_ms
from sys.dm_os_tasks ost
left join sys.dm_os_workers w on ost.worker_address=w.worker_address
left join sys.dm_os_waiting_tasks wt on w.task_address=wt.waiting_task_address
where
ost.session_id > 50
and ost.task_state in ('Running')
order by session_id, scheduler_id;
|
|
|
|
|
|