مانیتورینگ پیشرفته قفلها در 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) است. با ارائه دیدگاهی کاملتر و قابل فهمتر از قفلها، به شما کمک میکند تا تصمیمات آگاهانهتری برای بهینهسازی سیستمهای خود بگیرید.
