مقدمه

بسیاری از کندی‌های ناگهانی SQL Server زمانی رخ می‌دهند که CPU هنوز اشباع نشده و دیسک نیز ظاهراً مشکل جدی ندارد، اما Queryها برای دریافت حافظهٔ اجرای خود در صف مانده‌اند. عملگرهایی مانند Sort، Hash Join، Hash Aggregate و برخی عملیات موازی برای آغاز یا ادامهٔ کار به Workspace Memory نیاز دارند. Optimizer بر پایه تخمین تعداد ردیف‌ها و اندازه داده، مقدار حداقل، درخواستی و ایده‌آل حافظه را برآورد می‌کند؛ موتور سپس با توجه به ظرفیت لحظه‌ای Resource Semaphore تصمیم می‌گیرد چه مقدار Grant بدهد یا درخواست را منتظر نگه دارد.

این مقاله چهار Dynamic Management View مکمل را به یک نقشهٔ عیب‌یابی تبدیل می‌کند. sys.dm_exec_query_memory_grants جزئیات Requestها را می‌دهد، sys.dm_exec_query_resource_semaphores تصویر کلان مخزن Grant را نشان می‌دهد، sys.dm_exec_query_optimizer_memory_gateways فشار حافظه در زمان Compile را آشکار می‌کند و sys.dm_exec_query_parallel_workers ظرفیت Workerهای موازی را در هر NUMA Node نمایش می‌دهد. خواندن جداگانه هر نما مفید است، ولی ارزش واقعی زمانی ایجاد می‌شود که Snapshotهای هم‌زمان آن‌ها کنار Wait Statistics، Execution Plan و Baseline بار بررسی شوند.

هدف این راهنما ارائه Queryهای قابل اجرا، خروجی نمونه و روش تفسیر است تا DBA یا توسعه‌دهنده بتواند میان چهار سناریو فرق بگذارد: Grant بزرگ اما کم‌مصرف، صف واقعی حافظه اجرا، گلوگاه Compile و کمبود Worker موازی. اطلاعات DMVها عمدتاً زنده و گذرا هستند؛ پس ثبت زمان نمونه‌برداری، تکرار کنترل‌شده و پرهیز از Queryهای تشخیصی سنگین اهمیت دارد. در SQL Server 2022 و نسخه‌های بعدی برای مشاهده بسیاری از این نماها مجوز VIEW SERVER PERFORMANCE STATE و در نسخه‌های قدیمی‌تر معمولاً VIEW SERVER STATE لازم است.

دسترسی سریع به مقاله‌های تخصصی

برای مطالعه جزئیات ستون‌ها، ده مثال مستقل، خطاهای رایج و نکات Performance هر DMV از لینک‌های زیر استفاده کنید. همه مسیرها داخلی‌اند و مستقیماً به مقاله تخصصی همان نما می‌رسند.

• آموزش کامل sys.dm_exec_query_memory_grants و تحلیل Grantهای فعال و منتظر
• آموزش کامل sys.dm_exec_query_resource_semaphores و ظرفیت حافظهٔ اجرا
• آموزش کامل sys.dm_exec_query_optimizer_memory_gateways و فشار Compile
• آموزش کامل sys.dm_exec_query_parallel_workers و ظرفیت Workerهای موازی

مدل ذهنی حافظهٔ Query

حافظهٔ Query با Max Server Memory یا اندازه Buffer Pool مترادف نیست. بخشی از حافظهٔ در اختیار SQL Server برای Execution Workspace مدیریت می‌شود. هنگام Compile، Optimizer با تکیه بر Cardinality Estimate، نوع داده، عرض ردیف، تعداد عملگرهای هم‌زمان و Degree of Parallelism یک Grant را پیش‌بینی می‌کند. required_memory_kb حداقل لازم برای شروع، requested_memory_kb درخواست عملی، ideal_memory_kb مقدار مطلوب برای نگه‌داشتن عملیات در حافظه و granted_memory_kb تصمیم واقعی موتور است.

