Percentile Memory Grant Feedback؛ آموزش کامل MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT در SQL Server

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

در Queryهایی که مصرف حافظه بین اجراها تغییر می‌کند، نگاه به مجموعه اجراهای قبلی می‌تواند Grant باثبات‌تری بسازد و رفتار رفت‌وبرگشتی را کمتر کند. در این مقاله از Syntax و خواندن مقدار فعلی شروع می‌کنیم و بعد به سناریوی واقعی، روش اندازه‌گیری، خطاهای رایج و تصمیم Production می‌رسیم.

برای دیدن ارتباط این گزینه با سایر تنظیمات، راهنمای جامع Database Scoped Performance Configuration را هم ببینید.

تعریف و منطق فنی

محاسبه Memory Grant بر اساس توزیع اجراهای قبلی به‌جای تکیه ساده بر آخرین اجرا.

در Queryهایی که مصرف حافظه بین اجراها تغییر می‌کند، نگاه به مجموعه اجراهای قبلی می‌تواند Grant باثبات‌تری بسازد و رفتار رفت‌وبرگشتی را کمتر کند.

اصل عملی: تنظیم Performance فقط وقتی ارزش دارد که مسئله، معیار موفقیت و راه برگشت آن از قبل روشن باشد.

Syntax اصلی

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;

مقادیر و رفتار

• مقدار یا فرمان: ON یا OFF.
• سناریوی مناسب: گزارش فروش با بازه تاریخ از یک روز تا چند سال و مصرف حافظه بسیار متفاوت
• ریسک اصلی: اگر Query ماهیت کاملاً چندشکلی داشته باشد، یک Percentile هم برای همه الگوها مناسب نیست و Query Design مهم می‌ماند.
• نکته نسخه: Percentile mode برای Memory Grant Feedback در SQL Server 2022 معرفی شد و با Query Store و Feedback جدید مرتبط است.

این نقشه رابطه MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT را با Memory Grant، History و شاخص‌های Runtime نشان می‌دهد. تصویر برای فهم دامنه اثر است و جای Execution Plan واقعی را نمی‌گیرد.

چطور قبل از تغییر تصمیم بگیریم

قبل از اجرای تغییر، Queryهای نماینده را بر اساس اهمیت تجاری و مصرف منابع جدا کنید. Plan آنها را در Query Store نگه دارید و فقط یک متغیر را تغییر دهید. اگر هم‌زمان Index، Compatibility Level و MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT را عوض کنید، Attribution نتیجه از بین می‌رود.

معیار تک Query و معیار کل Workload را جدا ببینید. ممکن است یک Query سریع‌تر شود اما Throughput کل افت کند. اگر Query ماهیت کاملاً چندشکلی داشته باشد، یک Percentile هم برای همه الگوها مناسب نیست و Query Design مهم می‌ماند. به همین دلیل Concurrency و مصرف منابع باید کنار زمان پاسخ سنجیده شود.

اشتباه رایج: انتظار داشتن که Percentile Grant مشکل Predicate بد یا Statistics قدیمی را بدون اصلاح ریشه‌ای حل کند. راه حرفه‌ای این است که فرضیه، Baseline، Change و Rollback در یک سند کوتاه و قابل بازبینی ثبت شوند.

جریان MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT از مشاهده وضعیت، اعمال Change، Compile یا Runtime Behavior و سپس Measure عبور می‌کند. نقاط اندازه‌گیری قبل و بعد باید یکسان باشند.

۱۰ مثال عملی و قابل اجرا

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT';

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

SELECT DB_NAME() AS DatabaseName, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT desired_state_desc, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (1000) object_id, name, type_desc
FROM sys.objects
ORDER BY object_id DESC;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT TOP (20) session_id, requested_memory_kb, granted_memory_kb, required_memory_kb, used_memory_kb
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

SELECT TOP (50) *
FROM sys.query_store_plan_feedback
ORDER BY last_updated_time DESC;

IF CAST(SERVERPROPERTY('ProductMajorVersion') AS int) >= 13
BEGIN
    ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;
END
ELSE
    THROW 50010, N'نسخه موتور برای این Deployment بررسی شود.', 1;

-- Rollback کنترل‌شده
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT';

خطاهای رایج

• انتظار داشتن که Percentile Grant مشکل Predicate بد یا Statistics قدیمی را بدون اصلاح ریشه‌ای حل کند.
• اعمال چند تغییر Performance در یک Release و از دست دادن امکان تشخیص علت.
• مقایسه Warm Cache با Cold Cache و نتیجه‌گیری قطعی.
• نادیده گرفتن تفاوت نسخه، CU و Compatibility Level بین Test و Production.
• تمرکز فقط روی Duration و ندیدن CPU، Reads، Memory، Spill، Blocking یا Throughput.

Performance Considerations

اثر MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT را با Metric همان Feature بسنجید. مفاهیم کلیدی این مقاله: Percentile Grant, Memory Grant, History, Query Store, Variance. اگر معیار انتخابی با این زنجیره ارتباطی ندارد، آزمایش ممکن است نتیجه گمراه‌کننده بدهد.

