مقدمه

Blocking و Deadlock دو پدیده مرتبط اما متفاوت در کنترل هم‌زمانی SQL Server هستند. Lock برای حفظ سازگاری لازم است؛ مشکل زمانی آغاز می‌شود که Transaction طولانی، ترتیب دسترسی متضاد، Index نامناسب یا مدیریت نادرست Connection باعث صف انتظار، Timeout یا چرخه Deadlock شود. این راهنما یک نقشه عملی از مشاهده لحظه‌ای تا جمع‌آوری تاریخی، تحلیل XML و اقدام کنترل‌شده ارائه می‌کند.

هدف مقاله صرفاً فهرست‌کردن DMVها نیست. هر ابزار باید پاسخ یک سؤال مشخص را بدهد: چه Sessionی منتظر است، چه Sessionی منبع را نگه داشته، چه فرمانی اجرا شده، Transaction از چه زمانی باز است، Deadlock روی کدام Resource رخ داده و اصلاح پایدار در کد، Index یا مرز Transaction چیست. لینک هر ابزار به آموزش مستقل آن در همین صفحه قرار دارد.

فهرست دسترسی سریع

• sys.dm_tran_locks: نمایش قفل‌های فعال
• sys.dm_exec_requests: پایش درخواست‌های در حال اجرا
• sys.dm_exec_sessions: تحلیل نشست‌ها و اتصال‌های کاربری
• sys.dm_os_waiting_tasks: تحلیل وظایف منتظر
• sys.dm_exec_input_buffer: مشاهده آخرین فرمان ورودی نشست
• sp_who: بررسی سریع نشست‌ها با sp_who
• sp_who2 (Undocumented): کار با sp_who2 مستندنشده
• sp_lock (Deprecated): مهاجرت از sp_lock منسوخ
• KILL session_id: پایان امن نشست با KILL
• KILL session_id WITH STATUSONLY: پیگیری پیشرفت Rollback با KILL STATUSONLY
• Blocked Process Report: تحلیل گزارش فرایند مسدودشده
• Deadlock Graph: خواندن و رفع Deadlock Graph
• xml_deadlock_report Extended Event: جمع‌آوری xml_deadlock_report با Extended Events
• blocked_process_report Extended Event: جمع‌آوری blocked_process_report با Extended Events
• system_health Extended Events Session: استفاده از نشست system_health

Blocking، Lock و Deadlock چگونه شکل می‌گیرند؟

هر Transaction هنگام خواندن یا تغییر داده، بسته به Isolation Level و نوع عملیات Lockهایی می‌گیرد. Session دیگر اگر Lock ناسازگار روی همان Resource بخواهد منتظر می‌ماند. این انتظار می‌تواند کاملاً طبیعی و چند میلی‌ثانیه‌ای باشد؛ معیار مسئله‌بودن، مدت، تکرار، تعداد درخواست‌های متأثر و اثر بر SLA است.

Head Blocker معمولاً Sessionی است که خودش منتظر Session کاربری دیگری نیست اما چند Request پشت آن صف کشیده‌اند. این Session ممکن است فعال باشد یا پس از اجرای Batch به حالت Sleeping رفته باشد و Transaction باز را رها کرده باشد. به همین دلیل نگاه‌کردن به dm_exec_requests بدون dm_exec_sessions و input_buffer همیشه کافی نیست.

Deadlock زمانی رخ می‌دهد که دست‌کم دو مسیر، Resourceهای نگه‌داشته‌شده توسط یکدیگر را بخواهند و چرخه‌ای بدون راه خروج شکل بگیرد. SQL Server هزینه Rollback و DEADLOCK_PRIORITY را ارزیابی می‌کند، یک Victim انتخاب می‌کند و خطای 1205 می‌دهد. Victim لزوماً عامل طراحی بد نیست؛ Graph کامل باید Owner، Waiter و ترتیب دسترسی را اثبات کند.

اصل راهنما: ابتدا شواهد را حفظ کنید، سپس اثر کسب‌وکار را بسنجید و در پایان اقدام کنید. KILL، تغییر Threshold یا ساخت Index نباید جای تحلیل علت ریشه‌ای را بگیرد.

زمان، UTC و Offset در تحلیل رخداد

