گزارش فرآیندهای block شده در SQL Server

گزارش فرآیندهای block شده در SQL Server ابزاری کلیدی برای بهینه‌سازی عملکرد

نظارت بر قفل‌ها و فرآیندهای block شده، یک فعالیت ضروری برای هر مدیر پایگاه داده (DBA) یا توسعه‌دهنده SQL Server است. بدون ابزارهای مناسب، تشخیص و حل مشکلات عملکرد ناشی از block شدن می‌تواند چالش‌برانگیز باشد. SQL Server با معرفی قابلیت‌هایی مانند گزارش فرآیندهای block شده (Blocked Process Report)، ابزارهای قدرتمندی برای شناسایی سریع و کارآمد این مسائل فراهم کرده‌اند. این گزارش به شما کمک می‌کند تا ریشه‌ی مشکلات بن‌بست (deadlock) و قفل (blocking) را پیدا کرده و عملکرد پایگاه داده خود را به شکل چشمگیری بهبود بخشید.

گزارش فرآیندهای block شده چیست؟

گزارش فرآیندهای block شده (Blocked Process Report) یک خروجی XML است که SQL Server آن را زمانی تولید می‌کند که یک فرآیند برای مدت زمان مشخصی block بماند. این گزارش شامل جزئیات مهمی درباره فرآیندهای block شده و فرآیندهای blockکننده است، از جمله متن دستور SQL، منابع درگیر در قفل و اطلاعات مربوط به اتصالات.

برای فعال‌سازی این گزارش، باید آستانه فرآیند block شده (blocked process threshold) را پیکربندی کنید. این مقدار بر حسب ثانیه مشخص می‌کند که یک فرآیند قبل از اینکه گزارشی برای آن تولید شود، چه مدت باید block بماند. مقدار پیش‌فرض آن 0 است که به معنای غیرفعال بودن این قابلیت است. توصیه می‌شود این مقدار را برای شروع روی 5 یا 10 ثانیه تنظیم کنید تا گزارش‌هایی که بیش از حد تکراری نیستند را دریافت کنید. شما می‌توانید این آستانه را با استفاده از دستور sp_configure تنظیم کنید:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'blocked process threshold', 5; -- تنظیم آستانه به 5 ثانیه
GO
RECONFIGURE;
GO

پس از تنظیم این مقدار، SQL Server به صورت خودکار شروع به نظارت بر فرآیندهای block شده خواهد کرد و در صورت عبور از آستانه مشخص شده، گزارش مربوطه را تولید می‌کند.

نحوه دریافت گزارش فرآیندهای block شده

چندین روش برای دریافت و جمع‌آوری گزارش فرآیندهای block شده وجود دارد که در  SQL Server قابل استفاده هستند. هر کدام مزایا و معایب خود را دارند:

1. استفاده از SQL Trace یا SQL Server Profiler

یکی از رایج‌ترین روش‌ها برای جمع‌آوری این گزارش استفاده از SQL Trace یا رابط کاربری گرافیکی آن، SQL Server Profiler، است. شما می‌توانید یک تریس (trace) جدید ایجاد کرده و رویداد “Blocked process report” را در دسته “Locks” انتخاب کنید.

مراحل ایجاد تریس در SQL Server Profiler:

1. SQL Server Profiler را باز کنید.
2. یک تریس جدید (File > New Trace) ایجاد کنید.
3. در تب “Events Selection”، گزینه “Show all events” و “Show all columns” را فعال کنید.
4. رویداد “Blocked process report” را در دسته “Locks” پیدا کرده و انتخاب کنید.
5. در صورت نیاز، فیلترهایی را برای محدود کردن داده‌ها اعمال کنید (مثلاً بر اساس نام پایگاه داده یا نام برنامه).
6. ترایس را اجرا و داده‌ها را ذخیره کنید.

این رویداد را می‌توان به صورت برنامه‌ریزی شده با استفاده از رویه‌های سیستمی sp_trace_create، sp_trace_setevent و sp_trace_setfilter نیز پیکربندی کرد. در اینجا یک نمونه اسکریپت برای ایجاد یک تریس SQL برای جمع‌آوری گزارش فرآیندهای block شده آورده شده است:

-- Declare variables for trace properties
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 

-- Create a trace
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'C:\BlockedProcessReportTrace', @maxfilesize, NULL 
IF (@rc != 0) GOTO error

