آموزش کامل sys.dm_exec_background_job_queue_stats در SQL Server

sys.dm_exec_background_job_queue_stats یکی از اشیای مدیریت پویای مرتبط با اجرای Query در اکوسیستم Microsoft SQL Server است. ارائه آمار مرتبط با Background Job Queue در محیط‌های پشتیبانی‌شده و کمک به مشاهده رفتار تجمعی صف‌های داخلی. این مقاله از مبانی شروع می‌کند و سپس Syntax، روش خواندن خروجی، سناریوهای عیب‌یابی، مثال‌های قابل اجرا، خطاهای رایج و ملاحظات کارایی را بررسی می‌کند.

برای بازگشت به نمای کلی این مجموعه، راهنمای جامع DMVهای اجرای Query در SQL Server را ببینید. هنگام اجرای Queryهای مدیریتی در Production، سطح دسترسی و تفاوت نسخه‌ها را در نظر بگیرید.

DMVها وضعیت زنده یا آمار وابسته به حافظه و Cache را نشان می‌دهند. بنابراین خروجی امروز لزوماً تاریخچه دائمی نیست و برای Trend Analysis باید Snapshotهای انتخابی را ذخیره کنید.

تعریف و کاربرد

آمار صف کارهای پس‌زمینه محور اصلی این DMV است. ارائه آمار مرتبط با Background Job Queue در محیط‌های پشتیبانی‌شده و کمک به مشاهده رفتار تجمعی صف‌های داخلی. در عیب‌یابی واقعی، این داده زمانی ارزشمند می‌شود که سؤال مشخصی داشته باشید؛ برای مثال «چه چیزی اکنون کند است؟»، «کدام Query بیشترین هزینه را داشته؟» یا «آیا یک الگوی خاص در Sessionها و Requestها تکرار می‌شود؟».

دامنه و Permission موردنیاز می‌تواند بر اساس نسخه و محصول متفاوت باشد. در نسخه‌های جدید SQL Server برخی سناریوهای مشاهده وضعیت سرور به مجوزهای Performance State وابسته‌اند. در Azure SQL و محصولات توزیع‌شده نیز Scope ممکن است Database-level یا Platform-specific باشد؛ بنابراین Query را متناسب با محیط مقصد اعتبارسنجی کنید.

Syntax پایه

SELECT *
FROM sys.dm_exec_background_job_queue_stats;

پارامترها و شکل خروجی

این شیء به‌صورت DMV خوانده می‌شود و معمولاً پارامتر ورودی ندارد. هر ردیف نماینده یک واحد منطقی متناسب با موضوع DMV است. ستون‌ها و Scope دقیق میان نسخه‌ها و سرویس‌های ابری ممکن است تفاوت داشته باشند.

نوع خروجی

خروجی به‌شکل Result Set جدولی است. برای مانیتورینگ پایدار بهتر است به‌جای SELECT * فقط ستون‌های موردنیاز را انتخاب کنید؛ این کار قرارداد خروجی را روشن‌تر می‌کند و در صورت اضافه‌شدن ستون‌های جدید در نسخه‌های بعدی، Pipeline شما کمتر آسیب می‌بیند.

روش تحلیل مرحله‌ای

1. ابتدا مشکل را دقیق تعریف کنید و زمان وقوع را ثبت کنید.
2. یک Query سبک و محدود برای Snapshot اولیه اجرا کنید.
3. ردیف‌های مشکوک را با Session، Request، Query Text یا Plan مرتبط کنید.
4. نتیجه را با Baseline و Snapshotهای دیگر مقایسه کنید.
5. پیش از هر تغییر، علت را با شواهد مستقل تأیید کنید.

مثال‌های عملی

مثال شماره 1: نمونه‌برداری سریع از DMV

در این سناریو از sys.dm_exec_background_job_queue_stats برای «نمونه‌برداری سریع از DMV» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SELECT TOP (20) *
FROM sys.dm_exec_background_job_queue_stats;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 2: شمارش ردیف‌های قابل مشاهده

