مانیتورینگ پیشرفته قفل‌ها در SQL Server: جایگزینی قدرتمند برای sp_lock با sp_lock2

در دنیای SQL Server، مدیریت و نظارت بر قفل‌ها برای حفظ عملکرد و جلوگیری از بن‌بست (deadlock) حیاتی است. ابزار داخلی `sp_lock` اطلاعات پایه‌ای درباره قفل‌ها ارائه می‌دهد، اما اغلب برای تجزیه و تحلیل عمیق و سریع، ناکافی و ناخوانا است. این محدودیت‌ها باعث می‌شود که توسعه‌دهندگان و مدیران دیتابیس به دنبال راه‌حل‌های پیشرفته‌تری برای مشاهده جزئیات کامل قفل‌ها باشند. ما در اینجا `sp_lock2` را معرفی می‌کنیم، یک رویه ذخیره‌شده (stored procedure) قدرتمند که برای پر کردن این شکاف طراحی شده و دیدگاهی جامع و کاربردی از وضعیت قفل‌ها در SQL Server ارائه می‌دهد.

`sp_lock2` با جمع‌آوری اطلاعات از چندین Dynamic Management View (DMV) و ترکیب آن‌ها، جزئیات بسیار بیشتری نسبت به `sp_lock` سنتی نمایش می‌دهد. این رویه، اطلاعاتی مانند نام منبع قفل‌شده، نوع قفل، وضعیت قفل (مانند GRANT، WAIT، CONVERT)، مدت زمان قفل، شناسه‌های SPID مسدودکننده و مسدودشده، نام دیتابیس، شیء و ایندکس مربوطه، فرمان SQL در حال اجرا، نام هاست و کاربر، و حتی نام برنامه کلاینت را در اختیار شما قرار می‌دهد. این سطح از جزئیات، عیب‌یابی مشکلات عملکردی و بن‌بست‌ها را به مراتب آسان‌تر می‌کند و به شما امکان می‌دهد تا ریشه مسائل مربوط به قفل‌ها را به سرعت شناسایی کنید.

`sp_lock2` با استفاده از DMV‌های کلیدی SQL Server مانند `sys.dm_tran_locks` (برای اطلاعات قفل)، `sys.partitions`، `sys.objects`، `sys.indexes` (برای شناسایی اشیاء)، `sys.databases` (برای نام دیتابیس‌ها)، `sys.dm_exec_requests`، `sys.dm_exec_sessions` و `sys.dm_exec_connections` (برای جزئیات SPID‌ها و نشست‌ها) و `sys.dm_exec_sql_text` (برای متن فرمان SQL) کار می‌کند. این رویکرد یکپارچه، تضمین می‌کند که شما تمامی اطلاعات لازم را در یک خروجی سازمان‌یافته و قابل فهم در اختیار داشته باشید.

برای نصب و استفاده از `sp_lock2`، کافیست کد T-SQL زیر را در محیط SQL Server Management Studio (SSMS) یا هر ابزار اجرای کوئری دیگری اجرا کنید. توصیه می‌شود این رویه ذخیره‌شده را در دیتابیس `master` ایجاد کنید تا از هر دیتابیس دیگری قابل دسترسی باشد.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_lock2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_lock2]
GO

CREATE PROCEDURE [dbo].[sp_lock2]
    @spid INT = NULL,
    @DBName SYSNAME = NULL,
    @ObjectName SYSNAME = NULL,
    @IndexName SYSNAME = NULL,
    @LockType VARCHAR(30) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Local Variables
    DECLARE @SQLCommand NVARCHAR(4000);

    SELECT
        tl.request_session_id AS SPID,
        tl.resource_type AS [Resource],
        tl.resource_database_id AS [DBID],
        DB_NAME(tl.resource_database_id) AS [DBName],
        CASE
            WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
            WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID') THEN (SELECT OBJECT_NAME(p.object_id, tl.resource_database_id) FROM sys.partitions p WHERE p.hobt_id = tl.resource_associated_entity_id)
            ELSE NULL
        END AS [ObjectName],
        CASE
            WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID') THEN (SELECT i.name FROM sys.indexes i JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE p.hobt_id = tl.resource_associated_entity_id)
            ELSE NULL
        END AS [IndexName],
        tl.request_mode AS [Mode],
        tl.request_status AS [Status],
        DATEDIFF(ms, s.last_request_start_time, GETDATE()) AS [Duration(ms)],
        er.blocking_session_id AS [BlockingSPID],
        er.wait_type AS [Wait Type],
        er.wait_time AS [Wait Time],
        er.wait_resource AS [Wait Resource],
        s.host_name AS [HostName],
        s.login_name AS [LoginName],
        s.program_name AS [ProgramName],
        c.client_app_name AS [ClientApp],
        tl.request_owner_guid AS [TransactionID]
    FROM
        sys.dm_tran_locks tl
    LEFT JOIN
        sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
    LEFT JOIN
        sys.dm_exec_requests er ON tl.request_session_id = er.session_id
    LEFT JOIN
        sys.dm_exec_connections c ON tl.request_session_id = c.session_id
    WHERE
        (@spid IS NULL OR tl.request_session_id = @spid)
        AND (@DBName IS NULL OR DB_NAME(tl.resource_database_id) LIKE '%' + @DBName + '%')
        AND (@ObjectName IS NULL OR (CASE
                                        WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
                                        WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID') THEN (SELECT OBJECT_NAME(p.object_id, tl.resource_database_id) FROM sys.partitions p WHERE p.hobt_id = tl.resource_associated_entity_id)
                                        ELSE NULL
                                     END) LIKE '%' + @ObjectName + '%')
        AND (@IndexName IS NULL OR (CASE
                                        WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID') THEN (SELECT i.name FROM sys.indexes i JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE p.hobt_id = tl.resource_associated_entity_id)
                                        ELSE NULL
                                     END) LIKE '%' + @IndexName + '%')
        AND (@LockType IS NULL OR tl.resource_type LIKE '%' + @LockType + '%')
    ORDER BY
        tl.request_session_id, [Duration(ms)] DESC;

    -- Get the SQL command for waiting sessions
    DECLARE @WaitingSPID INT;
    DECLARE cur_waiting CURSOR FOR
    SELECT DISTINCT er.session_id
    FROM sys.dm_exec_requests er
    WHERE er.blocking_session_id IS NOT NULL
      AND er.blocking_session_id  0;

    OPEN cur_waiting;
    FETCH NEXT FROM cur_waiting INTO @WaitingSPID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQLCommand = SUBSTRING(st.text, (er.statement_start_offset / 2) + 1,
                                       ((CASE er.statement_end_offset
                                             WHEN -1 THEN DATALENGTH(st.text)
                                             ELSE er.statement_end_offset
                                         END - er.statement_start_offset) / 2) + 1)
        FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
        WHERE er.session_id = @WaitingSPID;

        IF @SQLCommand IS NOT NULL
        BEGIN
            PRINT N'-- SQL Command for SPID ' + CAST(@WaitingSPID AS NVARCHAR(10)) + N':';
            PRINT @SQLCommand;
            PRINT N''; -- Blank line for readability
        END;

        FETCH NEXT FROM cur_waiting INTO @WaitingSPID;
    END;

    CLOSE cur_waiting;
    DEALLOCATE cur_waiting;