Timeline دقیق ستون فقرات تحلیل Blocking و Deadlock است. برای ذخیره زمان نمونه‌برداری از datetime2 با دقت مناسب یا datetimeoffset استفاده کنید؛ datetime قدیمی دقت کمتر و دامنه متفاوتی دارد. Eventهای Extended Events معمولاً Timestamp را در UTC ارائه می‌کنند و باید اصل UTC در آرشیو دست‌نخورده بماند.

زمان محلی را در لایه گزارش با Offset روشن نمایش دهید و هنگام تغییر ساعت رسمی یا کار میان چند منطقه زمانی، رشته محلی بدون Offset ذخیره نکنید. برای Correlation با Log برنامه، Query Store و Ticket، علاوه بر زمان UTC شناسه Incident، Server، Database و Application را ثبت کنید. در نمونه‌برداری سریع، دقت میلی‌ثانیه‌ای مهم است اما ساعت‌های همه میزبان‌ها نیز باید همگام باشند.

Published Date مقاله یا زمان اجرای Query نباید با زمان رخداد اشتباه شود. هر Snapshot DMV زمان ثبت جداگانه لازم دارد؛ Ring Buffer و Event File نیز Retention محدود دارند. اگر Timeline شکاف دارد، آن را با حدس پر نکنید و به‌صراحت نبود داده را در گزارش بنویسید.

دسته‌بندی ابزارهای پایش

• وضعیت قفل و Request: sys.dm_tran_locks، sys.dm_exec_requests و sys.dm_exec_sessions.
• Wait و متن فرمان: sys.dm_os_waiting_tasks و sys.dm_exec_input_buffer.
• ابزارهای قدیمی و مهاجرت: sp_who، sp_who2 و sp_lock.
• اقدام و Rollback: KILL session_id و KILL WITH STATUSONLY.
• شواهد تاریخی: Blocked Process Report، Deadlock Graph و Sessionهای Extended Events.

این دسته‌بندی یک Workflow نیز پیشنهاد می‌کند: ابتدا DMV سبک و فیلترشده، سپس متن و Transaction نامزدها، بعد Event تاریخی، و تنها در پایان اقدام مدیریتی. جابه‌جایی این ترتیب می‌تواند شواهد را نابود یا Rollback بزرگی ایجاد کند.

جدول مقایسه ابزارها

معرفی همه ابزارها

sys.dm_tran_locks در SQL Server

نمای مدیریتی sys.dm_tran_locks تصویری لحظه‌ای از درخواست‌ها و قفل‌های اعطاشده یا در حال انتظار در موتور پایگاه داده ارائه می‌کند. هر ردیف، مالک قفل، نوع منبع، حالت قفل و وضعیت درخواست را نشان می‌دهد. برخلاف sp_lock که قدیمی و محدود است، این DMV ستون‌های دقیق‌تری درباره مالک و منبع قفل می‌دهد و مبنای مناسب‌تری برای ابزارهای مانیتورینگ است.

ابتدا بر اساس پایگاه داده، نشست یا وضعیت WAIT فیلتر کنید و فقط ستون‌های لازم را بخوانید؛ نمونه‌برداری بسیار پرتکرار از محیط شلوغ می‌تواند خودش هزینه ایجاد کند. مطالعه آموزش کامل sys.dm_tran_locks با ده مثال عملی

sys.dm_exec_requests در SQL Server

نمای sys.dm_exec_requests هر درخواست فعال در SQL Server را با وضعیت اجرا، زمان CPU، خواندن‌ها، Wait جاری، نشست مسدودکننده و درصد پیشرفت عملیات طولانی نشان می‌دهد. sys.dm_exec_sessions عمر یک نشست را توصیف می‌کند، اما sys.dm_exec_requests فقط کار فعال همان لحظه را نشان می‌دهد؛ اتصال این دو نمای کامل‌تری می‌سازد.

ستون‌های پرهزینه مانند Plan XML را فقط برای نامزدهای فیلترشده دریافت کنید و از CROSS APPLY بدون شرط روی همه درخواست‌ها در نمونه‌برداری سریع بپرهیزید. مطالعه آموزش کامل sys.dm_exec_requests با ده مثال عملی

sys.dm_exec_sessions در SQL Server

