Как посмотреть sql-запрос, выполняющийся в заданном процессе SQL Server’а

Иногда возникает ситуация, когда на SQL Server’е выполняется сложный запрос, который имеет сложную структуру и отнимает много времени.

Например, это может быть длинная хранимая процедура, содержащая сотни строк кода, вызовы других процедур и т.д. Или, например, job или генеренный запрос, содержащие в себе много запросов.

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

Список процессов, выполняющихся на сервере можно посмотреть через представление master.dbo.sysprocesses которое не содержит текста запроса.

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

Для начала объявим переменную, содержащую идентификатор нашего процесса:

declare @SPID
set @SPID = 82

В представлении sysprocesses нас будут интересовать следующие поля:
- sql_handle – ссылка на исполняющийся TDS-пакет.
- stmt_start – смещение начала строки кода, исполняющейся в данный момент в TDS-пакете, в формате unicode.
- stmt_end – смещение конца строки кода, исполняющейся в данный момент в TDS-пакете, в формате unicode.

Объявим переменные для данных значений и прочитаем их из представления:

DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int

@sql_handle = sql_handle,
@stmt_start = stmt_start/2,
@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0

Значения stmt_start и stmt_end делим на два, так как в формате unicode для каждого символа расходуется два байта.

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

Данная функция возвращает поле [text] и нам необходимо использовать значения переменных @stmt_start и @stmt_end для того, чтобы получить исполняемый код.

Делаем это следующим образом:

DECLARE @line nvarchar(4000)

SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
  CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle)) 

print @line



В результате переменная @line содержит именно ту часть кода, которая исполняется в данный момент.


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

CREATE PROCEDURE PrintCurrentCode
  @SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int

SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0

DECLARE @line nvarchar(4000)

SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
  CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))

print @line