:::: MENU ::::

Find long running queries

One of the first scripts I go to when looking at what’s happening in an instance is this:

SELECT     db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
END -r.statement_start_offset)/2) AS stmt_executing,
s.login_name,     s.HOST_NAME,     s.PROGRAM_NAME,    s.host_process_id,
r.status,     r.wait_time,    wait_type,     r.wait_resource,
r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt,
sys.dm_exec_sessions s
WHERE r.session_id > 50
and r.session_id=s.session_id
ORDER BY r.cpu_time desc

It shows you among other things blocked processes, a link to the sql plan, reads, writes, the executing statement and a lot of other handy stuff.

A simple good script to have around.


So, what do you think ?