END;
GO

پس از ایجاد رویه `sp_lock2`، می‌توانید از آن برای مشاهده جامع قفل‌ها در SQL Server استفاده کنید. خروجی این رویه ستون‌های متعددی را شامل می‌شود که هر کدام اطلاعات ارزشمندی را ارائه می‌دهند:

* SPID: شناسه‌ی پردازنده سرور (Server Process ID) که نشان‌دهنده‌ی یک نشست فعال در SQL Server است.
* Resource: نوع منبعی که قفل شده است (مانند OBJECT، PAGE، KEY، RID، DATABASE).
* DBName: نام دیتابیسی که منبع قفل‌شده در آن قرار دارد.
* ObjectName: نام شیء (مثلاً جدول یا View) که درگیر قفل است. اگر منبع یک صفحه، کلید یا RID باشد، نام شیء مرتبط نمایش داده می‌شود.
* IndexName: نام ایندکسی که درگیر قفل است، در صورتی که منبع یک صفحه، کلید یا RID باشد.
* Mode: نوع درخواست قفل (مانند S – Shared، X – Exclusive، U – Update، IS – Intent Shared).
* Status: وضعیت درخواست قفل (مانند GRANT – اعطا شده، WAIT – در انتظار، CONVERT – در حال تبدیل).
* Duration(ms): مدت زمانی (بر حسب میلی‌ثانیه) که قفل یا درخواست قفل فعال بوده است.
* BlockingSPID: SPID نشستی که باعث مسدود شدن (blocking) SPID فعلی شده است. اگر این مقدار 0 باشد یا NULL، نشان‌دهنده عدم مسدود شدن است.
* Wait Type: نوع انتظار SPID مسدودشده (مثلاً LCK_M_X برای انتظار برای قفل انحصاری).
* Wait Time: مدت زمان انتظار (بر حسب میلی‌ثانیه) برای SPID مسدودشده.
* Wait Resource: منبعی که SPID مسدودشده منتظر آن است.
* HostName: نام هاست کلاینتی که SPID از آن متصل شده است.
* LoginName: نام کاربری SQL Server که برای اتصال SPID استفاده شده است.
* ProgramName: نام برنامه‌ای که از آن اتصال ایجاد شده است (مثلاً SQL Server Management Studio).
* ClientApp: نام برنامه کلاینت (معمولاً مشابه ProgramName اما گاهی دقیق‌تر).
* TransactionID: شناسه‌ی تراکنش، که برای ردیابی تراکنش‌های توزیع‌شده مفید است.
* SQL Command: برای نشست‌هایی که در وضعیت انتظار هستند، این رویه فرمان SQL در حال اجرای آن‌ها را نیز نمایش می‌دهد که بسیار در عیب‌یابی کمک‌کننده است.

نحوه استفاده از sp_lock2:

شما می‌توانید `sp_lock2` را بدون هیچ پارامتری فراخوانی کنید تا تمامی قفل‌های فعال را مشاهده کنید:

EXEC sp_lock2;

برای فیلتر کردن خروجی بر اساس SPID خاص، می‌توانید پارامتر @spid را مشخص کنید:

EXEC sp_lock2 @spid=65;

برای مشاهده قفل‌ها در یک دیتابیس خاص، از پارامتر @DBName استفاده کنید:

EXEC sp_lock2 @DBName='YourDatabase';

همچنین می‌توانید خروجی را بر اساس نوع قفل، نام شیء یا نام ایندکس فیلتر کنید:

EXEC sp_lock2 @LockType='OBJECT';
EXEC sp_lock2 @ObjectName='YourTable';
EXEC sp_lock2 @IndexName='YourIndex';

`sp_lock2` یک ابزار ضروری برای هر مدیر دیتابیس یا توسعه‌دهنده SQL Server است که به دنبال بهبود عملکرد و رفع مشکلات مربوط به همزمانی (concurrency) است. با ارائه دیدگاهی کامل‌تر و قابل فهم‌تر از قفل‌ها، به شما کمک می‌کند تا تصمیمات آگاهانه‌تری برای بهینه‌سازی سیستم‌های خود بگیرید.