مقدمه

ساخت Execution Plan نیز حافظه مصرف می‌کند. Queryهای ساده معمولاً Compile کوچکی دارند، اما Statementهای دارای Joinهای فراوان، Expressionهای پیچیده، Partitionهای زیاد یا فضای جست‌وجوی بزرگ می‌توانند در مرحله Optimization حافظه قابل‌توجهی بخواهند. SQL Server برای جلوگیری از اشغال کل حافظه توسط Compileهای هم‌زمان، مسیر لایه‌ای Small، Medium و Big Gateway را به کار می‌گیرد و تعداد مصرف‌کنندگان هر Gate را محدود می‌کند.

sys.dm_exec_query_optimizer_memory_gateways وضعیت جاری این Gateها را برای هر Resource Pool نشان می‌دهد. ستون‌های max_count، active_count و waiter_count مشخص می‌کنند چند Compile مجاز، فعال و منتظر است. threshold و threshold_factor مرز ورود به Gate بعدی را توضیح می‌دهند و is_active نشان می‌دهد Gate در وضعیت فعلی باید اعمال شود یا خیر. این DMV از SQL Server 2016 به بعد در دسترس است.

فشار Compile با فشار Execution Memory یکی نیست. Wait Type مرتبط معمولاً RESOURCE_SEMAPHORE_QUERY_COMPILE است، درحالی‌که Query منتظر Execution Grant اغلب RESOURCE_SEMAPHORE دارد. ممکن است حافظه کلی سرور کافی به نظر برسد اما ظرفیت یک Gateway تمام شده باشد؛ یا Compileهای بزرگ واقعاً حافظه را مصرف کرده باشند. تشخیص صحیح به Snapshot Gateway، Wait Statistics، نرخ Compile و الگوی Queryهای ورودی نیاز دارد.

این مطلب بخشی از راهنمای جامع DMVهای حافظه Query در SQL Server است. برای دیدن ارتباط این نما با Semaphoreهای اجرا، Gateهای Compile و Workerهای Parallel می‌توانید ابتدا یا پس از مطالعه این مقاله به راهنمای مادر بازگردید.

تعریف sys.dm_exec_query_optimizer_memory_gateways

این DMV برای هر Gateway هر Resource Pool یک ردیف برمی‌گرداند. name معمولاً Small Gateway، Medium Gateway یا Big Gateway است. max_count سقف Compileهای هم‌زمان آن Gate، active_count تعداد Compile فعال و waiter_count تعداد Requestهای منتظر عبور از Gate را نشان می‌دهد. اگر active_count به max_count نزدیک باشد و waiter_count رشد کند، Gate به کاندیدای گلوگاه تبدیل می‌شود.

threshold حافظه بعدی بر حسب Byte است که با رسیدن مصرف Optimization به آن، Query باید از Gateway عبور کند. مقدار منفی یک یعنی در وضعیت فعلی عبور از آن Gate لازم نیست. threshold_factor برای Small Gateway بیشینه حافظه Optimizer یک Query پیش از نیاز به Gate را بر حسب Byte بیان می‌کند؛ برای Medium و Big سهمی از حافظه کل را به شکل عامل تقسیم نشان می‌دهد، بنابراین تفسیر آن بین Gateها یکسان نیست.

pool_id اثر Resource Governor را آشکار می‌کند. Workloadهای مختلف می‌توانند Gateهای مستقل و الگوهای انتظار متفاوت داشته باشند. برای یافتن علت، از این نما به عنوان شاخص فشار استفاده کنید و سپس Wait Statistics، نرخ SQL Compilations و Re-Compilations، Query Store و Queryهای پیچیده یا تولیدشده با SQL پویا را بررسی نمایید.

Syntax پایه

SELECT
        pool_id,
        name,
        max_count,
        active_count,
        waiter_count,
        threshold_factor,
        threshold,
        is_active
    FROM sys.dm_exec_query_optimizer_memory_gateways;

مجوزها و سازگاری نسخه

این DMV از SQL Server 2016 و نسخه‌های بعدی، Azure SQL Database، Azure SQL Managed Instance و SQL Database در Fabric پشتیبانی می‌شود. در SQL Server 2022 و بعد VIEW SERVER PERFORMANCE STATE و در نسخه‌های قدیمی‌تر VIEW SERVER STATE لازم است. Azure SQL Database معمولاً VIEW DATABASE STATE را در محدوده مجاز می‌خواهد.