اگر حافظه کافی نباشد، Request وارد صف Semaphore می‌شود و تا دریافت Grant یا Timeout صبر می‌کند. پس از دریافت Grant، used_memory_kb مصرف لحظه‌ای و max_used_memory_kb بیشینه مصرف مشاهده‌شده را نشان می‌دهند. Grant بسیار کمتر از نیاز ممکن است Spill به tempdb ایجاد کند؛ Grant بسیار بیشتر از مصرف نیز Concurrency را کاهش می‌دهد، زیرا حافظه‌ای که رزرو شده برای Requestهای دیگر قابل استفاده نیست. قضاوت باید بر اساس چند اجرا و Plan واقعی باشد، نه صرفاً یک نسبت.

مسیر Compile حافظه جداگانه‌ای دارد. SQL Server برای جلوگیری از مصرف کنترل‌نشده حافظه توسط Compileهای بزرگ، Gatewayهای لایه‌ای ایجاد می‌کند. درخواست ممکن است از Small، Medium یا Big Gateway عبور کند و هنگام نبود ظرفیت با RESOURCE_SEMAPHORE_QUERY_COMPILE منتظر بماند. از سوی دیگر Query موازی برای Taskهای خود Worker رزرو می‌کند؛ کمبود Worker می‌تواند DOP مؤثر را محدود کند یا تأخیر ایجاد کند، حتی اگر Grant حافظه به‌تنهایی بحرانی به نظر نرسد.

اصل تشخیصی مهم: حافظهٔ درخواستی، حافظهٔ اعطاشده، حافظهٔ واقعاً مصرف‌شده، ظرفیت Semaphore، فشار Compile و ظرفیت Worker شش عدد متفاوت‌اند. هیچ‌کدام را به‌تنهایی معادل «کمبود RAM» ندانید.

معرفی چهار DMV مجموعه

sys.dm_exec_query_memory_grants

این نما برای همه Queryهایی که Grant خواسته‌اند و در انتظارند یا Grant گرفته‌اند ردیف برمی‌گرداند. Queryهایی که اساساً Memory Grant لازم ندارند در آن دیده نمی‌شوند. ستون‌های Session، زمان درخواست، حجم لازم و ایده‌آل، مصرف واقعی، DOP، Handle متن و Plan و اطلاعات Pool امکان می‌دهد عامل صف یا Over-Grant را تا سطح Request دنبال کنید. آموزش جامع sys.dm_exec_query_memory_grants با ده سناریوی اجرایی

sys.dm_exec_query_resource_semaphores

این نما وضعیت جاری Resource Semaphoreهای مسئول Query Execution Memory را در سطح Pool خلاصه می‌کند. معمولاً یک ردیف برای Semaphore عادی و یک ردیف برای Queryهای کوچک وجود دارد؛ در حضور Resource Governor تعداد ردیف‌ها بیشتر می‌شود. مقایسه حافظه آزاد، اعطاشده، تعداد Grantee و Waiter برای اثبات فشار صف ضروری است. آموزش جامع sys.dm_exec_query_resource_semaphores و تحلیل ظرفیت

sys.dm_exec_query_optimizer_memory_gateways

این DMV وضعیت Gateهای کنترل‌کننده هم‌زمانی Query Optimization را گزارش می‌کند. تعداد فعال، سقف Compile هم‌زمان، تعداد منتظر، Threshold و فعال بودن Gate کمک می‌کند فشار Compile از فشار Execution جدا شود. این نما از SQL Server 2016 به بعد در دسترس است. آموزش جامع sys.dm_exec_query_optimizer_memory_gateways و Compile Wait

sys.dm_exec_query_parallel_workers

این DMV ظرفیت Workerهای مورد استفاده Queryهای موازی را به تفکیک Node نشان می‌دهد. Worker آزاد، رزروشده و مصرف‌شده همراه تعداد Schedulerها مشخص می‌کند آیا یک NUMA Node زیر فشار موازی‌سازی است. مقدار Worker آزاد در بار بسیار سنگین حتی می‌تواند منفی شود. آموزش جامع sys.dm_exec_query_parallel_workers و تحلیل NUMA

