Archivi tag: performance

Detect SQL Head blockers

select r.session_id, ss.host_name,r.blocking_session_id as block_id,
CASE WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
ELSE '' END as head,
r.status,r.wait_type, s.text as session_sql, r.wait_time as session_wait_time
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions ss on r.session_id = ss.session_id
LEFT OUTER JOIN sys.dm_exec_requests r2 ON ss.session_id = r2.blocking_session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) s
WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)

 

Detect CPU expensive running queries

select
    r.session_id,
    r.cpu_time,
    ((r.total_elapsed_time/1000.0)/60.0) as MinutesRunning,
    s.login_name,
    c.client_net_address,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
where s.login_name <> 'sa'
and r.session_id <> @@spid
--where st.text like '%your query string to search for%';
order by r.cpu_time desc