ستون‌های مهم و نحوه تفسیر

تعداد ستون‌ها کم است، اما threshold_factor باید با توجه به نوع Gate تفسیر شود. جدول زیر معنای عملی هر ستون را برای تشخیص Concurrency Compile خلاصه می‌کند.

معماری Gateway و Wait زمان Compile

Optimization یک فرایند جست‌وجو میان Planهای ممکن است. با افزایش تعداد Tableها، Join Orderها، Index Candidateها و Transformationها، فضای جست‌وجو و حافظه لازم رشد می‌کند. Gatewayها هم‌زمانی Compileهای پرمصرف را محدود می‌کنند تا چند درخواست پیچیده منابع کلی موتور را تمام نکنند. Query ممکن است با عبور از آستانه‌ها به Gate سطح بالاتر وارد شود و برای Slot آزاد منتظر بماند.

Small Gateway معمولاً اولین لایه کنترل است و threshold_factor آن مقدار بایتی مصرف یک Query پیش از نیاز به ورود را نشان می‌دهد. Medium و Big Gateway محدودیت سخت‌گیرانه‌تری برای Compileهای بزرگ‌تر دارند؛ threshold_factor آن‌ها به عنوان Divisor حافظه کل استفاده می‌شود. به همین دلیل تبدیل مستقیم همه threshold_factorها به مگابایت و مقایسه آن‌ها اشتباه است.

اگر waiter_count مثبت شود و RESOURCE_SEMAPHORE_QUERY_COMPILE افزایش یابد، دو احتمال اصلی مطرح است: حافظه Compile واقعاً تحت فشار است یا Slotهای یک Gate با Compileهای هم‌زمان پر شده‌اند، حتی اگر حافظه کلی هنوز کاملاً تمام نشده باشد. active_count، max_count، threshold و نرخ Compile به تفکیک زمان برای جدا کردن این سناریوها لازم‌اند.

Recompileهای مکرر، Ad-hoc Queryهای بی‌شمار با Literal متفاوت، Statementهای بسیار پیچیده و موج Deployment می‌توانند فشار را تشدید کنند. پاک کردن Plan Cache ممکن است همه Queryها را مجبور به Compile دوباره کند و اوضاع را بدتر سازد. درمان باید با شناسایی منبع Compile، Parameterization مناسب، کاهش پیچیدگی و حفظ Planهای قابل‌استفاده انجام شود.

قاعده کلیدی: waiter_count را با RESOURCE_SEMAPHORE_QUERY_COMPILE و نسبت active_count به max_count در چند Snapshot تطبیق دهید. threshold_factor را برای Small و Gateهای Medium/Big با یک فرمول واحد تفسیر نکنید.

ده مثال عملی و قابل اجرا

مثال ۱: نمایش همه Gatewayهای Optimizer

Snapshot پایه هر Gate و Pool را نمایش می‌دهد. مرتب‌سازی بر اساس Pool و نام برای مشاهده ساختار مناسب است و نقطه شروع تشخیص Waitهای Compile محسوب می‌شود.

SELECT
        pool_id,
        name,
        max_count,
        active_count,
        waiter_count,
        threshold_factor,
        threshold,
        is_active
    FROM sys.dm_exec_query_optimizer_memory_gateways
    ORDER BY pool_id, name;

نکتهٔ کاربردی: نام و ظرفیت ممکن است با وضعیت و نسخه متفاوت باشد. Collector را به جای فرض تعداد ثابت ردیف‌ها، بر نام و pool_id بنا کنید.

مثال ۲: تبدیل threshold از Byte به Megabyte

threshold بر حسب Byte است و برای خوانایی به مگابایت تبدیل می‌شود. مقدار منفی یک حفظ و با وضعیت Not Required نمایش داده می‌شود تا به عدد منفی مگابایت تعبیر نشود.