جدول مقایسه‌ای DMVها

روش تشخیص مرحله‌به‌مرحله

در Incident واقعی ابتدا زمان شروع علائم، Queryهای آسیب‌دیده و الگوی بار را ثبت کنید. سپس با Query خلاصه تعداد Grantهای منتظر و مدت انتظار را بسنجید. اگر صف وجود دارد، Semaphore متناظر و Pool را بررسی کنید. پس از آن Requestهای بزرگ را همراه SQL Text و Plan انتخاب کنید و تخمین ردیف، Sort، Hash و Spill را تحلیل کنید. به‌موازات آن Gatewayهای Compile و Workerهای Parallel را کنترل کنید تا علت جانبی از قلم نیفتد.

1. یک Snapshot سبک از شمار Requestهای منتظر، مجموع حافظه درخواستی و ظرفیت آزاد بگیرید و زمان دقیق را ثبت کنید.
2. برای ردیف‌های منتظر، session_id، wait_time_ms، حجم درخواست، DOP، SQL Text و Plan را به‌صورت هدفمند واکشی کنید.
3. ترکیب pool_id و resource_semaphore_id را برای اتصال به Semaphore استفاده کنید و از Join ناقص خودداری کنید.
4. Waitهای RESOURCE_SEMAPHORE و RESOURCE_SEMAPHORE_QUERY_COMPILE را از هم جدا کنید؛ درمان آن‌ها الزاماً یکسان نیست.
5. ظرفیت Worker هر NUMA Node را بررسی و ناهمگونی Nodeها را ثبت کنید؛ مجموع سرور ممکن است فشار موضعی را پنهان کند.
6. پس از اصلاح Statistics، Index، Query، MAXDOP یا Resource Governor، همان Snapshotها را با Baseline پیش از تغییر مقایسه کنید.

شش مثال کاربردی یکپارچه

مثال ۱: گرفتن نمای سریع از صف حافظهٔ اجرا

نخستین قدم در رخداد کندی این است که بدانیم چند درخواست حافظهٔ لازم خود را گرفته‌اند و چند درخواست هنوز در صف هستند. این Query تعداد درخواست‌های در انتظار و تعداد درخواست‌های دارای Grant را بدون نمایش متن کامل Plan خلاصه می‌کند؛ بنابراین برای نمونه‌برداری اولیه سبک و مناسب است.

SELECT
        WaitingRequests = SUM(CASE WHEN grant_time IS NULL THEN 1 ELSE 0 END),
        GrantedRequests = SUM(CASE WHEN grant_time IS NOT NULL THEN 1 ELSE 0 END),
        RequestedMemoryMB = CAST(SUM(requested_memory_kb) / 1024.0 AS decimal(18, 2)),
        GrantedMemoryMB = CAST(SUM(granted_memory_kb) / 1024.0 AS decimal(18, 2))
    FROM sys.dm_exec_query_memory_grants;

نکتهٔ کاربردی: وجود حتی یک ردیف منتظر را باید در کنار مدت انتظار، حجم Grantهای فعال و Wait Type از نوع RESOURCE_SEMAPHORE تحلیل کرد؛ یک Snapshot به‌تنهایی اثبات‌کنندهٔ بحران دائمی نیست.

مثال ۲: مشاهدهٔ ظرفیت Semaphoreهای اجرای Query

این Query وضعیت Semaphore معمولی و Semaphore درخواست‌های کوچک را به مگابایت نمایش می‌دهد. اگر waiter_count افزایش یابد و available_memory_kb نزدیک صفر بماند، فشار حافظهٔ اجرای Query محتمل است.