نمای sys.dm_exec_sessions اطلاعات سطح Session مانند نام ورود، میزبان، برنامه، وضعیت، زمان آخرین درخواست، مصرف تجمعی CPU و تعداد تراکنش باز را نگه می‌دارد. یک نشست می‌تواند در طول عمر خود چند Request متوالی داشته باشد؛ به همین دلیل داده‌های تجمعی این DMV با اندازه‌گیری لحظه‌ای dm_exec_requests یکسان نیست.

برای داشبوردها روی is_user_process و session_id فیلتر کنید و گروه‌بندی را با دوره نمونه‌برداری معقول انجام دهید. مطالعه آموزش کامل sys.dm_exec_sessions با ده مثال عملی

sys.dm_os_waiting_tasks در SQL Server

نمای sys.dm_os_waiting_tasks انتظارهای جاری را در سطح Task نمایش می‌دهد و برای تشخیص زنجیره Blocking، قفل، Latch، I/O و انتظارهای موازی‌سازی بسیار ارزشمند است. dm_exec_requests یک Wait اصلی در سطح Request می‌دهد، ولی dm_os_waiting_tasks جزئیات Taskهای موازی و شرح منبع انتظار را آشکار می‌کند.

برای کاهش نویز، انتظارهای کوتاه را با wait_duration_ms فیلتر و نتایج را پیش از اتصال به DMVهای متن و Plan محدود کنید. مطالعه آموزش کامل sys.dm_os_waiting_tasks با ده مثال عملی

sys.dm_exec_input_buffer در SQL Server

تابع مدیریتی sys.dm_exec_input_buffer متن آخرین Batch یا RPC ارسال‌شده برای یک Session و Request را برمی‌گرداند و به شناسایی فرمان Blocker حتی در حالت Sleeping کمک می‌کند. این تابع جایگزین ساخت‌یافته و قابل اتصال DBCC INPUTBUFFER است و برخلاف dm_exec_sql_text می‌تواند آخرین ورودی یک نشست Sleeping را نیز نشان دهد.

ابتدا Sessionهای هدف را محدود کنید و سپس APPLY بزنید؛ فراخوانی تابع برای همه Sessionها در حلقه سریع ضرورتی ندارد. مطالعه آموزش کامل sys.dm_exec_input_buffer با ده مثال عملی

sp_who در SQL Server

رویه سیستمی sp_who فهرستی فشرده از Sessionها، وضعیت، Login، میزبان، پایگاه داده، فرمان و شناسه Blocker ارائه می‌کند و برای بررسی سریع خط فرمان مناسب است. sp_who مستند و پایدار است، اما DMVها ستون‌های کارایی، Wait و متن Query را دقیق‌تر و قابل ترکیب‌تر ارائه می‌کنند.

برای بررسی دستی سبک است؛ برای مانیتورینگ خودکار بهتر است DMVهای مستند با ستون‌های صریح استفاده شوند. مطالعه آموزش کامل sp_who با ده مثال عملی

sp_who2 (Undocumented) در SQL Server

رویه sp_who2 خروجی توسعه‌یافته‌ای شامل CPUTime، DiskIO، LastBatch و ProgramName می‌دهد، اما مستند رسمی و قرارداد پایداری ندارد. sp_who2 برای عیب‌یابی تعاملی محبوب است، ولی sys.dm_exec_sessions و sys.dm_exec_requests جایگزین مستند و قابل اتکاتری هستند.

اجرای موردی معمولاً سبک است؛ ذخیره خروجی آن با INSERT EXEC در سامانه تولیدی به علت تغییرپذیری Schema شکننده است. مطالعه آموزش کامل sp_who2 (Undocumented) با ده مثال عملی

sp_lock (Deprecated) در SQL Server

رویه sp_lock اطلاعات قفل‌های جاری را به شکل قدیمی نمایش می‌دهد. این قابلیت Deprecated است و برای توسعه جدید باید به sys.dm_tran_locks مهاجرت کرد. sys.dm_tran_locks منبع، مالک، حالت درخواست و وضعیت را با جزئیات و نام‌گذاری مدرن فراهم می‌کند؛ sp_lock صرفاً برای سازگاری قدیمی مناسب است.

به جای Polling مداوم sp_lock، Query هدفمند روی DMV جدید و ثبت دوره‌ای با فاصله منطقی بسازید. مطالعه آموزش کامل sp_lock (Deprecated) با ده مثال عملی

KILL session_id در SQL Server