در این سناریو از sys.dm_exec_background_job_queue_stats برای «شمارش ردیف‌های قابل مشاهده» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SELECT COUNT_BIG(*) AS visible_row_count
FROM sys.dm_exec_background_job_queue_stats;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 3: افزودن شماره ردیف برای بررسی

در این سناریو از sys.dm_exec_background_job_queue_stats برای «افزودن شماره ردیف برای بررسی» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SELECT TOP (20)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS sample_row_number,
       d.*
FROM sys.dm_exec_background_job_queue_stats AS d;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 4: اجرای ایمن فقط در صورت وجود Object

در این سناریو از sys.dm_exec_background_job_queue_stats برای «اجرای ایمن فقط در صورت وجود Object» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

IF OBJECT_ID(N'sys.dm_exec_background_job_queue_stats') IS NOT NULL
BEGIN
    SELECT TOP (20) *
    FROM sys.dm_exec_background_job_queue_stats;
END
ELSE
BEGIN
    SELECT N'sys.dm_exec_background_job_queue_stats در این نسخه یا محیط در دسترس نیست.' AS message;
END;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 5: شناخت ستون‌های DMV از Metadata

در این سناریو از sys.dm_exec_background_job_queue_stats برای «شناخت ستون‌های DMV از Metadata» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SELECT c.column_id, c.name AS column_name,
       TYPE_NAME(c.user_type_id) AS data_type,
       c.max_length, c.is_nullable
FROM sys.all_columns AS c
WHERE c.object_id = OBJECT_ID(N'sys.dm_exec_background_job_queue_stats')
ORDER BY c.column_id;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 6: گرفتن Snapshot موقت

در این سناریو از sys.dm_exec_background_job_queue_stats برای «گرفتن Snapshot موقت» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

DROP TABLE IF EXISTS #dmv_snapshot;
SELECT TOP (100) *
INTO #dmv_snapshot
FROM sys.dm_exec_background_job_queue_stats;

SELECT COUNT_BIG(*) AS captured_rows
FROM #dmv_snapshot;

DROP TABLE IF EXISTS #dmv_snapshot;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 7: ثبت زمان نمونه‌برداری

در این سناریو از sys.dm_exec_background_job_queue_stats برای «ثبت زمان نمونه‌برداری» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SELECT SYSDATETIME() AS captured_at,
       DB_NAME() AS current_database,
       COUNT_BIG(*) AS row_count
FROM sys.dm_exec_background_job_queue_stats;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 8: مقایسه دو Snapshot شمارشی

در این سناریو از sys.dm_exec_background_job_queue_stats برای «مقایسه دو Snapshot شمارشی» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

DECLARE @Before bigint, @After bigint;

SELECT @Before = COUNT_BIG(*) FROM sys.dm_exec_background_job_queue_stats;
WAITFOR DELAY '00:00:01';
SELECT @After = COUNT_BIG(*) FROM sys.dm_exec_background_job_queue_stats;

SELECT @Before AS before_count,
       @After AS after_count,
       @After - @Before AS delta;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 9: مدیریت خطای Permission یا ناسازگاری

در این سناریو از sys.dm_exec_background_job_queue_stats برای «مدیریت خطای Permission یا ناسازگاری» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

BEGIN TRY
    SELECT TOP (20) *
    FROM sys.dm_exec_background_job_queue_stats;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number,
           ERROR_MESSAGE() AS error_message;
END CATCH;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

مثال شماره 10: اندازه‌گیری سربار Query پایشی

در این سناریو از sys.dm_exec_background_job_queue_stats برای «اندازه‌گیری سربار Query پایشی» استفاده می‌کنیم. هدف این است که Query قابل اجرا، محدود و قابل توسعه باشد. پیش از اجرا روی Production، Permission و وجود ستون‌های استفاده‌شده را در نسخه خود کنترل کنید.