-- Set the trace event for Blocked process report (Event ID: 137, Class ID: 21)
-- Check sys.trace_events for exact IDs if needed
EXEC @rc = sp_trace_setevent @TraceID, 137, 1, ON  -- TextData
EXEC @rc = sp_trace_setevent @TraceID, 137, 9, ON  -- ClientProcessID
EXEC @rc = sp_trace_setevent @TraceID, 137, 12, ON -- SPID
EXEC @rc = sp_trace_setevent @TraceID, 137, 13, ON -- Duration
EXEC @rc = sp_trace_setevent @TraceID, 137, 14, ON -- StartTime
EXEC @rc = sp_trace_setevent @TraceID, 137, 26, ON -- EventClass
EXEC @rc = sp_trace_setevent @TraceID, 137, 28, ON -- ObjectName
EXEC @rc = sp_trace_setevent @TraceID, 137, 34, ON -- DatabaseName
EXEC @rc = sp_trace_setevent @TraceID, 137, 35, ON -- HostName
EXEC @rc = sp_trace_setevent @TraceID, 137, 64, ON -- ApplicationName
EXEC @rc = sp_trace_setevent @TraceID, 137, 11, ON -- LoginName

-- You can add filters here, for example:
-- EXEC @rc = sp_trace_setfilter @TraceID, 34, 0, 0, N'YourDatabaseName'

-- Start the trace
EXEC @rc = sp_trace_setstatus @TraceID, 1
IF (@rc != 0) GOTO error

SELECT TraceID = @TraceID, Status = 'Started'
GOTO finish

error:
SELECT 'Error creating trace: ', @rc
finish:
GO

2. استفاده از اعلان‌های رویداد (Event Notifications)

در SQL Server ، می‌توانید از اعلان‌های رویداد (Event Notifications) برای واکنش به رویداد “Blocked process report” استفاده کنید. این روش به شما امکان می‌دهد تا گزارش XML را مستقیماً به یک صف سرویس (Service Broker Queue) ارسال کرده و سپس به صورت برنامه‌ریزی شده آن را تجزیه و تحلیل کنید. این رویکرد برای اتوماسیون و واکنش بلادرنگ (real-time) به مسائل قفل بسیار مفید است.

برای پیاده‌سازی این روش، باید یک سرویس‌بروکر (Service Broker) پیکربندی کنید که شامل یک صف (queue)، سرویس (service) و مسیر (route) باشد، و سپس یک اعلان رویداد ایجاد کنید. این روش معمولاً در پایگاه داده msdb پیاده‌سازی می‌شود.

-- Enable Service Broker on msdb database if not already enabled
ALTER DATABASE msdb SET ENABLE_BROKER;
GO

-- Create a queue to hold the event notifications
CREATE QUEUE BlockedProcessQueue;
GO

