Штатная команда sp_who2 для просмотра sql блокировок

Для отслеживания sql блокировок в SQL Server существует команда sp_who2. В результате выполнения данной команды мы получим список соединений с SQL Server, но вот находить блокировщиков придется вручную, анализируя колонку BlkBy.

В этой колонке мы увидим блокирующий процесс, который можно принудительно "убить" с помощью команды KILL ПРОБЕЛ SPID убиваемого процесса (напимер, KILL 22).

Скриншот окна выполнения запроса SQL Server

Скрипт для удобного использования команды sp_who2 при отслеживании sql блокировок

Однако если активных соединений с SQL Server очень много, то sql блокировки отслеживать трудно: приходится прокручивать весь список и вручную выбирать самых-самых "блокировщиков". Гораздо удобно создать временную таблицу и сделать сортировку по полю BlkBy. Готовый скрипт для обнаружения и устранения sql блокировок можно взять ниже.

DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT)

INSERT INTO @Table EXEC sp_who2

SELECT DISTINCT BlkBy FROM @Table

SELECT * FROM @Table

 Скриншот окна выполнения запроса SQL Server

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

Как узнать выполняемый блокирующим процессом скрипт или бонус к отслеживанию sql блокировок

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

Увидели мы блокирующий процесс. А дальше что? Убить блокировщика, вызвавшего sql блокировку, всегда успеем. Но ведь нужно смотреть в корень и предупредить возникновение блокировок. А для этого надо узнать, какой скрипт выполняется под блокировщиком.

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

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

Для запуска процедуры выполняем команду

Exec PrintCurrentCode SPID процесса

Команда выведет выполняемый блокировщиком код на языке TSQL, который мы с легкостью сможем проанализировать.

Удачи в отслеживании sql блокировок и отсутствия долгих транзакций!

Добавить комментарий


Защитный код
Обновить