SET STATISTICS TIME ON;
SELECT TOP (50) *
FROM sys.dm_exec_background_job_queue_stats;
SET STATISTICS TIME OFF;

در کاربرد واقعی، خروجی را همراه زمان Capture ذخیره کنید و از یک مقدار منفرد نتیجه‌گیری قطعی نکنید. اگر ردیف مشکوکی پیدا شد، مرحله بعد Correlation با Query Text، Plan، Session یا Waitهای مرتبط است.

خطاهای رایج

• استفاده از یک Snapshot منفرد و نتیجه‌گیری قطعی درباره علت کندی.
• اجرای SELECT * با فرکانس بسیار بالا و تبدیل ابزار مانیتورینگ به منبع سربار.
• نادیده‌گرفتن Restart، Recompile و Plan Cache Eviction در تفسیر آمار تجمعی.
• فرض یکسان‌بودن ستون‌ها و Permissionها در همه نسخه‌های SQL Server و سرویس‌های Azure/Fabric/Synapse.
• تغییر Index یا Configuration بدون تأیید علت با Plan و شاخص‌های مکمل.

ملاحظات Performance

Queryهای DMV معمولاً برای مشاهده وضعیت طراحی شده‌اند، اما هزینه صفر ندارند. Joinهای گسترده، CROSS APPLY روی هزاران Plan، تبدیل XML، Polling در بازه‌های بسیار کوتاه و ذخیره تمام ستون‌ها می‌تواند CPU، Memory یا I/O سامانه مانیتورینگ را افزایش دهد. TOP، Filter، Sampling و Drill-down را ترجیح دهید.

برای Dashboardهای دائمی، دو سطح جمع‌آوری مفید است: سطح اول Snapshot سبک و پرتکرار، سطح دوم Capture جزئیات فقط هنگام عبور شاخص از Threshold. این معماری حجم داده را کم می‌کند و در عین حال شواهد لازم برای Root Cause Analysis را حفظ می‌کند.

Best Practices

• Query مانیتورینگ را در Source Control نگه دارید و تغییرات آن را Review کنید.
• Timestamp، Server/Database Context و نسخه محصول را همراه Snapshot ثبت کنید.
• تنها ستون‌های لازم را جمع‌آوری کنید و Retention مشخص داشته باشید.
• برای داده‌های تجمعی Delta بین دو Snapshot را محاسبه کنید، نه فقط مقدار کل را.
• برای اقدام اصلاحی، DMV را با Plan، Query Store، Waitها و Metrics سیستم‌عامل ترکیب کنید.

کاربرد در پروژه‌های واقعی

در پروژه‌های سازمانی، sys.dm_exec_background_job_queue_stats می‌تواند بخشی از Runbook عیب‌یابی باشد. سناریو معمول این است که Alert از افزایش Latency یا مصرف CPU شروع می‌شود؛ سپس DBA با DMVهای Execution وضعیت را نمونه‌برداری می‌کند، ردیف‌های مشکوک را به Query و Session مرتبط می‌سازد و بر اساس شواهد، راهکارهایی مثل اصلاح Query، Index، Parameterization یا Capacity را ارزیابی می‌کند.

برای تحلیل حرفه‌ای sys.dm_exec_background_job_queue_stats باید میان «مشاهده داده» و «اثبات علت» تفاوت قائل شد. DMV سرنخ می‌دهد، اما علت نهایی معمولاً از ترکیب چند منبع مانند Query Text، Execution Plan، Wait Statistics، Session Context، Configuration و الگوی بار کاری به دست می‌آید. بنابراین خروجی را به‌عنوان Evidence در یک زنجیره تشخیصی استفاده کنید، نه به‌عنوان حکم نهایی.