دستور KILL یک Session مشخص را خاتمه می‌دهد و اگر تراکنش باز داشته باشد SQL Server تغییرات آن را Rollback می‌کند. این دستور درمان علت Blocking نیست و باید آخرین اقدام کنترل‌شده باشد. لغو Query از سمت برنامه یا اصلاح Timeout کم‌خطرتر است؛ KILL کل Session و تراکنش‌های آن را هدف می‌گیرد.

Rollback تراکنش بزرگ می‌تواند I/O و Log قابل توجهی مصرف کند؛ پیش از KILL اندازه تراکنش، Blocker بودن و اثر کسب‌وکار را بررسی کنید. مطالعه آموزش کامل KILL session_id با ده مثال عملی

KILL session_id WITH STATUSONLY در SQL Server

عبارت KILL session_id WITH STATUSONLY فقط گزارش پیشرفت Rollback نشست قبلاً خاتمه‌یافته را درخواست می‌کند و Session تازه‌ای را نمی‌کشد. dm_exec_requests داده‌های قابل Query می‌دهد، اما WITH STATUSONLY پیام رسمی موتور درباره Rollback همان SPID را ارائه می‌کند.

این دستور Rollback را سریع‌تر نمی‌کند؛ Polling با فاصله مناسب انجام دهید تا فقط وضعیت را پایش کنید. مطالعه آموزش کامل KILL session_id WITH STATUSONLY با ده مثال عملی

Blocked Process Report در SQL Server

Blocked Process Report یک سند XML است که پس از عبور Blocking از آستانه تنظیم‌شده تولید می‌شود و اطلاعات Blocked، Blocker، منبع انتظار و Input Buffer را ثبت می‌کند. برخلاف Snapshot لحظه‌ای DMV، گزارش پس از گذشت آستانه ساخته می‌شود و شواهد تاریخی قابل نگهداری از دو سوی Blocking می‌دهد.

آستانه بسیار پایین در بارکاری پرتراکنش Event Storm ایجاد می‌کند؛ معمولاً مقدار ۵ ثانیه یا بیشتر با توجه به SLA انتخاب می‌شود. مطالعه آموزش کامل Blocked Process Report با ده مثال عملی

Deadlock Graph در SQL Server

Deadlock Graph یک سند XML از چرخه وابستگی منابع است که قربانی انتخاب‌شده، Processها، قفل‌های نگه‌داشته و درخواستی و Batchهای درگیر را نشان می‌دهد. Blocked Process یک انتظار یک‌طرفه و قابل ادامه است؛ Deadlock یک چرخه است که موتور برای شکستن آن یک Transaction را قربانی می‌کند.

به جای جمع‌آوری Plan و متن سنگین برای همه Queryها، Deadlock XML را نگه دارید و تنها رخدادهای مرتبط را غنی‌سازی کنید. مطالعه آموزش کامل Deadlock Graph با ده مثال عملی

xml_deadlock_report Extended Event در SQL Server

رویداد sqlserver.xml_deadlock_report متن کامل Deadlock XML را در Extended Events ثبت می‌کند و روش استاندارد برای آرشیو و تحلیل Deadlockهای SQL Server است. system_health همین Event را به‌صورت پیش‌فرض می‌گیرد، ولی Session اختصاصی Retention، مسیر فایل و چرخه عملیاتی قابل کنترل‌تری می‌دهد.

Event کم‌حجم است، اما Ring Buffer محدود و Event File نیازمند سقف اندازه و تعداد Rollover مشخص است. مطالعه آموزش کامل xml_deadlock_report Extended Event با ده مثال عملی

blocked_process_report Extended Event در SQL Server

رویداد sqlserver.blocked_process_report گزارش XML Blocking طولانی‌تر از آستانه سرور را در یک Session سبک Extended Events دریافت می‌کند. Event با همین نام حامل گزارش است، درحالی‌که Blocked Process Report خود ساختار XML و معنای تشخیصی داده را توصیف می‌کند.

Threshold، مقصد Event File، max_file_size و max_rollover_files را طوری تنظیم کنید که حجم تکرار گزارش کنترل شود. مطالعه آموزش کامل blocked_process_report Extended Event با ده مثال عملی

system_health Extended Events Session در SQL Server

