MSSQL листинг процессов и текста запросов / MS SQL Server process and query text listing

/*  Simple script to show all running processes of MS SQL Server and query text of each process */ 

Довольно типичная задача, однако как всегда в нужный момент не оказалось под рукой скрипта, который бы представил информацию в удобном виде. А именно, отобразить все процессы с их блокирующими процессами и текстом запроса.

Для получения текстов запроса  используется DBCC inputbuffer, тут могут быть неточности , т.к. по прошествии времени в буфере уже может быть другой текст запроса, не соответствующий sql_statement из sys.dm_exec_requests.

Но для запросов, которые являются блокирующими и выполняются долго, это как раз очень актуально.

create table #processes ( session_id int, status nvarchar(32), blocked_by int, waittype nvarchar(60), wait_resource nvarchar(256),
wait_sec float, cpu_time int, logical_reads bigint, reads bigint, writes bigint, elapsed_sec float, statement_text nvarchar(max),
command_text nvarchar(4000), command nvarchar (32), login_name nvarchar(128), host_name nvarchar(128),
program_name nvarchar(256), last_request_end_time datetime, login_time datetime, open_transaction_count int )

create table #dbcc_result(spid int, eventtype nvarchar(128), parameters int, eventinfo nvarchar(max))
insert into #processes
 ( session_id, status, blocked_by, waittype, wait_resource,
wait_sec, cpu_time, logical_reads, reads, writes, elapsed_sec, statement_text,
command_text, command, login_name, host_name,
program_name, last_request_end_time, login_time, open_transaction_count)
SELECT   s.session_id, 
            r.status, 
            r.blocking_session_id, 
            r.wait_type, 
            wait_resource, 
            r.wait_time / (1000.0), 
            r.cpu_time, 
            r.logical_reads, 
            r.reads, 
            r.writes, 
            r.total_elapsed_time / (1000.0), 
            Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                    ((CASE r.statement_end_offset 
                        WHEN -1 
                        THEN Datalength(st.TEXT) 
                        ELSE r.statement_end_offset 
                        END - r.statement_start_offset) / 2) + 1) AS statement_text, 
            Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                    '') AS command_text, 
            r.command, 
            s.login_name, 
            s.host_name, 
            s.program_name, 
            s.last_request_end_time, 
            s.login_time, 
            r.open_transaction_count 
FROM     sys.dm_exec_sessions AS s 
            JOIN sys.dm_exec_requests AS r 
            ON r.session_id = s.session_id 
            CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
WHERE    r.session_id != @@SPID 

declare @spid int = 0, @sql varchar(4000)

declare commands CURSOR LOCAL FORWARD_ONLY STATIC   for 
select session_id from #processes
open commands
 fetch next from commands into @spid

    WHILE @@FETCH_STATUS = 0
    BEGIN
set @sql  = 'DBCC inputbuffer(' + cast(@spid as varchar(5))+')'
insert #dbcc_result(eventtype , parameters , eventinfo ) 
EXEC(@sql) 
update #dbcc_result set spid = @spid where spid is null

fetch next from commands 
into @spid
end

select   p.*, dr.eventinfo from #processes p
join #dbcc_result dr on dr.spid = p.session_id



drop table #processes
drop table #dbcc_result