در محیط Production بهتر است Queryهای مانیتورینگ نسخه‌بندی شوند، ستون‌های خروجی مشخص و محدود باشند و زمان Capture در هر Snapshot ثبت شود. همچنین باید بدانید Restart سرویس، Recompile، حذف Plan از Cache یا تغییر معماری می‌تواند باعث از بین رفتن بخشی از داده‌های تجمعی شود. برای تحلیل روند، تاریخچه را خارج از DMV نگهداری کنید.

یک الگوی عملی مناسب این است که ابتدا Query سبک برای تشخیص وضعیت اجرا شود، سپس فقط برای ردیف‌های مشکوک جزئیات بیشتری مانند متن SQL یا Plan خوانده شود. این رویکرد Drill-down هم سربار را کم می‌کند و هم خروجی را قابل‌مدیریت نگه می‌دارد. در سامانه‌های پرتراکنش، Sampling هوشمند از Polling بسیار تهاجمی بهتر است.

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

پرسش ۱: sys.dm_exec_background_job_queue_stats دقیقاً چه مسئله‌ای را حل می‌کند؟

sys.dm_exec_background_job_queue_stats برای مشاهده آمار صف کارهای پس‌زمینه به کار می‌رود. ارزش اصلی آن در این است که بدون ساخت جدول مانیتورینگ جداگانه، بخشی از وضعیت داخلی موتور را قابل مشاهده می‌کند؛ البته تفسیر خروجی باید همراه با Context، زمان نمونه‌برداری و نسخه محصول باشد.

پرسش ۲: از کجا تحلیل sys.dm_exec_background_job_queue_stats را شروع کنیم؟

ابتدا تعداد ردیف‌ها و ستون‌های مهم را بشناسید، سپس یک Snapshot سبک بگیرید و تنها شاخص‌هایی را بررسی کنید که مستقیماً با مسئله شما مرتبط هستند. جمع‌آوری همه ستون‌ها در حلقه‌های بسیار کوتاه می‌تواند خود سامانه مانیتورینگ را به منبع سربار تبدیل کند.

پرسش ۳: آیا sys.dm_exec_background_job_queue_stats برای پایش سازمانی مناسب است؟

بله، اما بهتر است DMV را منبع داده خام بدانید و برای نگهداری تاریخچه، Job یا سامانه مانیتورینگ جداگانه طراحی کنید. در پروژه‌های سازمانی معمولاً Snapshotهای انتخابی با Timestamp ذخیره می‌شوند تا روندها، جهش‌ها و الگوهای تکرارشونده قابل مقایسه باشند.

پرسش ۴: چه زمانی برای تحلیل تجاری Performance از sys.dm_exec_background_job_queue_stats استفاده کنیم؟

هنگامی که کندی سرویس، افزایش زمان پاسخ یا رشد مصرف منابع بر SLA و تجربه کاربر اثر گذاشته است، داده این DMV می‌تواند بخشی از شواهد فنی باشد. برای تصمیم تجاری باید داده فنی با شاخص‌هایی مانند زمان پاسخ API، حجم تراکنش و ساعات اوج ترکیب شود.

پرسش ۵: تفاوت sys.dm_exec_background_job_queue_stats با یک ابزار Monitoring کامل چیست؟

DMV یک نمای نزدیک به موتور و عمدتاً لحظه‌ای یا وابسته به Cache ارائه می‌کند، در حالی که ابزار Monitoring تاریخچه، هشدار، Visualization و Correlation میان چند منبع را فراهم می‌کند. بهترین معماری معمولاً استفاده مکمل از هر دو است.

پرسش ۶: آیا می‌توان بر اساس خروجی sys.dm_exec_background_job_queue_stats فوراً تنظیمات سرور را تغییر داد؟

بهتر است خیر. ابتدا فرضیه را با چند Snapshot، Query Plan، Waitها و شاخص‌های مرتبط تأیید کنید. تغییر Configuration، Index یا Query صرفاً بر اساس یک ردیف DMV ممکن است مسئله دیگری ایجاد کند؛ در پروژه حساس، بازبینی DBA یا مشاور SQL Server توصیه می‌شود.