SELECT
        pool_id,
        resource_semaphore_id,
        AvailableMemoryMB = CAST(available_memory_kb / 1024.0 AS decimal(18, 2)),
        GrantedMemoryMB = CAST(granted_memory_kb / 1024.0 AS decimal(18, 2)),
        grantee_count,
        waiter_count
    FROM sys.dm_exec_query_resource_semaphores
    ORDER BY pool_id, resource_semaphore_id;

نکتهٔ کاربردی: شناسهٔ صفر معمولاً Semaphore عادی و شناسهٔ یک Semaphore درخواست کوچک است. در محیط دارای Resource Governor، ترکیب pool_id و resource_semaphore_id را مبنای تطبیق قرار دهید.

مثال ۳: یافتن Queryهای منتظر همراه متن دستور

برای تشخیص عامل صف، ردیف‌های بدون grant_time با متن Batch ترکیب می‌شوند. استفاده از OUTER APPLY باعث می‌شود حتی در صورت در دسترس نبودن متن، اطلاعات Grant حذف نشود.

SELECT
        mg.session_id,
        mg.wait_time_ms,
        RequestedMemoryMB = CAST(mg.requested_memory_kb / 1024.0 AS decimal(18, 2)),
        mg.dop,
        QueryText = st.text
    FROM sys.dm_exec_query_memory_grants AS mg
    OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
    WHERE mg.grant_time IS NULL
    ORDER BY mg.wait_time_ms DESC;

نکتهٔ کاربردی: متن Query را همراه Plan تخمینی، آمار Cardinality و عملگرهای Sort و Hash بررسی کنید. قطع Session فقط واکنش اضطراری است و علت تخمین اشتباه یا طراحی نامناسب را درمان نمی‌کند.

مثال ۴: بررسی فشار حافظهٔ زمان Compile

همه مشکلات حافظه در مرحله اجرا رخ نمی‌دهند. این DMV نشان می‌دهد Gateهای Small، Medium و Big برای Compile چند مصرف‌کننده فعال و چند منتظر دارند و آیا Gate در حال حاضر فعال است یا خیر.

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

نکتهٔ کاربردی: Waitهای این ناحیه معمولاً با RESOURCE_SEMAPHORE_QUERY_COMPILE دیده می‌شوند. تعداد Compile زیاد، Queryهای بسیار پیچیده و Recompileهای پی‌درپی از محورهای بررسی هستند.

مثال ۵: سنجش ظرفیت Workerهای Parallel در هر NUMA Node

Grant حافظه و Parallelism به هم وابسته‌اند، زیرا Query موازی علاوه بر حافظه Worker نیز رزرو می‌کند. این Query درصد تقریبی Worker آزاد را در هر Node محاسبه می‌کند و Nodeهای تحت فشار را در ابتدای نتیجه قرار می‌دهد.

SELECT
        node_id,
        scheduler_count,
        max_worker_count,
        reserved_worker_count,
        used_worker_count,
        free_worker_count,
        FreeWorkerPercent = CAST(100.0 * free_worker_count /
            NULLIF(max_worker_count, 0) AS decimal(6, 2))
    FROM sys.dm_exec_query_parallel_workers
    ORDER BY FreeWorkerPercent, node_id;

نکتهٔ کاربردی: عدد منفی برای free_worker_count در بار بسیار سنگین ممکن است دیده شود. توازن NUMA، تنظیم MAXDOP و Queryهای موازی هم‌زمان را با هم بررسی کنید.

مثال ۶: ساخت داشبورد متنی یک‌ردیفی برای Incident

برای ثبت سریع وضعیت در Ticket یا جدول مانیتورینگ، می‌توان چهار نما را در یک خروجی خلاصه کرد. این Query از Subqueryهای مستقل استفاده می‌کند تا شاخص‌های صف اجرا، صف Compile و ظرفیت Worker را هم‌زمان نشان دهد.

