/* 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