system_health یک Extended Events Session داخلی و پیش‌فرض است که شواهد مهمی مانند Deadlock، خطاهای شدید، مشکلات Scheduler و فشار حافظه را با هزینه کم جمع می‌کند. system_health نقطه شروع فوری است، اما برای SLA و نگهداری بلندمدت جای Session اختصاصی با سیاست Retention را نمی‌گیرد.

Session را دست‌کاری یا متوقف نکنید؛ Query خواندن را به Eventهای هدف و بازه زمانی لازم محدود کنید. مطالعه آموزش کامل system_health Extended Events Session با ده مثال عملی

شش مثال ترکیبی و کاربردی

مثال 1: نمای فوری Blocked و Blocker

در این سناریو هدف، ساخت اولین تصویر Incident و تشخیص رابطه مستقیم Blocking است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

SELECT
        r.session_id AS blocked_session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        DB_NAME(r.database_id) AS database_name,
        ib.event_info AS blocked_input
    FROM sys.dm_exec_requests AS r
    OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) AS ib
    WHERE r.blocking_session_id > 0
    ORDER BY r.wait_time DESC;
    

Blocker ریشه‌ای ممکن است در بالای زنجیره باشد؛ Session 57 را با sessions، input_buffer و Transactionها ادامه دهید. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

مثال 2: یافتن Head Blocker و تعداد قربانی‌ها

در این سناریو هدف، اولویت‌بندی Blockerهای مستقیم و آشکارکردن نشست خواب دارای Transaction باز است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

;WITH blocked AS
    (
        SELECT session_id, blocking_session_id
        FROM sys.dm_exec_requests
        WHERE blocking_session_id > 0
    )
    SELECT
        b.blocking_session_id AS blocker_session_id,
        COUNT_BIG(*) AS direct_blocked_count,
        s.login_name,
        s.host_name,
        s.status,
        s.open_transaction_count
    FROM blocked AS b
    JOIN sys.dm_exec_sessions AS s
      ON s.session_id = b.blocking_session_id
    GROUP BY b.blocking_session_id, s.login_name, s.host_name, s.status, s.open_transaction_count
    ORDER BY direct_blocked_count DESC;
    

تعداد قربانی‌ها معیار اثر است، اما پیش از هر اقدام هزینه Rollback و حیاتی بودن سرویس را بسنجید. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

مثال 3: ترکیب Lock و Waiting Task

در این سناریو هدف، کنار هم گذاشتن Wait سطح Task و درخواست Lock اعطانشده است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

SELECT
        wt.session_id AS blocked_session_id,
        wt.blocking_session_id,
        wt.wait_type,
        wt.wait_duration_ms,
        l.resource_type,
        l.request_mode,
        l.request_status,
        wt.resource_description
    FROM sys.dm_os_waiting_tasks AS wt
    LEFT JOIN sys.dm_tran_locks AS l
      ON l.request_session_id = wt.session_id
     AND l.request_status = N'WAIT'
    WHERE wt.blocking_session_id > 0
    ORDER BY wt.wait_duration_ms DESC;
    

در Query موازی یا چند Lock ممکن است ردیف تکرار شود؛ Scope را قبل از آرشیو بر اساس Request تجمیع کنید. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

مثال 4: استخراج Statement جاری و Batch

در این سناریو هدف، تفکیک Statement فعال از Batch کامل برای Blocker و Blocked است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

SELECT
        r.session_id,
        SUBSTRING(
            st.text,
            (r.statement_start_offset / 2) + 1,
            ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1
        ) AS current_statement,
        st.text AS full_batch
    FROM sys.dm_exec_requests AS r
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
    WHERE r.session_id IN (57, 64);
    

متن SQL ممکن است داده حساس داشته باشد؛ دسترسی و Retention آن را در سامانه Incident محدود کنید. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

مثال 5: گرفتن Deadlock Graph از system_health

در این سناریو هدف، بازیابی شواهد Deadlock اخیر از Collector پیش‌فرض است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