SELECT
        CaptureTime = SYSDATETIME(),
        WaitingMemoryGrants =
            (SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL),
        SemaphoreWaiters =
            (SELECT COALESCE(SUM(waiter_count), 0) FROM sys.dm_exec_query_resource_semaphores),
        CompileGatewayWaiters =
            (SELECT COALESCE(SUM(waiter_count), 0) FROM sys.dm_exec_query_optimizer_memory_gateways),
        FreeParallelWorkers =
            (SELECT COALESCE(SUM(free_worker_count), 0) FROM sys.dm_exec_query_parallel_workers);

نکتهٔ کاربردی: برای روندسنجی، خروجی را با Timestamp در یک مخزن مانیتورینگ ذخیره کنید. اجرای بی‌وقفه و با فاصله بسیار کوتاه خود DMVها می‌تواند سربار ایجاد کند؛ فاصله نمونه‌برداری باید متناسب با Incident باشد.

تفسیر نشانه‌ها و انتخاب اقدام اصلاحی

اگر Grantهای منتظر دارید، Semaphore حافظه آزاد کمی نشان می‌دهد و چند Query فعال بخش بزرگی از حافظه را گرفته‌اند، ابتدا Queryهای غالب را بررسی کنید. فاصله زیاد میان Grant و مصرف واقعی در چند اجرای پایدار می‌تواند Over-Grant باشد؛ به‌روزرسانی Statistics، اصلاح Predicate، کاهش عرض ردیف، حذف Sort غیرضروری و استفاده درست از ایندکس‌ها از اقدامات معمول‌اند. Memory Grant Feedback در نسخه‌ها و حالت‌های واجد شرایط می‌تواند تخمین را در اجراهای بعدی تعدیل کند، اما جای طراحی درست Query را نمی‌گیرد.

اگر صف Execution ندارید ولی Compile Gateway منتظر دارد و Wait مربوط به Query Compile رشد می‌کند، علت را در Compileهای پیچیده، Plan Cache ناکارآمد، Recompileهای زیاد، SQL پویا با Literalهای متعدد و تعداد Joinهای افراطی جست‌وجو کنید. Parameterization، کاهش پیچیدگی، حفظ Statistics مناسب و جلوگیری از Recompile غیرضروری می‌تواند کمک کند. پاک کردن کل Plan Cache در محیط Production راه‌حل عمومی نیست و ممکن است موج تازه‌ای از Compile ایجاد کند.

اگر Worker آزاد در یک Node بسیار پایین است، Queryهای موازی هم‌زمان، DOP انتخاب‌شده و توزیع Schedulerها را بررسی کنید. پایین آوردن سراسری MAXDOP بدون تست ممکن است Latency تک‌Query را افزایش دهد. در محیط سازمانی بهتر است Workload نماینده بازپخش شود، Query Store و Waitها بررسی شوند و تغییر با معیارهای SLA سنجیده شود. هدف ایجاد تعادل میان Throughput، Latency، حافظه و Worker است.

خطاهای رایج

• تعبیر هر مقدار بزرگ در requested_memory_kb به‌عنوان Memory Leak، بدون بررسی Plan و مصرف واقعی.
• گرفتن Plan XML برای همه Requestها در هر ثانیه و ایجاد سربار تشخیصی روی سرور تحت فشار.
• اتصال Semaphoreها فقط بر پایه resource_semaphore_id و نادیده گرفتن pool_id.
• نادیده گرفتن Queryهای کوچک، Compile Gatewayها یا Workerها و تمرکز انحصاری بر یک DMV.
• تغییر هم‌زمان چند تنظیم مانند Max Server Memory، MAXDOP و Resource Governor بدون Baseline و امکان بازگشت.
• اجرای DBCC FREEPROCCACHE یا Restart سرویس برای پاک کردن علامت، پیش از ثبت شواهد علت ریشه‌ای.
• فرض ثابت بودن داده DMVها؛ این اطلاعات با شروع و پایان Requestها سریع تغییر می‌کند.
• استفاده از Threshold یکسان برای همه Instanceها، بدون توجه به RAM، NUMA، SLA و الگوی بار.

نکات Performance و بهترین روش‌ها