پرسش ۷: خطای رایج هنگام استفاده از sys.dm_exec_background_job_queue_stats چیست؟

یکی از خطاهای رایج، برداشت قطعی از یک Snapshot منفرد است. خطای دیگر اجرای SELECT ستاره در دفعات بسیار زیاد و ذخیره حجم زیادی از داده بدون هدف تحلیلی است. ستون‌ها را محدود کنید و Capture Interval را متناسب با مسئله انتخاب کنید.

پرسش ۸: استفاده از sys.dm_exec_background_job_queue_stats چه ملاحظات Performance دارد؟

خود DMVها برای مشاهده وضعیت طراحی شده‌اند، اما Query سنگین روی آن‌ها، Joinهای بزرگ، استخراج Plan برای هزاران ردیف یا Polling بسیار سریع می‌تواند هزینه ایجاد کند. TOP، Filter، Sampling و ذخیره دوره‌ای هدفمند معمولاً رویکرد امن‌تری است.

پرسش ۹: Best Practice برای استفاده از sys.dm_exec_background_job_queue_stats چیست؟

قبل از جمع‌آوری، سؤال عملیاتی خود را مشخص کنید؛ سپس حداقل ستون‌های لازم را بگیرید، Timestamp اضافه کنید، نسخه و Scope را ثبت کنید و نتایج را با Baseline مقایسه کنید. برای تحلیل ریشه‌ای، DMVهای مرتبط را کنار هم قرار دهید نه اینکه تنها به یک نما تکیه کنید.

پرسش ۱۰: آیا sys.dm_exec_background_job_queue_stats در همه نسخه‌های SQL Server یکسان است؟

خیر. سطح دسترسی، Scope، ستون‌ها و حتی در دسترس بودن برخی DMVهای تخصصی میان SQL Server، Azure SQL، Managed Instance، Synapse و Fabric می‌تواند متفاوت باشد. همیشه مستندات نسخه مقصد را بررسی و Query را ابتدا در محیط Test اجرا کنید.

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

1. sys.dm_exec_background_job_queue_stats چه نوع اطلاعاتی ارائه می‌کند و Scope آن چیست؟
2. برای جلوگیری از نتیجه‌گیری اشتباه از sys.dm_exec_background_job_queue_stats چه Contextهایی را باید ثبت کرد؟
3. چگونه داده sys.dm_exec_background_job_queue_stats را با DMVهای دیگر Correlate می‌کنید؟
4. چرا یک Snapshot منفرد برای Root Cause Analysis کافی نیست؟
5. برای کم‌کردن سربار مانیتورینگ sys.dm_exec_background_job_queue_stats چه راهکارهایی دارید؟

چک‌لیست نهایی

• وجود DMV و ستون‌های مورد استفاده در نسخه مقصد بررسی شد.
• Permission لازم با حداقل سطح دسترسی تأمین شد.
• Query نمونه‌برداری محدود، قابل تکرار و Timestampدار است.
• نتیجه با Baseline و حداقل یک منبع داده مستقل مقایسه شد.
• قبل از تغییر Production، فرضیه در Test یا با شواهد کافی اعتبارسنجی شد.

جمع‌بندی

sys.dm_exec_background_job_queue_stats ابزار ارزشمندی برای مشاهده آمار صف کارهای پس‌زمینه است، اما بیشترین ارزش آن زمانی به دست می‌آید که در یک فرآیند تشخیصی منظم استفاده شود. Query سبک، Snapshot زمان‌دار، Correlation با سایر DMVها و پرهیز از نتیجه‌گیری عجولانه چهار اصل اصلی استفاده حرفه‌ای هستند.

برای دیدن ارتباط این DMV با سایر اجزای Execution، به مقاله مادر DMVهای اجرای Query در SQL Server برگردید.