SELECT
        pool_id,
        name,
        ThresholdState = CASE
            WHEN threshold = -1 THEN N'Not Required'
            ELSE N'Required'
        END,
        ThresholdMB = CASE
            WHEN threshold = -1 THEN NULL
            ELSE CAST(threshold / 1048576.0 AS decimal(18, 2))
        END,
        threshold_factor
    FROM sys.dm_exec_query_optimizer_memory_gateways;

نکتهٔ کاربردی: threshold_factor Small می‌تواند بایتی باشد، ولی برای Medium و Big نقش Divisor دارد. فقط threshold را با فرمول نمایش‌داده‌شده به مگابایت تبدیل کنید.

مثال ۳: پیدا کردن Gateهای دارای Waiter

فیلتر waiter_count بزرگ‌تر از صفر مستقیم‌ترین راه دیدن صف جاری Compile است. نسبت ظرفیت فعال نیز نمایش داده می‌شود تا مشخص شود Gate به سقف هم‌زمانی نزدیک است یا خیر.

SELECT
        pool_id,
        name,
        max_count,
        active_count,
        waiter_count,
        is_active
    FROM sys.dm_exec_query_optimizer_memory_gateways
    WHERE waiter_count > 0
    ORDER BY waiter_count DESC, pool_id, name;

نکتهٔ کاربردی: یک Snapshot مثبت را با Wait Stats و تکرار زمانی تأیید کنید. Compileهای بسیار کوتاه ممکن است بین نمونه‌ها ظاهر و ناپدید شوند.

مثال ۴: محاسبه درصد اشغال Slotهای Compile

active_count نسبت به max_count به درصد تبدیل می‌شود. NULLIF تقسیم بر صفر را ایمن می‌کند و ردیف‌های با اشغال بالاتر در ابتدای خروجی قرار می‌گیرند.

SELECT
        pool_id,
        name,
        CompileSlotUsedPercent = CAST(100.0 * active_count /
            NULLIF(max_count, 0) AS decimal(6, 2)),
        active_count,
        max_count,
        waiter_count
    FROM sys.dm_exec_query_optimizer_memory_gateways
    ORDER BY CompileSlotUsedPercent DESC, waiter_count DESC;

نکتهٔ کاربردی: اشغال صددرصد بدون Waiter ممکن است کوتاه و قابل‌قبول باشد. تداوم Waiter و رشد Wait Time معیار قوی‌تری برای Incident است.

مثال ۵: فیلتر Gateهای فعال با مدیریت وضعیت Bit

is_active مشخص می‌کند Gate در وضعیت فعلی اعمال می‌شود. Query زیر فقط Gateهای فعال را نشان می‌دهد و Threshold منفی را به NULL نمایشی تبدیل می‌کند.

SELECT
        pool_id,
        name,
        active_count,
        waiter_count,
        EffectiveThresholdBytes = NULLIF(threshold, -1)
    FROM sys.dm_exec_query_optimizer_memory_gateways
    WHERE is_active = 1
    ORDER BY pool_id, name;

نکتهٔ کاربردی: NULL حاصل NULLIF یعنی threshold برابر منفی یک و عبور در آن لحظه لازم نبوده است؛ آن را با Threshold واقعی صفر اشتباه نکنید.

مثال ۶: خلاصه انتظار Compile به تفکیک Pool

این Aggregate تعداد Gateها، Compileهای فعال، ظرفیت و Waiter را برای هر Resource Pool جمع می‌کند. برای داشبورد سطح Workload مفید است، ولی هنگام Alert باید به نام Gate Drill-down شود.

SELECT
        pool_id,
        GatewayCount = COUNT_BIG(*),
        TotalMaxCompileSlots = SUM(max_count),
        TotalActiveCompiles = SUM(active_count),
        TotalCompileWaiters = SUM(waiter_count)
    FROM sys.dm_exec_query_optimizer_memory_gateways
    GROUP BY pool_id
    ORDER BY TotalCompileWaiters DESC, pool_id;

نکتهٔ کاربردی: جمع max_count بین Gateها الزاماً ظرفیت قابل‌جایگزینی واحد نیست؛ هر Gate لایه متفاوتی دارد. این عدد فقط خلاصه داشبورد است.

مثال ۷: نمایش نام Resource Pool در کنار Gateway