;WITH sh AS
    (
        SELECT CAST(t.target_data AS xml) AS target_xml
        FROM sys.dm_xe_session_targets AS t
        JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
        WHERE s.name = N'system_health'
          AND t.target_name = N'ring_buffer'
    )
    SELECT
        e.n.value('@timestamp', 'datetime2') AS event_utc,
        e.n.query('(data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_xml
    FROM sh
    CROSS APPLY target_xml.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS e(n)
    ORDER BY event_utc DESC;
    

Ring Buffer محدود است؛ Graph مهم را با Timestamp UTC در مخزن جدا و امن نگهداری کنید. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

مثال 6: پایش Rollback پس از KILL

در این سناریو هدف، جداسازی مرحله پایش Rollback از تصمیم اولیه KILL است. Query زیر یک نمونه مستقل و قابل اجرا برای محیط مناسب ارائه می‌کند؛ فرمان‌های تغییردهنده باید ابتدا در Lab و با مجوز کنترل‌شده آزموده شوند.

SELECT
        session_id,
        command,
        percent_complete,
        total_elapsed_time / 1000.0 AS elapsed_seconds,
        estimated_completion_time / 1000.0 AS estimated_seconds,
        N'KILL ' + CONVERT(nvarchar(11), session_id) + N' WITH STATUSONLY;' AS status_command
    FROM sys.dm_exec_requests
    WHERE command = N'KILLED/ROLLBACK';
    

Rollback را متوقف یا دوباره KILL نکنید؛ STATUSONLY سرعت را تغییر نمی‌دهد و فقط وضعیت گزارش می‌کند. این نتیجه نمونه کمک می‌کند پیش از اجرا شکل خروجی را بشناسید، اما جای مشاهده داده واقعی همان Incident را نمی‌گیرد.

Workflow استاندارد پاسخ به Incident

1. زمان UTC، Server، Database، اثر بر SLA و شناسه Incident را ثبت کنید.
2. با dm_exec_requests صف Blocked و Blocker مستقیم را ببینید.
3. با waiting_tasks و tran_locks نوع Wait و Resource را تأیید کنید.
4. Session، Input Buffer، Transaction باز و زمان شروع را برای Head Blocker جمع کنید.
5. برای رخداد گذشته، Blocked Process Report یا Deadlock XML را از XE بازیابی کنید.
6. پیش از KILL، هویت SPID، مالک سرویس، اندازه Transaction و هزینه Rollback را بسنجید.
7. پس از بازیابی سرویس، علت دائمی را در Query، Index، ترتیب دسترسی یا مرز Transaction اصلاح کنید.
8. نتیجه Deploy را با Baseline و تکرار رخداد اندازه بگیرید و Runbook را به‌روز کنید.

این Workflow باید در تمرین Game Day آزموده شود. تیم Application و DBA باید بدانند چه کسی حق مشاهده، چه کسی حق تأیید KILL و چه کسی مسئول اصلاح کد است. نبود نقش روشن باعث می‌شود در لحظه Incident چند نفر Queryهای سنگین یا اقدام‌های متناقض اجرا کنند.

خطاهای رایج و اصلاح آن‌ها

• KILL کردن Victim یا اولین SPID دیده‌شده؛ اصلاح: Head Blocker و چرخه را با شواهد اثبات کنید.
• تعبیر هر Wait به Blocking؛ اصلاح: Waitهای Lock را از I/O، Latch، Memory و CPU جدا کنید.
• وابستگی Production به sp_who2 یا sp_lock؛ اصلاح: Query مستند DMV با ستون صریح بسازید.
• Threshold بسیار پایین بدون Event File محدود؛ اصلاح: نرخ Event، SLA و Retention را طراحی کنید.
• ذخیره زمان محلی بدون Offset؛ اصلاح: UTC یا datetimeoffset و همگام‌سازی ساعت میزبان‌ها.
• ساخت Index از روی نام Resource بدون Plan؛ اصلاح: Predicate، Cardinality و هزینه DML را بررسی کنید.

رفع خطا باید قابل اندازه‌گیری باشد. کاهش تعداد Deadlock، مدت Blocking، Timeout برنامه و زمان بازیابی شاخص‌های بهتری از صرفاً بسته‌شدن Ticket هستند. اگر پس از Deploy فقط Victim عوض شده، علت چرخه هنوز باقی است.

Performance و طراحی Collector

Collector خوب کم‌هزینه، محدود و قابل توضیح است. ابتدا ستون‌های عددی و Scope نامزدها را جمع کنید؛ متن SQL، Plan XML و Input Buffer را فقط برای Sessionهای Blocked، Blocker یا طولانی بگیرید. دوره نمونه‌برداری زیرثانیه‌ای بدون سؤال مشخص معمولاً حجم و سربار می‌سازد.

برای Event File حداکثر اندازه، تعداد Rollover، فضای Volume، ACL سرویس و Job انتقال را مشخص کنید. Ring Buffer برای بررسی سریع است و آرشیو دائمی نیست. Dropped Event، مدت اجرای Query Collector و اندازه هر Batch مانیتورینگ باید خودشان Alert داشته باشند.

داده مانیتورینگ می‌تواند شامل متن پارامتر، نام مشتری یا مسیر داخلی باشد. Masking، کنترل دسترسی، Retention و حذف امن بخشی از Performance Engineering نیستند، اما بخشی جدایی‌ناپذیر از طراحی قابل استفاده Production محسوب می‌شوند.

سؤالات متداول

پرسش 1: Blocking در SQL Server همیشه مشکل است؟

خیر. Lock و انتظار کوتاه بخش طبیعی سازگاری تراکنشی است. Blocking زمانی مشکل عملیاتی می‌شود که مدت، تعداد نشست‌های متأثر یا اثر آن از SLA عبور کند؛ بنابراین آستانه باید با Baseline هر سرویس تعریف شود.

پرسش 2: Deadlock چه تفاوتی با Blocking طولانی دارد؟

در Blocking یک Session می‌تواند پس از آزادشدن منبع ادامه دهد، اما در Deadlock یک چرخه وابستگی شکل می‌گیرد و موتور برای شکستن چرخه یکی از Transactionها را با خطای 1205 قربانی می‌کند. Deadlock Graph برای اثبات چرخه ضروری است.

پرسش 3: پایش حرفه‌ای Blocking چه سودی برای کسب‌وکار دارد؟

زمان تشخیص و بازیابی کوتاه‌تر، Timeout کمتر برای مشتری، تصمیم امن‌تر درباره KILL و کشف نقاط پرتکرار کد از نتایج مستقیم است. گزارش باید اثر فنی را به تعداد درخواست، درآمد یا فرایند کسب‌وکار متصل کند.

پرسش 4: چه زمانی پروژه مانیتورینگ اختصاصی SQL Server توجیه دارد؟

وقتی چند سرویس، SLA متفاوت، رخداد تکراری یا الزام نگهداری شواهد دارید، Collector، Event File، Dashboard و Runbook اختصاصی ارزشمند است. طراحی باید با Load Test و سیاست امنیتی همراه باشد.

پرسش 5: DMV بهتر است یا Extended Events؟

DMV برای Snapshot زنده و Extended Events برای تاریخچه رخداد مناسب است. این دو رقیب نیستند؛ DMV وضعیت اکنون را توضیح می‌دهد و XE شواهد زمانی مانند Deadlock XML یا Blocked Process Report را حفظ می‌کند.

پرسش 6: آیا خدمات تحلیل Deadlock باید فقط Graph را تحویل دهد؟

خیر. خروجی مفید شامل Timeline، Query و Plan مرتبط، ترتیب دسترسی، Indexهای درگیر، دلیل انتخاب Victim، راهکار کد یا Schema و روش آزمون پس از اصلاح است. Graph خام تنها نقطه شروع تحلیل است.

پرسش 7: رایج‌ترین خطای DBA هنگام Blocking چیست؟

KILL کردن اولین SPID دیده‌شده بدون تأیید Head Blocker، مالک Session و هزینه Rollback خطای پرخطر است. خطای دیگر، اشتباه گرفتن Victim با مقصر یا اتکا به یک Snapshot بدون زمان است.

پرسش 8: Collectorهای Blocking چه اثری بر Performance دارند؟

Queryهای فیلترشده DMV و Eventهای هدفمند معمولاً سبک‌اند، اما گرفتن Plan و Text همه Sessionها، Threshold بسیار پایین و Event File بدون سقف می‌تواند سربار یا حجم زیاد بسازد. هزینه خود Collector باید اندازه‌گیری شود.

پرسش 9: بهترین روش جلوگیری از Deadlock چیست؟

کوتاه نگه داشتن Transaction، ترتیب ثابت دسترسی به Resource، Index مناسب، حذف تعامل کاربر داخل Transaction و Retry محدود خطای 1205 راهکارهای اصلی‌اند. Priority یا KILL علت چرخه را رفع نمی‌کند.

پرسش 10: این روش‌ها در همه نسخه‌های SQL Server یکسان‌اند؟

مفاهیم ثابت‌اند، اما ستون DMV، مجوزهای جدید، Eventها و رفتار Azure SQL تفاوت دارد. Metadata همان سرور و مستندات نسخه مقصد مرجع نهایی است و قابلیت‌های Deprecated یا Undocumented باید در ارتقا حذف شوند.

سؤالات مصاحبه

سؤال 1: Head Blocker را چگونه پیدا می‌کنید؟

Edgeهای blocking_session_id را جمع می‌کنم، زنجیره را تا Sessionی که Blocker کاربری بالاتری ندارد دنبال می‌کنم و سپس Transaction، Input Buffer و Context برنامه را تأیید می‌کنم.

سؤال 2: چرا Victim همان مقصر Deadlock نیست؟

موتور بر اساس Priority و هزینه تقریبی Rollback Victim را انتخاب می‌کند. علت طراحی از چرخه Resource و ترتیب دسترسی Processها به دست می‌آید.

سؤال 3: چه زمانی KILL قابل دفاع است؟

وقتی اثر Blocking جدی است، Head Blocker و مالک آن تأیید شده، گزینه کم‌خطرتر وجود ندارد و هزینه Rollback سنجیده و اقدام ثبت و مجاز شده باشد.

سؤال 4: DMV و XE را چگونه ترکیب می‌کنید؟

XE تاریخچه و XML رخداد را حفظ می‌کند؛ DMV وضعیت فعلی Session، Request، Wait و Transaction را تکمیل می‌کند. Timeline مشترک این دو را به Incident متصل می‌کند.

سؤال 5: چگونه اثر اصلاح Deadlock را می‌سنجید؟

نرخ خطای 1205، Graphهای هم‌الگو، مدت Transaction، زمان پاسخ و Timeout را پیش و پس از Deploy با بار قابل مقایسه اندازه می‌گیرم.

جمع‌بندی و مسیر مطالعه

پایش Blocking و Deadlock یک Query جادویی ندارد. نتیجه قابل اعتماد از ترکیب Snapshot زنده، شواهد تاریخی، Timeline UTC، شناخت Transaction و Context Application ساخته می‌شود. ابزار قدیمی می‌تواند برای بررسی سریع مفید باشد، اما اتوماسیون باید بر قابلیت مستند، مجوز حداقلی و Query فیلترشده بنا شود.

در Incident ابتدا داده را حفظ کنید، اثر را بسنجید و سپس اقدام کنید. بعد از بازیابی سرویس، Transaction کوتاه‌تر، ترتیب ثابت Resource، Index مبتنی بر Plan و Retry محدود را به‌عنوان اصلاح پایدار بررسی کنید. برای مطالعه جزئیات هر بخش از لینک‌های زیر استفاده کنید.

• sys.dm_tran_locks: آموزش مستقل، مثال‌ها و چک‌لیست
• sys.dm_exec_requests: آموزش مستقل، مثال‌ها و چک‌لیست
• sys.dm_exec_sessions: آموزش مستقل، مثال‌ها و چک‌لیست
• sys.dm_os_waiting_tasks: آموزش مستقل، مثال‌ها و چک‌لیست
• sys.dm_exec_input_buffer: آموزش مستقل، مثال‌ها و چک‌لیست
• sp_who: آموزش مستقل، مثال‌ها و چک‌لیست
• sp_who2 (Undocumented): آموزش مستقل، مثال‌ها و چک‌لیست
• sp_lock (Deprecated): آموزش مستقل، مثال‌ها و چک‌لیست
• KILL session_id: آموزش مستقل، مثال‌ها و چک‌لیست
• KILL session_id WITH STATUSONLY: آموزش مستقل، مثال‌ها و چک‌لیست
• Blocked Process Report: آموزش مستقل، مثال‌ها و چک‌لیست
• Deadlock Graph: آموزش مستقل، مثال‌ها و چک‌لیست
• xml_deadlock_report Extended Event: آموزش مستقل، مثال‌ها و چک‌لیست
• blocked_process_report Extended Event: آموزش مستقل، مثال‌ها و چک‌لیست
• system_health Extended Events Session: آموزش مستقل، مثال‌ها و چک‌لیست