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

Для выявления и устранения проблем с блокировкой важно иметь инструмент или скрипт, который может предоставить информацию о цепочке блокировки. Один из таких скриптов, который стал популярным среди экспертов SQL Server, – это скрипт дерева блокировки.

Скрипт дерева блокировки – это мощный инструмент, который помогает выявить главных блокировщиков в цепочке блокировки. Он предоставляет информацию о SPID (идентификатор процесса сервера), типе ожидания, имени входа, исходной базе данных, тексте SQL и других соответствующих деталях. Этот скрипт постоянно улучшается и модифицируется экспертами SQL Server, чтобы сделать его более эффективным и полезным.

Одно из недавних изменений в скрипте касается распространенной проблемы, с которой сталкиваются при работе с приложениями, использующими курсоры. Обычно при отладке приложения с курсорами отображается SQL-выражение “FETCH API_CURSOR…”, которое не предоставляет фактическое SQL-выражение, выполняемое курсором. Это изменение, внесенное экспертом SQL Server JohnnyB, заполняет этот пробел и улучшает полезность скрипта.

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

Вот обновленный скрипт:

/* SQL Blocking Tree w/Cursor info
Благодарности эксперту SQL Server JOHNNYBNO */
IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
    DROP TABLE #Blocks
SELECT   spid
        ,blocked
        ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
INTO     #Blocks
FROM     sys.sysprocesses spr
    CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
GO

WITH BlockingTree (spid, blocking_spid, [level], batch)
AS
(
    SELECT   blc.spid
            ,blc.blocked
            ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM    #Blocks blc
    WHERE   (blc.blocked = 0 OR blc.blocked = SPID) AND
            EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
    UNION ALL
    SELECT   blc.spid
            ,blc.blocked
            ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM     #Blocks AS blc
        INNER JOIN BlockingTree bt 
            ON  blc.blocked = bt.SPID
    WHERE   blc.blocked > 0 AND
            blc.blocked <> blc.SPID
)
SELECT  N'' + ISNULL(REPLICATE (N'|         ', LEN (LEVEL)/4 - 2),'')
        + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN '' ELSE '|------  ' END
        + CAST (bt.SPID AS NVARCHAR (10)) AS BlockingTree
        ,spr.lastwaittype   AS [Type]
        ,spr.loginame       AS [Login Name]
        ,DB_NAME(spr.dbid)  AS [Source database]
        ,st.text            AS [SQL Text]
        ,CASE WHEN cur.sql_handle IS NULL THEN '' ELSE (SELECT [TEXT] FROM sys.dm_exec_sql_text (cur.sql_handle)) END  AS [Cursor SQL Text]
        ,DB_NAME(sli.rsc_dbid)  AS [Database]
        ,OBJECT_SCHEMA_NAME(sli.rsc_objid,sli.rsc_dbid) AS [Schema]
        ,OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) AS [Table]
        ,spr.waitresource   AS [Wait Resource]
        ,spr.cmd            AS [Command]
        ,spr.program_name   AS [Application]
        ,spr.hostname       AS [HostName]
        ,spr.last_batch     AS [Last Batch Time]
FROM BlockingTree bt
    LEFT OUTER JOIN sys.sysprocesses spr 
        ON  spr.spid = bt.spid
    CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
    LEFT JOIN sys.dm_exec_cursors(0) cur
        ON  cur.session_id = spr.spid AND
            cur.fetch_status != 0
    JOIN sys.syslockinfo sli
        ON  sli.req_spid = spr.spid AND
            sli.rsc_type = 5 AND
            OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) IS NOT NULL
ORDER BY LEVEL ASC

При выполнении вышеуказанного скрипта будет отображено дерево блокировки, аналогичное изображению ниже:

Если вы считаете этот скрипт полезным, не забудьте поблагодарить экспертов SQL Server Branden и JohnnyB за их вклад. Их модификации сделали этот скрипт еще более ценным для устранения проблем с блокировкой в SQL Server.

Обновление 1: Благодаря комментарию эксперта SQL Server Роберта код был изменен для отображения имени объекта.

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