pool_id به Catalog View مربوط به Resource Governor متصل می‌شود تا نام Pool برای تیم عملیات خوانا باشد. LEFT JOIN ردیف Gateway را حتی اگر نام متناظر در شرایط خاص قابل بازیابی نباشد حفظ می‌کند.

SELECT
        g.pool_id,
        ResourcePoolName = rp.name,
        GatewayName = g.name,
        g.max_count,
        g.active_count,
        g.waiter_count
    FROM sys.dm_exec_query_optimizer_memory_gateways AS g
    LEFT JOIN sys.resource_governor_resource_pools AS rp
        ON rp.pool_id = g.pool_id
    ORDER BY g.pool_id, g.name;

نکتهٔ کاربردی: نام Pool به تشخیص مالک Workload کمک می‌کند. سیاست‌های Resource Governor را پیش از تغییر ظرفیت یا جابه‌جایی Workload بررسی کنید.

مثال ۸: تطبیق با Wait Statistics زمان Compile

این Query شمار تجمعی Wait مربوط به Compile Memory را از زمان Startup می‌خواند. مقدار باید به صورت Delta و همراه Snapshot Gateway تحلیل شود تا مشخص شود انتظار جدید در همان بازه رخ داده است.

SELECT
        wait_type,
        waiting_tasks_count,
        WaitTimeSeconds = CAST(wait_time_ms / 1000.0 AS decimal(18, 2)),
        SignalWaitSeconds = CAST(signal_wait_time_ms / 1000.0 AS decimal(18, 2))
    FROM sys.dm_os_wait_stats
    WHERE wait_type = N'RESOURCE_SEMAPHORE_QUERY_COMPILE';

نکتهٔ کاربردی: Wait Stats تجمعی است و مقدار مطلق شدت فعلی را نشان نمی‌دهد. Delta بازه Incident و زمان Restart یا Reset دستی Wait Stats را ثبت کنید.

مثال ۹: ذخیره Snapshot Gateway در Temp Table

برای مقایسه چند Query روی یک لحظه ثابت، داده Gateway در Temp Table ذخیره و روی Pool، Waiter و نام ایندکس می‌شود. این روش برای تحلیل Ad-hoc مناسب است و به جدول دائمی دست نمی‌زند.

DROP TABLE IF EXISTS #OptimizerGatewaySnapshot;
    
    SELECT
        CaptureTime = SYSDATETIME(),
        pool_id,
        name,
        max_count,
        active_count,
        waiter_count,
        threshold_factor,
        threshold,
        is_active
    INTO #OptimizerGatewaySnapshot
    FROM sys.dm_exec_query_optimizer_memory_gateways;
    
    CREATE INDEX IX_OptimizerGatewaySnapshot_Pressure
        ON #OptimizerGatewaySnapshot(pool_id, waiter_count DESC, name);
    
    SELECT *
    FROM #OptimizerGatewaySnapshot
    ORDER BY waiter_count DESC, pool_id, name;

نکتهٔ کاربردی: برای Trend Production، جدول دائمی باریک با Retention و Delta Wait Stats بسازید. Temp Table فقط عمر همان Session را دارد.

مثال ۱۰: طبقه‌بندی سلامت Compile Gateway

وضعیت Critical هنگامی ساخته می‌شود که Waiter مثبت و همه Slotها اشغال باشند. Warning برای Waiter مثبت با ظرفیت نسبی و Healthy برای نبود Waiter است. این منطق نمونه باید با Baseline تنظیم شود.

SELECT
        pool_id,
        name,
        max_count,
        active_count,
        waiter_count,
        HealthState = CASE
            WHEN waiter_count > 0 AND active_count >= max_count THEN N'Critical'
            WHEN waiter_count > 0 THEN N'Warning'
            ELSE N'Healthy'
        END
    FROM sys.dm_exec_query_optimizer_memory_gateways
    ORDER BY CASE
        WHEN waiter_count > 0 AND active_count >= max_count THEN 0
        WHEN waiter_count > 0 THEN 1 ELSE 2 END,
        pool_id, name;

نکتهٔ کاربردی: برای جلوگیری از هشدار کاذب، شرط تداوم در چند Sample و Delta مثبت RESOURCE_SEMAPHORE_QUERY_COMPILE را نیز اضافه کنید.

خطاهای رایج