Collector را از Query خلاصه آغاز کنید و فقط هنگام مشاهده علامت، Text و Plan ردیف‌های هدف را بخوانید. ستون‌های لازم را صریح انتخاب کنید و از SELECT * در Job دائمی بپرهیزید. Aggregate و ORDER BY روی DMV ممکن است خود به حافظه نیاز داشته باشد؛ بنابراین نرخ نمونه‌برداری، تعداد ستون و دوره نگهداری را کنترل کنید.

به جای یک Threshold ثابت، Baseline روز عادی، پایان ماه، ETL و اوج ترافیک را جدا کنید. Alert را روی تداوم صف، رشد مدت انتظار و هم‌زمانی چند نشانه بنا کنید. هر Snapshot باید Instance، Database مرتبط در صورت امکان، Timestamp، Session و وضعیت Resource Pool را داشته باشد تا تحلیل پس از رخداد معتبر بماند.

برای اصلاح، کوچک‌ترین تغییر قابل سنجش را انتخاب کنید. ابتدا Statistics و Plan، سپس طراحی Query و Index، بعد تنظیمات سطح Workload و در نهایت ظرفیت سخت‌افزار را بررسی کنید. هر تغییر باید با معیارهایی مانند Duration، CPU، Logical Reads، Spill، Grant، Throughput و تعداد Waiter قبل و بعد مقایسه شود.

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

پرسش ۱: DMVهای Query Memory دقیقاً چه مشکلی را حل می‌کنند؟

این نماها وضعیت زندهٔ حافظهٔ لازم برای Sort، Hash، اجرای موازی و Compile را آشکار می‌کنند. با کنار هم گذاشتن آن‌ها می‌توان تشخیص داد تأخیر از صف Grant اجرا، محدودیت Semaphore، Gate زمان Compile یا کمبود Worker ناشی شده است. برای عیب‌یابی حرفه‌ای بهتر است چند Snapshot زمان‌دار تهیه شود تا رفتار گذرا با الگوی پایدار اشتباه نشود.

پرسش ۲: چرا بعضی Queryها در sys.dm_exec_query_memory_grants دیده نمی‌شوند؟

فقط درخواست‌هایی که به Execution Memory Grant نیاز دارند در این نما ظاهر می‌شوند. یک Query ساده که Sort، Hash یا عملگر حافظه‌بر ندارد ممکن است هیچ ردیفی ایجاد نکند. نبودن ردیف بنابراین به معنی اجرا نشدن Query نیست؛ بلکه نشان می‌دهد آن درخواست در لحظه نمونه‌برداری Grant قابل‌گزارش نداشته است.

پرسش ۳: برای یک سامانه تجاری چه شاخص‌هایی باید مانیتور شوند؟

حداقل تعداد و مدت Grantهای منتظر، حافظهٔ درخواست‌شده و مصرف‌شده، Waitهای RESOURCE_SEMAPHORE، شمار منتظرهای Compile Gateway و درصد Worker آزاد را ثبت کنید. آستانه‌ها را از Baseline همان سامانه استخراج کنید، زیرا یک مقدار ثابت برای همه سرورها معتبر نیست. در پروژه‌های حساس، طراحی داشبورد و Alert باید با بار واقعی و SLA کسب‌وکار آزمایش شود.

پرسش ۴: آیا افزایش RAM همیشه صف Memory Grant را برطرف می‌کند؟

خیر. RAM بیشتر ممکن است ظرفیت را افزایش دهد، اما تخمین Cardinality نادرست، Grant بیش‌ازحد، Concurrency بالا، Plan نامناسب یا تنظیم نادرست Resource Governor همچنان صف می‌سازد. پیش از خرید سخت‌افزار باید Planها، آمارها، ایندکس‌ها و الگوی هم‌زمانی تحلیل شوند؛ مشاورهٔ Performance در این مرحله معمولاً از هزینهٔ تغییر عجولانه جلوگیری می‌کند.