-- Create a service for the queue
CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue
(
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

-- Create an event notification for the Blocked Process Report event
CREATE EVENT NOTIFICATION BlockedProcessEventNotification
ON SERVER 
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlockedProcessService', 'current database';
GO

پس از ایجاد اعلان رویداد، هر زمان که یک گزارش فرآیند block شده تولید شود، یک پیام XML به BlockedProcessQueue ارسال می‌شود. شما می‌توانید با استفاده از یک WAITFOR (RECEIVE ...) یا یک Job Agent SQL Server، این صف را پایش کرده و پیام‌ها را پردازش کنید.

مثال برای دریافت و خواندن داده‌ها از صف:

-- To read from the queue (example, usually done in a stored procedure or SQL Agent Job)
WAITFOR (
    RECEIVE TOP(1) message_body
    FROM BlockedProcessQueue
), TIMEOUT 5000;
GO

شما می‌توانید با استفاده از قابلیت‌های XML در SQL Server، اطلاعات موجود در message_body را تجزیه و تحلیل کنید. این امکان به شما می‌دهد تا جزئیات خاصی مانند spid فرآیند block شده، sqltext و منابع قفل شده را استخراج کنید:

-- Example of parsing the XML message
SELECT CAST(message_body AS XML).query('
    /EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid
').value('.', 'INT') AS BlockedSPID,
CAST(message_body AS XML).query('
    /EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/inputbuf
').value('.', 'NVARCHAR(MAX)') AS BlockedSQLText,
CAST(message_body AS XML).query('
    /EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid
').value('.', 'INT') AS BlockingSPID,
CAST(message_body AS XML).query('
    /EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf
').value('.', 'NVARCHAR(MAX)') AS BlockingSQLText,
CAST(message_body AS XML).query('
    /EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/waitresource
').value('.', 'NVARCHAR(256)') AS WaitResource
FROM BlockedProcessQueue WITH (NOLOCK) -- Use NOLOCK or a transaction for real-world scenarios
WHERE message_body IS NOT NULL;
GO

3. Extended Events (XEvents)

در SQL Server روش، Extended Events (XEvents) روش ترجیحی برای جمع‌آوری داده‌های عملکرد و رویدادها است. XEvents نسبت به SQL Trace سربار کمتری دارد و انعطاف‌پذیری بیشتری را ارائه می‌دهد. گزارش فرآیند block شده به صورت خودکار در سشن system_health (که به صورت پیش‌فرض فعال است) جمع‌آوری می‌شود، اما می‌توانید یک سشن سفارشی نیز ایجاد کنید.

ایجاد یک سشن Extended Events سفارشی برای گزارش فرآیندهای block شده:

-- Create the event session
CREATE EVENT SESSION [BlockedProcessMonitor]
ON SERVER 
    ADD EVENT sqlserver.blocked_process_report (
        ACTION (sqlserver.sql_text, sqlserver.session_id, sqlserver.database_name, sqlserver.client_app_name, sqlserver.username)
    )
    ADD TARGET package0.ring_buffer (
        SET max_memory=(4096)
    )
    -- Or use asynchronous_file_target for persistent storage
    -- ADD TARGET package0.asynchronous_file_target (
    --     SET filename=N'C:\temp\BlockedProcessMonitor.xel',
    --     max_file_size=(5),
    --     max_rollover_files=(4)
    -- )
WITH (
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

-- Start the event session
ALTER EVENT SESSION [BlockedProcessMonitor] ON SERVER STATE = START;
GO

برای خواندن داده‌ها از یک سشن Extended Events که از ring_buffer یا asynchronous_file_target استفاده می‌کند، می‌توانید از تابع سیستمی sys.fn_xe_file_target_read_file استفاده کنید:

SELECT 
    CAST(event_data AS XML) AS event_data_xml
FROM sys.fn_xe_file_target_read_file('C:\temp\BlockedProcessMonitor*.xel', NULL, NULL, NULL);

-- For ring_buffer target (replace BlockedProcessMonitor with your session name)
SELECT
    CAST(event_data AS XML) AS event_data_xml
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address
WHERE xes.name = 'BlockedProcessMonitor'
AND xet.target_name = 'ring_buffer';
GO

تفسیر گزارش فرآیندهای block شده

گزارش فرآیند block شده یک سند XML است که ساختار مشخصی دارد. درک این ساختار برای تجزیه و تحلیل موثر بسیار مهم است. ساختار اصلی شامل دو گره اصلی <blocked-process> و <blocking-process> است که هر کدام اطلاعات دقیقی درباره فرآیند مربوطه ارائه می‌دهند.

یک مثال از ساختار XML این گزارش:

<blocked-process-report monitorLoop="509" >
 <blocked-process>
  <process id="process25b42d8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594038845440 (c6130d22f6d0)" waittime="5001" ownerId="1361285" transactionname="user_transaction" lasttranstarted="2023-10-27T10:00:05.120" XDES="0x2b866c1e0" lockMode="S" schedulerid="5" kpid="2416" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-10-27T10:00:05.120" lastbatchcompleted="2023-10-27T10:00:05.120" clientapp="Microsoft SQL Server Management Studio" hostname="DEVMACHINE" hostpid="1234" loginname="DOMAIN\User" isolationlevel="read committed" xactid="1361285" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <inputbuf>
    SELECT * FROM TestTable WHERE ID = 1;
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process id="process25b42d8" taskpriority="0" logused="0" waitresource="0x0" waittime="0" ownerId="1361285" transactionname="user_transaction" lasttranstarted="2023-10-27T10:00:00.000" XDES="0x2b866c1e0" lockMode="X" schedulerid="5" kpid="2416" status="running" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-10-27T10:00:00.000" lastbatchcompleted="2023-10-27T10:00:00.000" clientapp="Microsoft SQL Server Management Studio" hostname="DEVMACHINE" hostpid="5678" loginname="DOMAIN\User" isolationlevel="read committed" xactid="1361285" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <inputbuf>
    BEGIN TRAN; UPDATE TestTable SET Value = 'New' WHERE ID = 1;
   </inputbuf&gt>
  </process>
 </blocking-process>
</blocked-process-report>

مهمترین اطلاعاتی که باید در این گزارش به دنبال آن باشید:

• <blocked-process> و <blocking-process>: این گره‌ها به وضوح نشان می‌دهند که کدام فرآیند block شده و کدام فرآیند آن را block کرده است.
• spid: Session ID (شناسه جلسه) برای هر دو فرآیند. این مورد برای یافتن فرآیندها در sys.dm_exec_requests یا sys.sysprocesses حیاتی است.
• waittime: مدت زمانی (بر حسب میلی‌ثانیه) که فرآیند block شده منتظر مانده است.
• waitresource: منبعی که فرآیند block شده منتظر آن است. این می‌تواند یک کلید (KEY)، یک صفحه (PAGE)، یک سطر (RID) یا یک شی (OBJECT) باشد. فرمت آن شامل نام پایگاه داده، فایل، صفحه یا کلید است (مثلاً KEY: 6:72057594038845440 (c6130d22f6d0)).
• inputbuf: دستور SQL که توسط فرآیند در حال اجرا است. این یکی از مهمترین بخش‌ها برای تشخیص دقیق مشکل است.
• lockMode: نوع قفلی که توسط فرآیند blockکننده روی منبع نگه داشته شده است (مثلاً S برای Shared، X برای Exclusive).
• clientapp، hostname، loginname: اطلاعاتی درباره کلاینت، میزبان و کاربر مسئول فرآیندها.

استفاده از گزارش برای حل مشکل block شدن

پس از جمع‌آوری و تفسیر گزارش، گام بعدی حل مشکل است. این گزارش به شما کمک می‌کند تا استراتژی‌های موثری را پیاده‌سازی کنید:

1. شناسایی فرآیندهای اصلی: با استفاده از spidهای موجود در گزارش، فرآیندهای block شده و blockکننده را در SQL Server شناسایی کنید. می‌توانید از دستور KILL برای خاتمه دادن به فرآیند blockکننده (در صورت لزوم و با احتیاط زیاد) استفاده کنید، اما این یک راه حل موقت است.
2. تجزیه و تحلیل دستور SQL: دستورات SQL موجود در inputbuf هر دو فرآیند را بررسی کنید. به دنبال الگوهای کوئری غیربهینه، تراکنش‌های طولانی یا قفل‌های نالازم باشید.
3. شناسایی منابع قفل شده: از waitresource برای تعیین اینکه کدام شیء (جدول، ایندکس، صفحه، سطر) دلیل اصلی قفل است، استفاده کنید. این به شما کمک می‌کند تا روی بهینه‌سازی هدفمند تمرکز کنید.
4. راه حل‌های بهینه‌سازی:
   • تنظیم ایندکس: اضافه کردن ایندکس‌های مناسب یا بازسازی ایندکس‌های موجود می‌تواند به کاهش زمان قفل شدن کمک کند.
   • بازنویسی کوئری: بهینه‌سازی دستورات SQL، کاهش تعداد قفل‌ها یا تغییر منطق تراکنش‌ها می‌تواند مشکل را حل کند.
   • مدیریت تراکنش: مطمئن شوید که تراکنش‌ها تا حد امکان کوتاه باشند و فقط داده‌های ضروری را قفل کنند. استفاده از سطح ایزوله‌سازی تراکنش (transaction isolation level) مناسب (مانند READ COMMITTED SNAPSHOT در SQL Server ) می‌تواند به کاهش قفل‌ها کمک کند.
   • تقسیم عملیات بزرگ: شکستن تراکنش‌های بزرگ به تراکنش‌های کوچکتر می‌تواند زمان نگهداری قفل را کاهش دهد.

نتیجه‌گیری

گزارش فرآیندهای block شده در SQL Server یک ابزار تشخیصی فوق‌العاده قدرتمند است که به شما امکان می‌دهد تا به سرعت ریشه‌ی مشکلات قفل و بن‌بست را در محیط‌های SQL Server شناسایی کنید. با فعال‌سازی این قابلیت و استفاده از SQL Trace، اعلان‌های رویداد یا Extended Events، می‌توانید به طور فعال بر وضعیت پایگاه داده خود نظارت کرده و با تجزیه و تحلیل دقیق گزارش‌های XML، عملکرد سیستم را بهینه سازید. درک و استفاده صحیح از این گزارش می‌تواند به بهبود پایداری، سرعت و تجربه کاربری برنامه‌های وابسته به SQL Server کمک شایانی کند.