ده مثال عملی و قابل اجرا
مثال ۱: نمایش وضعیت خام همه Semaphoreها
این Query ساده همه ستونهای کلیدی را برای Snapshot اولیه انتخاب میکند و نتیجه را بر اساس Pool و نوع Semaphore مرتب میسازد. برای Job دائمی بهتر است دقیقاً همین ستونهای موردنیاز صریح انتخاب شوند.
SELECT
pool_id,
resource_semaphore_id,
target_memory_kb,
total_memory_kb,
available_memory_kb,
granted_memory_kb,
used_memory_kb,
grantee_count,
waiter_count
FROM sys.dm_exec_query_resource_semaphores
ORDER BY pool_id, resource_semaphore_id;
| pool_id | resource_semaphore_id | available_memory_kb | grantee_count | waiter_count |
|---|
| 1 | 0 | 524288 | 18 | 3 |
| 1 | 1 | 98304 | 6 | 0 |
نکتهٔ کاربردی: شناسه صفر Semaphore معمولی و یک Small Query است. Snapshot را با زمان ثبت کنید، چون مقدارها با شروع و پایان Queryها تغییر میکنند.
مثال ۲: تبدیل ظرفیت حافظه به مگابایت
نمایش مگابایت برای داشبورد و گزارش انسانی خواناتر است. تبدیل با تقسیم اعشاری انجام میشود تا بخش کسری حذف نشود و decimal خروجی پایدار ارائه کند.
SELECT
pool_id,
resource_semaphore_id,
TargetMB = CAST(target_memory_kb / 1024.0 AS decimal(18, 2)),
TotalMB = CAST(total_memory_kb / 1024.0 AS decimal(18, 2)),
AvailableMB = CAST(available_memory_kb / 1024.0 AS decimal(18, 2)),
GrantedMB = CAST(granted_memory_kb / 1024.0 AS decimal(18, 2)),
UsedMB = CAST(used_memory_kb / 1024.0 AS decimal(18, 2))
FROM sys.dm_exec_query_resource_semaphores;
| pool_id | resource_semaphore_id | TargetMB | AvailableMB | GrantedMB | UsedMB |
|---|
| 1 | 0 | 8192.00 | 512.00 | 7680.00 | 6240.50 |
| 1 | 1 | 128.00 | 96.00 | 32.00 | 18.75 |
نکتهٔ کاربردی: granted حافظه رزروشده و used مصرف فیزیکی است. فاصله این دو در سطح Semaphore سرنخ Over-Grant جمعی است، ولی Queryهای منفرد را در DMV Memory Grants پیدا کنید.
مثال ۳: نامگذاری Semaphore معمولی و Small Query
CASE شناسه فنی را به برچسب خوانا تبدیل میکند. این Query همچنین شروط Small Query را در توضیح خروجی منعکس میکند، اما طبقهبندی واقعی را موتور SQL Server انجام میدهد.
SELECT
pool_id,
resource_semaphore_id,
SemaphoreType = CASE resource_semaphore_id
WHEN 0 THEN N'Regular'
WHEN 1 THEN N'Small Query'
ELSE N'Other'
END,
grantee_count,
waiter_count
FROM sys.dm_exec_query_resource_semaphores
ORDER BY pool_id, resource_semaphore_id;
| pool_id | resource_semaphore_id | SemaphoreType | grantee_count | waiter_count |
|---|
| 1 | 0 | Regular | 18 | 3 |
| 1 | 1 | Small Query | 6 | 0 |
نکتهٔ کاربردی: Small Query باید هم کمتر از پنج مگابایت Grant درخواست کند و هم Cost کمتر از سه داشته باشد. صرف کوچک بودن یکی از این دو معیار کافی نیست.
مثال ۴: فیلتر Semaphoreهای دارای صف
برای Alert اولیه فقط ردیفهایی انتخاب میشوند که waiter_count آنها مثبت است. حافظه آزاد و اعطاشده نشان میدهد صف در چه Pool و چه نوع Semaphore رخ داده است.
SELECT
pool_id,
resource_semaphore_id,
waiter_count,
grantee_count,
available_memory_kb,
granted_memory_kb,
used_memory_kb
FROM sys.dm_exec_query_resource_semaphores
WHERE waiter_count > 0
ORDER BY waiter_count DESC, available_memory_kb;
| pool_id | resource_semaphore_id | waiter_count | available_memory_kb | granted_memory_kb |
|---|
| 2 | 0 | 7 | 16384 | 8388608 |
| 1 | 0 | 3 | 524288 | 7864320 |
نکتهٔ کاربردی: waiter_count مثبت را با چند Snapshot و Grantهای منتظر همان Pool تأیید کنید. صفر بودن در یک لحظه میتواند صف کوتاهمدت بین دو نمونه را پنهان کند.
مثال ۵: محاسبه درصد ظرفیت آزاد
available_memory_kb نسبت به total_memory_kb به درصد تبدیل میشود. NULLIF از خطای تقسیم بر صفر جلوگیری میکند و درصد کم در کنار Waiter مثبت ردیفهای پرریسک را مشخص میسازد.
SELECT
pool_id,
resource_semaphore_id,
FreePercent = CAST(100.0 * available_memory_kb /
NULLIF(total_memory_kb, 0) AS decimal(6, 2)),
waiter_count,
grantee_count
FROM sys.dm_exec_query_resource_semaphores
ORDER BY FreePercent, waiter_count DESC;
| pool_id | resource_semaphore_id | FreePercent | waiter_count | grantee_count |
|---|
| 2 | 0 | 0.19 | 7 | 24 |
| 1 | 0 | 6.25 | 3 | 18 |
| 1 | 1 | 75.00 | 0 | 6 |
نکتهٔ کاربردی: درصد پایین بدون Waiter ممکن است گذرا یا قابلقبول باشد. تداوم، مدت انتظار Requestها و روند مصرف برای Alert قابلاعتمادترند.
مثال ۶: مدیریت NULL و خواندن شمارندههای تجمعی
timeout_error_count و forced_grant_count برای Small Semaphore میتوانند NULL باشند. COALESCE برای نمایش داشبورد مقدار صفر میگذارد، اما ستون IsCounterAvailable مشخص میکند صفر واقعی با نبود شمارنده اشتباه نشود.
SELECT
pool_id,
resource_semaphore_id,
IsCounterAvailable = CASE
WHEN timeout_error_count IS NULL THEN 0 ELSE 1
END,
TimeoutErrors = COALESCE(timeout_error_count, 0),
ForcedGrants = COALESCE(forced_grant_count, 0)
FROM sys.dm_exec_query_resource_semaphores;
| pool_id | resource_semaphore_id | IsCounterAvailable | TimeoutErrors | ForcedGrants |
|---|
| 1 | 0 | 1 | 12 | 31 |
| 1 | 1 | 0 | 0 | 0 |
نکتهٔ کاربردی: برای Alert از Delta شمارندهها استفاده کنید. صفر نمایشی Small Semaphore به معنی ثبت صفر رخداد نیست؛ ممکن است ستون اساساً قابلاعمال نباشد.
مثال ۷: خلاصه فشار به تفکیک Resource Pool
در محیط Resource Governor، مجموع ظرفیت و صف برای هر Pool محاسبه میشود. این سطح گزارش به تیم کمک میکند Workload آسیبدیده را از سایر Poolها جدا کند.
SELECT
pool_id,
SemaphoreCount = COUNT_BIG(*),
TotalMemoryMB = CAST(SUM(total_memory_kb) / 1024.0 AS decimal(18, 2)),
AvailableMemoryMB = CAST(SUM(available_memory_kb) / 1024.0 AS decimal(18, 2)),
TotalGrantees = SUM(grantee_count),
TotalWaiters = SUM(waiter_count)
FROM sys.dm_exec_query_resource_semaphores
GROUP BY pool_id
ORDER BY TotalWaiters DESC, AvailableMemoryMB;
| pool_id | SemaphoreCount | TotalMemoryMB | AvailableMemoryMB | TotalWaiters |
|---|
| 2 | 2 | 8320.00 | 48.00 | 7 |
| 1 | 2 | 8320.00 | 608.00 | 3 |
نکتهٔ کاربردی: Aggregate جزئیات Regular و Small را پنهان میکند؛ هنگام Alert دوباره به سطح Semaphore برگردید و سیاست Pool را با Workload Group بررسی کنید.
مثال ۸: اتصال صحیح به Requestهای Memory Grant
این مثال Semaphore را با Grantهای همان Pool و همان شناسه متصل میکند. استفاده از هر دو کلید مانع تکثیر اشتباه ردیفها در سرور دارای چند Resource Pool میشود.
SELECT
rs.pool_id,
rs.resource_semaphore_id,
rs.waiter_count,
mg.session_id,
mg.wait_time_ms,
mg.requested_memory_kb,
mg.grant_time
FROM sys.dm_exec_query_resource_semaphores AS rs
LEFT JOIN sys.dm_exec_query_memory_grants AS mg
ON mg.pool_id = rs.pool_id
AND mg.resource_semaphore_id = rs.resource_semaphore_id
WHERE rs.waiter_count > 0
ORDER BY rs.pool_id, rs.resource_semaphore_id, mg.wait_time_ms DESC;
| pool_id | resource_semaphore_id | waiter_count | session_id | wait_time_ms |
|---|
| 2 | 0 | 7 | 81 | 22140 |
| 2 | 0 | 7 | 86 | 7900 |
نکتهٔ کاربردی: اگر Join فقط روی resource_semaphore_id باشد، Sessionهای Poolهای دیگر نیز به ردیف وصل میشوند. این خطا در داشبوردها بسیار رایج و گمراهکننده است.
مثال ۹: محاسبه Delta شمارندهها با Snapshot نمونه
این نمونه قابل اجرا دو Snapshot فرضی را با VALUES میسازد تا روش محاسبه Delta روشن شود. در سامانه واقعی OldValue و NewValue از جدول تاریخچه و با توجه به زمان Startup خوانده میشوند.
WITH Samples AS
(
SELECT *
FROM (VALUES
(1, 0, CAST(10 AS bigint), CAST(28 AS bigint)),
(1, 0, CAST(12 AS bigint), CAST(31 AS bigint))
) AS v(pool_id, resource_semaphore_id, timeout_count, forced_count)
), Numbered AS
(
SELECT *,
rn = ROW_NUMBER() OVER
(PARTITION BY pool_id, resource_semaphore_id ORDER BY timeout_count)
FROM Samples
)
SELECT
CurrentTimeoutDelta = MAX(timeout_count) - MIN(timeout_count),
CurrentForcedGrantDelta = MAX(forced_count) - MIN(forced_count)
FROM Numbered
WHERE rn IN (1, 2);
| CurrentTimeoutDelta | CurrentForcedGrantDelta |
|---|
| 2 | 3 |
نکتهٔ کاربردی: در Collector واقعی ترتیب را با CaptureTime تعیین کنید و اگر Startup تغییر کرده است Delta قبلی را ادامه ندهید؛ Restart شمارندهها را بازنشانی میکند.
مثال ۱۰: طبقهبندی سلامت Semaphore برای Alert
CASE یک وضعیت ساده Operational میسازد. بحرانی زمانی است که Waiter وجود دارد و ظرفیت آزاد کمتر از یک درصد کل است؛ حالت هشدار برای هر Waiter دیگر و حالت عادی برای نبود صف استفاده میشود.
SELECT
pool_id,
resource_semaphore_id,
waiter_count,
available_memory_kb,
total_memory_kb,
HealthState = CASE
WHEN waiter_count > 0
AND 100.0 * available_memory_kb /
NULLIF(total_memory_kb, 0) < 1 THEN N'Critical'
WHEN waiter_count > 0 THEN N'Warning'
ELSE N'Healthy'
END
FROM sys.dm_exec_query_resource_semaphores
ORDER BY CASE
WHEN waiter_count > 0 AND 100.0 * available_memory_kb /
NULLIF(total_memory_kb, 0) < 1 THEN 0
WHEN waiter_count > 0 THEN 1 ELSE 2 END;
| pool_id | resource_semaphore_id | waiter_count | HealthState |
|---|
| 2 | 0 | 7 | Critical |
| 1 | 0 | 3 | Warning |
| 1 | 1 | 0 | Healthy |
نکتهٔ کاربردی: آستانه یک درصد فقط نمونه است. مقدار Production باید از Baseline، مدت صف و SLA به دست آید و در چند Snapshot متوالی فعال شود.
سؤالات متداول
پرسش ۱: sys.dm_exec_query_resource_semaphores چه چیزی نشان میدهد؟
وضعیت جاری حافظه اجرای Query را در سطح Resource Semaphore و Pool نمایش میدهد. ظرفیت هدف، حافظه آزاد، Grant و مصرف، تعداد Queryهای دارای Grant و تعداد منتظرها در دسترس است. برای یافتن Sessionهای دقیق باید آن را با sys.dm_exec_query_memory_grants ترکیب کنید.
پرسش ۲: تفاوت Semaphore عادی و Small Query چیست؟
Semaphore عادی بیشتر Grantهای اجرا را مدیریت میکند. Small Query Semaphore برای Queryهایی است که هم کمتر از پنج مگابایت حافظه میخواهند و هم Cost تخمینی کمتر از سه دارند. این مسیر ظرفیت محدودی برای جلوگیری از متوقف شدن Queryهای کوچک در پشت مصرفکنندگان بزرگ فراهم میکند.
پرسش ۳: این DMV چگونه از SLA یک سامانه تجاری محافظت میکند؟
رشد waiter_count میتواند تأخیر گزارشها و APIهای وابسته به SQL Server را پیش از اشباع CPU نشان دهد. با Baseline و Alert چندشرطی، تیم عملیات زودتر Pool و Workload آسیبدیده را تشخیص میدهد. Threshold باید بر اساس ساعت اوج و اهمیت فرایند کسبوکار تنظیم شود.
پرسش ۴: آیا افزایش حافظه سرور بهترین راه رفع Semaphore Wait است؟
نه همیشه. Query با Estimate اشتباه، Grant بیشازحد، Concurrency گزارشها یا محدودیت Resource Governor میتواند علت اصلی باشد. تحلیل تخصصی Plan، Statistics و Pool معمولاً مشخص میکند اصلاح نرمافزاری یا زمانبندی از خرید سختافزار مؤثرتر است. افزایش RAM باید پس از سنجش کمبود ظرفیت واقعی انجام شود.
پرسش ۵: تفاوت waiter_count و timeout_error_count چیست؟
waiter_count تعداد Requestهای منتظر در همین لحظه است. timeout_error_count شمار تجمعی Timeoutها از زمان Startup است و برای Small Semaphore میتواند NULL باشد. برای روندسنجی Timeout باید Delta دو Snapshot با Startup یکسان محاسبه شود؛ مقدار مطلق شدت فعلی را نشان نمیدهد.
پرسش ۶: چگونه داشبورد Resource Semaphore بسازیم؟
CaptureTime، pool_id، semaphore_id، ظرفیت، Grant، مصرف، Grantee، Waiter و شمارندهها را در جدول باریک ذخیره کنید. Alert را روی تداوم صف و درصد آزاد بسازید و Drill-down به Memory Grants فراهم کنید. طراحی Retention، نسخهپذیری و امنیت باید در پروژه مانیتورینگ آزمایش شود.
پرسش ۷: چرا Join بر اساس semaphore_id نتیجه تکراری میدهد؟
resource_semaphore_id در کل Instance یکتا نیست و در Resource Poolهای مختلف تکرار میشود. شرط اتصال درست شامل pool_id و resource_semaphore_id است. حذف pool_id باعث اتصال Grantهای یک Pool به Semaphoreهای Pool دیگر و چندبرابر شدن شمار Sessionها میشود.
پرسش ۸: اجرای Aggregate روی این DMV چه ریسکی دارد؟
ORDER BY و Aggregate خود میتوانند حافظه و CPU مصرف کنند، بهویژه اگر با DMVهای دیگر و Plan XML ترکیب شوند. در بحران، Snapshot سبک بگیرید و جزئیات را برای Pool هدف واکشی کنید. نرخ Polling و زمان اجرای Collector نیز باید مانیتور شود.
پرسش ۹: بهترین روش Alert برای waiter_count چیست؟
یک Waiter لحظهای را بحران تلقی نکنید. چند نمونه متوالی، رشد مدت انتظار در Memory Grants، available percent پایین و Wait Type را ترکیب کنید. آستانه و بازه باید از Baseline همان Pool و SLA استخراج شود و پس از تغییر Workload بازتنظیم گردد.
پرسش ۱۰: مجوز و سازگاری نسخه این DMV چیست؟
در SQL Server 2022 و بعد VIEW SERVER PERFORMANCE STATE و در نسخههای قدیمیتر معمولاً VIEW SERVER STATE لازم است. Azure SQL Database قواعد مجوز متفاوتی دارد. ساختار DMV ابزار عیبیابی است؛ Collector باید نسخه و ستونها را پس از Upgrade کنترل کند.