فشار Compile کمتر از فشار Execution Memory شناخته شده است و همین موضوع باعث می‌شود Waitها یا ستون‌های Gateway اشتباه تفسیر شوند. خطاهای زیر در داشبورد و Incident Response رایج‌اند.

• یکسان دانستن RESOURCE_SEMAPHORE_QUERY_COMPILE با RESOURCE_SEMAPHORE اجرای Query.
• تبدیل threshold_factor همه Gateها به مگابایت، با وجود تفاوت معنای Small و Medium یا Big.
• فرض اینکه active_count بالا بدون waiter_count الزاماً بحران است.
• پاک کردن کل Plan Cache برای رفع Compile Wait و ایجاد موج Compile جدید.
• نادیده گرفتن pool_id و سیاست Resource Governor.
• تحلیل مقدار تجمعی Wait Stats بدون Delta و زمان Startup.
• تکیه بر Snapshot منفرد در Workload دارای Compileهای بسیار کوتاه.
• بهینه‌سازی تنظیمات سرور بدون شناسایی Queryهای پیچیده، Recompile و Ad-hoc SQL.

ملاحظات Performance

خود DMV کوچک است و خواندن آن معمولاً سبک، اما Polling بسیار سریع همراه Joinهای سنگین به Query Store یا Plan Cache می‌تواند هزینه ایجاد کند. Snapshot عددی Gate را با فاصله مناسب بگیرید و فقط هنگام Waiter مثبت، تشخیص Queryهای در حال Compile و نرخ Compile را عمیق‌تر کنید.

Wait Statistics تجمعی است؛ ذخیره Delta بازه‌ای ارزش بیشتری از تکرار کل مقدار دارد. Counterهای SQL Compilations/sec و Re-Compilations/sec را نیز با نرخ Batch Requests مقایسه کنید. نرخ Compile بالا به تنهایی مشکل نیست، اما نسبت غیرعادی همراه Gateway Waiter نشانه قوی است.

درمان‌های سطح Plan Cache باید محتاطانه باشند. DBCC FREEPROCCACHE یا Restart می‌تواند همه Planها را حذف و فشار Compile ناگهانی ایجاد کند. تغییر Parameterization، OPTION(RECOMPILE)، Keep Plan یا طراحی SQL پویا باید بر Queryهای هدف و با تست بار انجام شود.

• Snapshot Gateway سبک و زمان‌دار است.
• Wait Stats به شکل Delta تحلیل می‌شود.
• نرخ Compile با Batch Requests مقایسه شده است.
• Query Store یا Plan Cache فقط برای بازه و Query هدف بررسی می‌شود.
• پاک‌سازی سراسری Cache بخشی از راه‌حل عادی نیست.
• Collector پس از Upgrade SQL Server تست می‌شود.

بهترین روش‌ها و کاربرد سازمانی

برای Runbook، ابتدا Wait Type را تفکیک کنید. اگر RESOURCE_SEMAPHORE_QUERY_COMPILE رشد دارد، Snapshot Gateway و Pool را ثبت کنید. سپس نرخ Compile، Recompile و نوع Queryهای ورودی را بررسی نمایید. این ترتیب مانع تغییر اشتباه Max Server Memory برای مسئله‌ای می‌شود که ریشه آن پیچیدگی یا Churn در Plan Cache است.

Queryهای دارای ده‌ها Join، Viewهای تو‌در‌تو، Predicateهای پیچیده و Ad-hoc SQL تولیدشده با Literalهای متفاوت کاندیدای بررسی‌اند. Parameterization امن، کاهش فضای جست‌وجوی Optimizer، ساده‌سازی Query و جلوگیری از Recompile غیرضروری باید با صحت نتیجه و Plan واقعی آزمایش شوند.

در سامانه سازمانی، Baseline Compile روز عادی و Deployment را جدا کنید. موج Compile پس از Deploy ممکن است کوتاه باشد، اما تداوم آن SLA را تهدید می‌کند. داشبورد باید Gateway Waiter، Delta Wait، Compilations/sec و مالک Workload را کنار هم نشان دهد و برای Escalation به تیم توسعه مسیر مشخص داشته باشد.

