В предыдущей статье мы обсудили, как использовать шаблон Visitor для ручного выявления проблемных ситуаций, когда подсказки NOLOCK применяются к цели оператора UPDATE или DELETE в SQL Server. Однако такой ручной подход не масштабируется при работе с большим количеством операторов во всей среде. В этой статье мы рассмотрим решение для автоматизации сбора и выявления этих проблемных ситуаций.

Получение модулей сопоставления шаблонов

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

CREATE DATABASE BadNOLOCKs;
GO

USE BadNOLOCKS;
GO

CREATE PROCEDURE dbo.p1_false AS
    UPDATE dbo.x SET i += 1;
    SELECT * FROM dbo.x WITH (NOLOCK);
GO

CREATE PROCEDURE dbo.p2_true AS
    UPDATE x SET i += 1 FROM dbo.x WITH (NOLOCK);
    UPDATE x SET i += 5 FROM dbo.x INNER JOIN dbo.y WITH (NOLOCK) ON x.i = y.i;
GO

CREATE PROCEDURE dbo.p3_true AS
    DELETE q FROM dbo.x AS q WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM sys.objects);
    DELETE x FROM dbo.x /* WITH (NOLOCK) */ AS x;
GO

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

Сохранение результатов для запросов

Чтобы отслеживать выявленные ситуации, мы можем сохранить результаты в таблице, которая позволяет нам выполнять запросы к данным. Мы можем создать временную таблицу для сбора результатов из каждой базы данных и добавить дополнительные столбцы для имени базы данных и имени сервера:

CREATE TABLE #Modules (
    ProcedureName nvarchar(512),
    Body nvarchar(max),
    DatabaseName sysname,
    ServerName sysname
);

Затем мы можем изменить наш код для вставки полученных данных в эту временную таблицу:

EXEC master.dbo.sp_ineachdb N'
    INSERT #Modules (
        ProcedureName,
        Body,
        DatabaseName,
        ServerName
    )
    SELECT
        ProcedureName = s.name + N''.'' + o.name + CASE WHEN t.[object_id] IS NOT NULL THEN N'' (триггер для '' + p.name + N'')'' ELSE N'''' END,
        Body = OBJECT_DEFINITION(o.object_id),
        DatabaseName = DB_NAME(),
        ServerName = @@SERVERNAME
    FROM
        sys.objects AS o WITH (NOLOCK)
        INNER JOIN sys.schemas AS s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id]
        LEFT OUTER JOIN sys.triggers AS t WITH (NOLOCK) ON o.[object_id] = t.[object_id]
        LEFT OUTER JOIN sys.objects AS p WITH (NOLOCK) ON t.parent_id = p.[object_id]
    WHERE
        o.[type] IN (N''P'', N''TR'')
        AND (
            LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%update%from%nolock%''
            OR LOWER(OBJECT_DEFINITION(o.object_id)) LIKE N''%delete%from%nolock%''
        );
';

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

Заключение

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

Дата последнего обновления статьи: 2021-08-03