پرسش ۵: تفاوت حافظهٔ Compile و حافظهٔ اجرای Query چیست؟

حافظهٔ Compile برای ساخت و بهینه‌سازی Execution Plan مصرف می‌شود و Gateهای Optimizer هم‌زمانی مصرف‌کنندگان بزرگ آن را محدود می‌کنند. Execution Grant پس از Compile برای عملگرهایی مانند Sort و Hash رزرو می‌شود. Wait Type و DMV مرتبط این دو مسیر متفاوت است، هرچند فشار کلی حافظه و پیچیدگی Query می‌تواند هر دو را هم‌زمان تشدید کند.

پرسش ۶: برای پیاده‌سازی مانیتورینگ این DMVها از کجا شروع کنیم؟

یک Collector سبک با فاصله زمانی منطقی بسازید، Snapshotها را با زمان، نام Instance و شاخص‌های بار ذخیره کنید و ابتدا فقط Alertهای مشاهده‌ای ایجاد کنید. سپس Thresholdها را با Baseline تنظیم کنید. اگر تیم تجربه کافی ندارد، اجرای آزمایشی، آموزش DBA و بازبینی Queryهای Collector می‌تواند مانع سربار و هشدارهای کاذب شود.

پرسش ۷: رایج‌ترین خطای تحلیل Query Memory چیست؟

رایج‌ترین خطا نتیجه‌گیری از یک Snapshot منفرد است. ممکن است یک عملیات ETL کوتاه در همان لحظه حافظه زیادی گرفته باشد، اما بحران پایدار نباشد. خطای دیگر Join کردن Semaphoreها فقط با resource_semaphore_id است؛ در حضور چند Resource Pool باید pool_id نیز وارد شرط اتصال شود تا ردیف‌های اشتباه تولید نشوند.

پرسش ۸: خواندن این DMVها چه اثری بر Performance دارد؟

انتخاب چند ستون و فیلتر مناسب معمولاً سبک است، اما ORDER BY، Aggregateهای سنگین، دریافت Plan XML برای همه Sessionها و Polling بسیار سریع می‌تواند فشار بیشتری ایجاد کند. در Incident حافظه، ابزار تشخیصی نباید خود به مصرف‌کننده بزرگ حافظه تبدیل شود. ابتدا Snapshot خلاصه بگیرید و جزئیات را فقط برای مظنون‌ها واکشی کنید.

پرسش ۹: بهترین روش برای تنظیم Alert چیست؟

Alert را بر پایه تداوم و ترکیب نشانه‌ها بسازید، نه یک عدد لحظه‌ای. برای نمونه، waiter_count مثبت در چند دوره متوالی همراه با wait_time_ms رو به رشد و available_memory_kb پایین معنای بیشتری دارد. Threshold باید بر اساس دوره‌های عادی و اوج بار همان Instance بازبینی و مستند شود.

پرسش ۱۰: این مجموعه با کدام نسخه‌های SQL Server سازگار است؟

دو DMV مربوط به Optimizer Memory Gateways و Parallel Workers از SQL Server 2016 به بعد در دسترس‌اند. ستون‌های Worker در Memory Grants نیز از SQL Server 2016 اضافه شده‌اند. در SQL Server 2022 و نسخه‌های جدیدتر معمولاً مجوز VIEW SERVER PERFORMANCE STATE لازم است؛ در نسخه‌های قدیمی‌تر VIEW SERVER STATE مبناست.

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

سؤال مصاحبه ۱: Memory Grant با Buffer Pool چه تفاوتی دارد؟

Buffer Pool عمدتاً صفحات داده و ساختارهای حافظه‌ای موتور را نگه می‌دارد، اما Query Execution Memory بخشی از حافظه است که برای Workspace عملگرهایی مانند Sort و Hash رزرو می‌شود. Grant مقدار رزروشده برای یک Request است و مصرف واقعی می‌تواند کمتر از آن باشد.

سؤال مصاحبه ۲: نشانه اصلی فشار حافظه اجرای Query چیست؟

وجود Grantهای منتظر با grant_time IS NULL، رشد wait_time_ms و Wait از نوع RESOURCE_SEMAPHORE نشانه‌های مهم‌اند. این علائم باید همراه ظرفیت Semaphore و حجم Grantهای فعال سنجیده شوند.

سؤال مصاحبه ۳: چرا pool_id در تحلیل Semaphore مهم است؟

در سیستم دارای Resource Governor هر Resource Pool مانند محدوده‌ای مستقل برای مدیریت منابع عمل می‌کند و Semaphoreهای خودش را دارد. ازاین‌رو resource_semaphore_id به‌تنهایی شناسه یکتا در کل Instance نیست و اتصال درست به pool_id نیز نیاز دارد.

سؤال مصاحبه ۴: RESOURCE_SEMAPHORE_QUERY_COMPILE به چه معناست؟

این Wait به محدودیت حافظه یا ظرفیت Gateهای زمان بهینه‌سازی و Compile مربوط است، نه Grant اجرای Query. Queryهای پیچیده، Compile هم‌زمان زیاد و Recompile مکرر می‌توانند آن را افزایش دهند.

سؤال مصاحبه ۵: چگونه Over-Grant را شناسایی می‌کنید؟

granted_memory_kb را با max_used_memory_kb در چند اجرای نماینده مقایسه می‌کنم و Plan، Cardinality Estimate و Memory Grant Feedback را نیز می‌بینم. نسبت بزرگ در یک Snapshot کافی نیست و باید الگوی پایدار و اثر آن بر Concurrency ثابت شود.

سؤال مصاحبه ۶: چه زمانی MAXDOP در این تحلیل مطرح می‌شود؟

وقتی Queryهای موازی Worker زیادی رزرو کرده‌اند، ظرفیت یک NUMA Node پایین است یا Grantهای بزرگ با DOP بالا هم‌زمان اجرا می‌شوند، MAXDOP بخشی از بررسی است. تغییر آن باید با تست بار و در کنار Cost Threshold و طراحی Query انجام شود.

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

• مجوز لازم برای خواندن DMVها تأیید شده است.
• زمان و چند Snapshot متوالی ثبت شده‌اند.
• Grantهای منتظر از Grantهای فعال جدا شده‌اند.
• حجم درخواست، Grant و بیشینه مصرف واقعی مقایسه شده است.
• Pool و Semaphore با کلید ترکیبی درست تطبیق داده شده‌اند.
• Waitهای Execution و Compile از هم تفکیک شده‌اند.
• Workerهای Parallel در سطح هر NUMA Node بررسی شده‌اند.
• Plan، Statistics، Cardinality و Spill برای Queryهای غالب تحلیل شده‌اند.
• اقدام اصلاحی با Baseline و امکان بازگشت آزمایش شده است.
• Collector تشخیصی سربار غیرضروری ایجاد نمی‌کند.

جمع‌بندی

عیب‌یابی حافظه Query یک مسئله چندلایه است. sys.dm_exec_query_memory_grants عامل‌های سطح Request را نشان می‌دهد، sys.dm_exec_query_resource_semaphores ظرفیت و صف سطح Pool را روشن می‌کند، sys.dm_exec_query_optimizer_memory_gateways فشار Compile را جدا می‌سازد و sys.dm_exec_query_parallel_workers محدودیت Worker موازی را آشکار می‌کند. وقتی این چهار نما با Wait Statistics، Plan و Baseline ترکیب شوند، تصمیم میان اصلاح Query، تغییر تنظیمات Workload و افزایش ظرفیت به شواهد متکی خواهد بود.

برای ادامه، راهنماهای تخصصی Memory Grants، Resource Semaphores، Optimizer Memory Gateways و Parallel Workers را مطالعه کنید. هر مقاله شامل ده Query مستقل، خروجی نمونه، خطاهای رایج، نکات سازگاری نسخه و چک‌لیست عملی است.