Michael J. Swart

May 14, 2008

What's taking you so long?

Filed under: Technical Articles — Michael J. Swart @ 6:09 am

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

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress