Смотрим текущую активность SQL Server

http://www.sql.ru/blogs/decolores/1871

Периодически публикую запрос, который сам использую для мониторинга того, что происходит на SQL Server. Сегодня - очередное обновление.

Сам запрос:

set transaction isolation level read uncommitted;

go

select

        sess.[login_name],

        sess.[host_name],

        qs.start_time,

        sess.last_request_start_time,

        datediff(ms, qs.start_time, getdate()) as duration,

        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [Procedure],

        case when qs.sql_handle IS NULL

               then ' '

               else ( substring(st.text,(qs.statement_start_offset+2)/2,(

                       case when qs.statement_end_offset = -1        

                       then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )

        end as query_text,

        sess.cpu_time,

        qs.logical_reads,

        qs.writes,

        sess.session_id,

        case when qs.wait_type = 'CXPACKET' and qs.blocking_session_id = 0 then cp.[blocked] else qs.blocking_session_id end as blocking_id,

        isnull(nullif(qs.wait_resource, ''), cp.[waitresource]) as waitresource,

        qs.wait_time,

        qs.wait_type,

        convert(xml, pl.query_plan) _plan,

        rgwg.[name]

from sys.dm_exec_sessions sess

-- To get blocking info for parallel queries

outer apply (select top 1 blocked, esp.waitresource from  master.dbo.sysprocesses esp where esp.spid = sess.session_id and esp.blocked > 0 and esp.blocked <> esp.spid) cp

inner join sys.dm_exec_requests qs

        on qs.session_id = sess.session_id

        and qs.[wait_type] not in ('BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP')

left join sys.dm_exec_procedure_stats ps

        on ps.sql_handle = qs.sql_handle

        and ps.[plan_handle] = qs.[plan_handle]

outer apply master.sys.dm_exec_sql_text(qs.sql_handle) st

outer apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset   ) pl

inner join sys.resource_governor_workload_groups as rgwg    

        on rgwg.[group_id] = sess.[group_id]

where (sess.[host_name] is not null or sess.session_id>=49)

order by sess.session_id

В отличии от предыдущих версий 

·  показывает план выполнения для конкретного запроса

·  показывает блокировки для параллельных запросов


Что "может пойти не так":
1. План выполнения запроса доступен не всегда - так уж бывает.

2. Встречаются планы, которые невозможно прочитать из-за ограничений парсера xml - слишком сложные запросы, причем тут роль играет не только число таблиц, могут быть просто слишком запросы и без этого:

Msg 6335, Level 16, State 101, Line 3
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

3. Бывает, в процессе выполнения самого диагностического запроса, "диагностируемый" уже выполнился и более недоступен. Получим что-то вроде:

Msg 8181, Level 16, State 1, Line 3
Text for '' is missing from the system catalog. The object must be dropped and re-created before it can be used.