Sometimes I wonder what SQL Server is doing. For example maybe I want to fail-over SQL Server to another node and I want to ask SQL Server the question “Are you in the middle of something?” If the answer is “yes” than it can mean a long rollback. I haven’t found one system view that can answer the question, but there is a combination of three of them that I think covers everything:
— longest waiting queries
select top 10 * from sys.dm_os_waiting_tasks
where session_id > 50
order by wait_duration_ms desc
— longest transaction (possibly not waiting but keeping resources)
select top 10 *
from sys.dm_tran_active_transactions at
left join sys.dm_tran_session_transactions s_t
on s_t.transaction_id = at.transaction_id
where transaction_type in (1,4)
order by transaction_begin_time asc
— longest running queries (possibly not waiting but working hard)
select top 10 st.text, er.*
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) st
order by er.start_time asc