اگر Query ماهیت کاملاً چندشکلی داشته باشد، یک Percentile هم برای همه الگوها مناسب نیست و Query Design مهم می‌ماند. این ریسک دلیل طراحی آزمایش بهتر است، نه دلیل تصمیم عجولانه. Query Store، Actual Plan و DMVهای Runtime را کنار هم ببینید.

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

1. مقدار فعلی، نسخه Engine و Compatibility Level را ثبت کنید.
2. Baseline Queryهای حیاتی را بگیرید.
3. تغییر را ابتدا در محیط مشابه Production یا بازه کم‌ریسک اجرا کنید.
4. فقط یک متغیر Performance را عوض کنید.
5. Rollback را پیش از Deployment بنویسید.
6. Plan و Runtime را در چند بازه بررسی کنید.
7. اگر نتیجه مبهم است تغییر را دائمی نکنید.

سناریوی عملی MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT سه بخش دارد: Baseline، تغییر کنترل‌شده و تصمیم بر اساس Measure. حذف هر کدام کیفیت تصمیم را پایین می‌آورد.

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

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT دقیقاً چه مشکلی را هدف می‌گیرد؟

برای محاسبه Memory Grant بر اساس توزیع اجراهای قبلی به‌جای تکیه ساده بر آخرین اجرا طراحی شده است. ارزش آن زمانی روشن می‌شود که قبل از تغییر Baseline داشته باشید و اثر را روی Workload واقعی بسنجید.

مقدار فعلی MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT را چگونه ببینیم؟

از sys.database_scoped_configurations شروع کنید و خروجی را کنار نسخه Engine، Compatibility Level و وضعیت Query Store ثبت کنید.

آیا MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT همیشه SQL Server را سریع‌تر می‌کند؟

خیر. اگر Query ماهیت کاملاً چندشکلی داشته باشد، یک Percentile هم برای همه الگوها مناسب نیست و Query Design مهم می‌ماند. بنابراین CPU، Duration، Reads و Metric مرتبط با Feature باید قبل و بعد مقایسه شوند.

چه سناریوی تجاری برای MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT مناسب است؟

نمونه روشن: گزارش فروش با بازه تاریخ از یک روز تا چند سال و مصرف حافظه بسیار متفاوت. در پروژه واقعی Change Window، Rollback و معیار موفقیت را قبل از تغییر تعریف کنید.

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT چه تفاوتی با تنظیم سراسری دارد؟

Database Scoped Configuration دامنه اثر را محدودتر می‌کند و برای Instanceهای چند Workload کنترل دقیق‌تری می‌دهد؛ با این حال Hintهای Query و تنظیمات دیگر ممکن است رفتار نهایی را تغییر دهند.

برای پیاده‌سازی حرفه‌ای MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT چه کاری لازم است؟

بررسی Query Store، Execution Plan، Baseline، تست بار و Rollback. آموزش و مشاوره زمانی مفید است که تصمیم بر اساس داده همان سامانه گرفته شود.

رایج‌ترین خطا درباره MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT چیست؟

انتظار داشتن که Percentile Grant مشکل Predicate بد یا Statistics قدیمی را بدون اصلاح ریشه‌ای حل کند. این کار اغلب یک درمان موقت را به سیاست دائمی تبدیل می‌کند.

اثر Performance MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT را چگونه بسنجیم؟

بازه‌های هم‌سطح را مقایسه کنید و CPU، Duration، Reads و Metric تخصصی Feature را بسنجید. Query Store برای Plan Regression بسیار مفید است.

Best Practice تغییر MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT چیست؟

تغییر را کوچک، مستند و قابل برگشت نگه دارید؛ فقط یک متغیر Performance را در هر آزمایش عوض کنید و نتیجه را چند بار بسنجید.

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT با چه نسخه‌هایی سازگار است؟

Percentile mode برای Memory Grant Feedback در SQL Server 2022 معرفی شد و با Query Store و Feedback جدید مرتبط است. مستندات همان نسخه و Compatibility Level محیط خود را ملاک نهایی قرار دهید.

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

1. چرا برای MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT Baseline لازم است؟
2. اگر بعد از تغییر MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT یک Query بهتر و چند Query بدتر شوند چه می‌کنید؟
3. Query Store در ارزیابی Percentile Memory Grant Feedback چه نقشی دارد؟
4. چطور مشکل Statistics را از اثر MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT جدا می‌کنید؟
5. Rollback شما برای MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT در Production چیست؟

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

• مقدار فعلی MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT ثبت شده است.
• Baseline مشخص است.
• نسخه و Compatibility Level کنترل شده‌اند.
• معیار موفقیت و شکست نوشته شده‌اند.
• Rollback آماده است.
• Query Store و Planهای مهم بعد از تغییر بررسی می‌شوند.

جمع‌بندی

Percentile Memory Grant Feedback زمانی مفید است که با مسئله واقعی و قابل اندازه‌گیری استفاده شود. محاسبه Memory Grant بر اساس توزیع اجراهای قبلی به‌جای تکیه ساده بر آخرین اجرا. به‌جای تغییر بر اساس توصیه عمومی، Workload خودتان را بسنجید و تغییر را قابل برگشت نگه دارید.

برای مقایسه این گزینه با سایر اعضای همین خانواده به مقاله مادر تنظیمات Performance در سطح Database برگردید.