1. RESOURCE_SEMAPHORE_QUERY_COMPILE را از Wait اجرای Query جدا کنید.
2. Gateway دارای Waiter و Resource Pool آن را ثبت کنید.
3. نسبت active_count به max_count و Threshold را در چند Snapshot بسنجید.
4. نرخ Compile، Recompile و Batch Requests را مقایسه کنید.
5. Queryهای پیچیده یا Ad-hoc مولد Churn را شناسایی کنید.
6. اصلاح محدود را با تست بار و Baseline Compile اعتبارسنجی کنید.

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

پرسش ۱: sys.dm_exec_query_optimizer_memory_gateways چه چیزی را گزارش می‌کند؟

وضعیت Gateهای کنترل‌کننده هم‌زمانی Query Optimization را برای هر Resource Pool نشان می‌دهد. ظرفیت مجاز، Compile فعال، Waiter، Threshold و فعال بودن Gate قابل مشاهده است. این نما برای جدا کردن فشار حافظه Compile از Memory Grant اجرای Query کاربرد دارد.

پرسش ۲: Small، Medium و Big Gateway چه نقشی دارند؟

این لایه‌ها Compileها را بر اساس رشد مصرف حافظه به محدودیت‌های هم‌زمانی متفاوت هدایت می‌کنند. Compile بزرگ‌تر ممکن است از Gateهای سخت‌گیرانه‌تری عبور کند. هدف جلوگیری از مصرف کنترل‌نشده حافظه توسط چند Optimization پیچیده و حفظ پایداری کلی موتور است.

پرسش ۳: فشار Compile چه اثری بر سرویس تجاری دارد؟

Session پیش از گرفتن Plan و شروع اجرا منتظر می‌ماند، بنابراین Latency API یا گزارش افزایش می‌یابد حتی اگر خود Execution سریع باشد. داشبورد Gateway و Wait به تیم عملیات کمک می‌کند این تأخیر را از قفل، I/O یا Memory Grant اجرا تفکیک کند و SLA را دقیق‌تر مدیریت نماید.

پرسش ۴: آیا خرید RAM مشکل Compile Gateway را حل می‌کند؟

ممکن است ظرفیت را افزایش دهد، اما Slotهای Gate، نرخ Compile، Recompile و پیچیدگی Query همچنان مهم‌اند. اگر Plan Cache Churn یا SQL پویا علت باشد، RAM بیشتر درمان ریشه‌ای نیست. بازبینی تخصصی Workload و Planها پیش از توسعه سخت‌افزار تصمیم کم‌ریسک‌تری ایجاد می‌کند.

پرسش ۵: تفاوت threshold و threshold_factor چیست؟

threshold آستانه بعدی حافظه بر حسب Byte است و منفی یک یعنی عبور لازم نیست. threshold_factor در Small Gateway مقدار بایتی مصرف یک Query پیش از Gate را نشان می‌دهد، اما برای Medium و Big به عنوان عامل تقسیم حافظه کل استفاده می‌شود. تفسیر یکسان این ستون اشتباه است.

پرسش ۶: چگونه مانیتورینگ Compile Gateway پیاده‌سازی شود؟

Snapshot Gateway را با CaptureTime ذخیره و Delta Wait Type مربوط را کنار آن ثبت کنید. نرخ Compilations و Re-Compilations را با Batch Requests مقایسه کنید و Drill-down به Pool و Queryهای پیچیده بدهید. برای پروژه Production، Retention و آستانه‌های Baseline باید تست شوند.

پرسش ۷: چرا waiter_count صفر است اما Wait تاریخی زیاد دیده می‌شود؟

DMV وضعیت همین لحظه را نشان می‌دهد، ولی sys.dm_os_wait_stats تجمعی از زمان Startup یا Reset است. ممکن است فشار قبلاً رخ داده و اکنون تمام شده باشد. برای ارتباط معتبر باید Delta Wait و Snapshotهای زمان‌دار یک بازه یکسان را مقایسه کنید.

پرسش ۸: آیا خواندن این DMV روی Performance اثر زیادی دارد؟

خود نما چند ردیف کوچک دارد و معمولاً سبک است. سربار زمانی ایجاد می‌شود که با Polling بسیار سریع، Plan Cache، Query Store یا XMLهای بزرگ ترکیب شود. راه درست Snapshot عددی سبک و Drill-down فقط هنگام Waiter یا Delta غیرعادی است.

پرسش ۹: بهترین روش رفع Compile Memory Wait چیست؟

ابتدا منبع Compile زیاد یا پیچیده را شناسایی کنید. Parameterization، کاهش Recompile، ساده‌سازی Query و جلوگیری از Ad-hoc Planهای بی‌شمار گزینه‌های معمول‌اند. Plan Cache را سراسری پاک نکنید مگر با دلیل و برنامه؛ هر اصلاح باید با نرخ Compile، Wait و Latency قبل و بعد سنجیده شود.

پرسش ۱۰: این DMV در چه نسخه‌هایی و با چه مجوزی موجود است؟

از SQL Server 2016 و نسخه‌های بعدی پشتیبانی می‌شود. SQL Server 2022 و بعد معمولاً VIEW SERVER PERFORMANCE STATE و نسخه‌های قدیمی‌تر VIEW SERVER STATE می‌خواهند. Azure SQL Database محدوده و مجوز پایگاه داده خود را دارد.

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

سؤال مصاحبه ۱: Wait Type مرتبط با Gateway چیست؟

RESOURCE_SEMAPHORE_QUERY_COMPILE. این Wait به حافظه و ظرفیت زمان Compile مربوط است و با RESOURCE_SEMAPHORE اجرای Query تفاوت دارد.

سؤال مصاحبه ۲: max_count و active_count چگونه تفسیر می‌شوند؟

max_count سقف Compileهای هم‌زمان Gate و active_count مصرف جاری Slotهاست. نزدیک بودن آن‌ها همراه waiter_count مثبت گلوگاه محتمل را نشان می‌دهد.

سؤال مصاحبه ۳: threshold برابر منفی یک چه معنایی دارد؟

یعنی Query در وضعیت فعلی ملزم به عبور از آن Gateway نیست. بهتر است در گزارش به NULL یا برچسب Not Required تبدیل شود، نه مگابایت منفی.

سؤال مصاحبه ۴: چرا threshold_factor را یکسان تبدیل نمی‌کنیم؟

برای Small معنای بایتی دارد، اما برای Medium و Big عامل تقسیم سهم حافظه کل است. فرمول و واحد تفسیری یکسان نیست.

سؤال مصاحبه ۵: چه چیزی می‌تواند Compile Churn بسازد؟

Ad-hoc SQL با Literalهای متفاوت، Recompile مکرر، Invalidations، Plan Cache کوچک یا پاک‌شده و Deployment می‌توانند نرخ Compile را بالا ببرند.

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

• Wait Type Compile از Execution تفکیک شده است.
• Gateway و pool_id دارای Waiter ثبت شده‌اند.
• active_count، max_count و waiter_count در چند Sample مقایسه شده‌اند.
• threshold منفی یک و معنای متفاوت threshold_factor درست مدیریت شده‌اند.
• Delta Wait Stats و زمان Startup موجود است.
• نرخ Compile، Recompile و Batch Requests بررسی شده است.
• Queryهای پیچیده، Ad-hoc و OPTION(RECOMPILE) بازبینی شده‌اند.
• هیچ Cache Flush سراسری بدون برنامه و Baseline انجام نشده است.

جمع‌بندی

sys.dm_exec_query_optimizer_memory_gateways پنجره‌ای مستقیم به کنترل هم‌زمانی حافظه در مرحله Compile است. با مشاهده Gate، Pool، ظرفیت، مصرف Slot و Waiter می‌توان فهمید تأخیر پیش از Execution رخ می‌دهد یا خیر. ارتباط آن با RESOURCE_SEMAPHORE_QUERY_COMPILE محور تشخیص معتبر است.

راه‌حل پایدار معمولاً از شناسایی Compile Churn و Queryهای پیچیده می‌گذرد، نه پاک کردن سراسری Cache. Snapshot سبک، Delta Wait، نرخ Compile و آزمایش اصلاح روی Workload نماینده کمک می‌کند Latency کاهش یابد بدون آنکه موج Compile تازه‌ای ساخته شود.

برای تکمیل تصویر عیب‌یابی، به مقاله مادر DMVهای Query Memory و نقشه تشخیص یکپارچه بازگردید.