آموزش MAX_GRANT_PERCENT در SQL Server؛ کاربرد، مثال و نکات Performance

آموزش MAX_GRANT_PERCENT در SQL Server؛ کاربرد، مثال و نکات Performance

توسط admin | گروه SQL Server | 1405/05/02

نظرات 0

آموزش MAX_GRANT_PERCENT در SQL Server؛ کاربرد، مثال و نکات Performance

مقدمه

MAX_GRANT_PERCENT یکی از ابزارهای کنترل رفتار Query Optimizer در Microsoft SQL Server است. هدف اصلی آن تعیین سقف درصد Memory Grant برای Query است. استفاده حرفه‌ای از Hint زمانی ارزشمند است که ابتدا Query، Statistics، Indexها، Cardinality Estimation و Execution Plan اندازه‌گیری شده باشند؛ Hint نباید جایگزین طراحی صحیح Schema یا رفع علت ریشه‌ای مشکل شود.

در سناریوی واقعی، کنترل Queryهایی که حافظه بیش از حد رزرو می‌کنند و Concurrent workload را مختل می‌کنند. با این حال باید بدانیم که سقف خیلی کم می‌تواند Spill به tempdb و افت شدید کارایی ایجاد کند. بنابراین قبل و بعد از اعمال MAX_GRANT_PERCENT لازم است زمان CPU، Logical Reads، Duration، Memory Grant، Spill، تعداد Recompile و پایداری Plan مقایسه شود. این مقاله از مثال‌های ساده شروع می‌کند و سپس به تصمیم‌گیری Production، خطاهای رایج و روش آزمون امن می‌رسد.

راهنمای جامع همه Query Hintهای این مجموعه در مقاله مادر Query Hints در SQL Server قرار دارد و برای مقایسه این Hint با گزینه‌های دیگر می‌توانید به آن برگردید.

تعریف و Syntax

MAX_GRANT_PERCENT به Optimizer یا موتور اجرای SQL Server یک محدودیت، ترجیح یا رفتار خاص را اعلام می‌کند. Syntax مرجع این مقاله به شکل زیر است؛ جزئیات دقیق Scope به نوع Hint وابسته است و بعضی گزینه‌ها فقط در OPTION، برخی در Database Setting یا Plan Guide معنا دارند.

OPTION (MAX_GRANT_PERCENT = 25);
مولفهتوضیحنکته عملی
MAX_GRANT_PERCENTتعیین سقف درصد Memory Grant برای Queryفقط پس از اندازه‌گیری اعمال شود
ScopeQuery یا Scope مخصوص همان قابلیتکنترل Queryهایی که حافظه بیش از حد رزرو می‌کنند و Concurrent workload را مختل می‌کنند
ریسک اصلیسقف خیلی کم می‌تواند Spill به tempdb و افت شدید کارایی ایجاد کند.Rollback plan داشته باشید
نقشه مفهومی و معماری برای MAX_GRANT_PERCENT در SQL Serverدیاگرام فنی اختصاصی MAX_GRANT_PERCENT شامل Optimizer، Execution Plan، ورودی، خروجی، خطا و ملاحظات کارایی.MAX_GRANT_PERCENT — نقشه مفهومی و معماریMAX_GRANT_PERCENTMAX_GRANT_PERCENTQuery OptimizerMAX_GRANT_PERCENTExecution PlanMAX_GRANT_PERCENTCardinality EstimateMAX_GRANT_PERCENTMemory/CPUMAX_GRANT_PERCENTQuery StoreMAX_GRANT_PERCENTQuery → Optimizer → Plan → Runtime Metrics → Decision | MAX_GRANT_PERCENT

تصویر اول ارتباط MAX_GRANT_PERCENT با Query Optimizer، Plan، برآوردها و منابع Runtime را نشان می‌دهد. نقطه تصمیم اصلی این است که Hint باید بر مبنای مشاهده Plan و Metricها انتخاب شود، نه صرفاً برای حذف یک علامت هشدار.

پارامترها، خروجی و اثر روی Plan

خروجی مستقیم MAX_GRANT_PERCENT یک مقدار Scalar نیست؛ اثر آن در شکل Execution Plan، انتخاب Operator، نحوه Compilation یا مصرف منابع دیده می‌شود. برای ارزیابی صحیح باید Actual Execution Plan و آمار اجرای Query را کنار هم قرار دهید. در Queryهای پارامتری، تفاوت مقادیر ورودی و Skew داده اهمیت ویژه دارد.

  • Syntax پایه: OPTION (MAX_GRANT_PERCENT = 25)
  • قبل از Hint، Baseline بدون Hint ثبت شود.
  • Query Store برای مقایسه Plan و Runtime Statistics مفید است.
  • Hint را با نسخه SQL Server و Compatibility Level محیط مقصد بررسی کنید.
  • بعد از تغییر Statistics یا Indexها، نیاز به Hint را دوباره ارزیابی کنید.

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

مثال 1: آزمایش پایه روی Catalog Views

آزمایش پایه روی Catalog Views برای مشاهده اثر Hint بدون وابستگی به جدول کسب‌وکار.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 20);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 1Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 1: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 2: سناریوی پارامتری

سناریوی پارامتری برای بررسی اینکه مقدار ورودی چگونه روی Plan و برآورد ردیف اثر می‌گذارد.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 25);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 2Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 2: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 3: استفاده در SELECT با محدودیت ردیف

استفاده در SELECT با محدودیت ردیف برای مقایسه زمان دریافت اولین نتیجه و کل زمان اجرا.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 35);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 3Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 3: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 4: اعمال شرط و بررسی Selectivity

اعمال شرط و بررسی Selectivity برای دیدن تفاوت دسترسی به داده و انتخاب Operator.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 50);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 4Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 4: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

جریان اجرا از Query تا Plan برای MAX_GRANT_PERCENT در SQL Serverدیاگرام فنی اختصاصی MAX_GRANT_PERCENT شامل Optimizer، Execution Plan، ورودی، خروجی، خطا و ملاحظات کارایی.MAX_GRANT_PERCENT — جریان اجرا از Query تا PlanMAX_GRANT_PERCENTMAX_GRANT_PERCENTCompileMAX_GRANT_PERCENTEstimateMAX_GRANT_PERCENTChoose PlanMAX_GRANT_PERCENTExecuteMAX_GRANT_PERCENTRuntime StatsMAX_GRANT_PERCENTQuery → Optimizer → Plan → Runtime Metrics → Decision | MAX_GRANT_PERCENT

تصویر دوم جریان اجرای Query دارای MAX_GRANT_PERCENT را از مرحله Parse و Compile تا انتخاب Plan و جمع‌آوری Runtime Metricها نشان می‌دهد. این جریان کمک می‌کند محل واقعی اثر Hint را از علائم ظاهری Performance جدا کنیم.

مثال 5: ترکیب با Join یا Aggregate

ترکیب با Join یا Aggregate برای بررسی تعامل Hint با شکل پیچیده‌تر Plan.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 10);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 5Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 5: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 6: بررسی رفتار با ورودی کم‌حجم و حالتی که نتیجه ممکن است خالی باشد.

بررسی رفتار با ورودی کم‌حجم و حالتی که نتیجه ممکن است خالی باشد.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 20);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 6Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 6: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 7: حالت مرزی

حالت مرزی برای مقایسه Plan در شرایط تغییر تعداد ردیف یا توزیع نامتوازن.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 25);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 7Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 7: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 8: سناریوی گزارش‌گیری سازمانی و مقایسه Duration، CPU و Logical Reads.

سناریوی گزارش‌گیری سازمانی و مقایسه Duration، CPU و Logical Reads.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 35);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 8Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 8: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

سناریوی تصمیم‌گیری، خطا و Performance برای MAX_GRANT_PERCENT در SQL Serverدیاگرام فنی اختصاصی MAX_GRANT_PERCENT شامل Optimizer، Execution Plan، ورودی، خروجی، خطا و ملاحظات کارایی.MAX_GRANT_PERCENT — سناریوی تصمیم‌گیری، خطا و PerformanceMAX_GRANT_PERCENTMAX_GRANT_PERCENTBaselineMAX_GRANT_PERCENTRegression RiskMAX_GRANT_PERCENTCPU/ReadsMAX_GRANT_PERCENTSpill/RecompileMAX_GRANT_PERCENTBest PracticeMAX_GRANT_PERCENTQuery → Optimizer → Plan → Runtime Metrics → Decision | MAX_GRANT_PERCENT

تصویر سوم یک تصمیم Production را برای MAX_GRANT_PERCENT نمایش می‌دهد: ابتدا Baseline، سپس تست کنترل‌شده، بررسی Regression و در نهایت تصمیم برای نگه‌داشتن یا حذف Hint. این چرخه از قفل‌شدن ناخواسته روی یک Plan نامناسب جلوگیری می‌کند.

مثال 9: نمونه عیب‌یابی: اجرای Baseline، اعمال Hint و سپس مقایسه دلیل بهتر یا بدتر شدن Plan.

نمونه عیب‌یابی: اجرای Baseline، اعمال Hint و سپس مقایسه دلیل بهتر یا بدتر شدن Plan.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 50);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 9Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 9: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

مثال 10: آزمون Performance با تمرکز بر Plan Stability، Memory Grant، Parallelism و امکان Regression.

آزمون Performance با تمرکز بر Plan Stability، Memory Grant، Parallelism و امکان Regression.

SELECT o.type_desc, COUNT_BIG(*) AS Cnt
FROM sys.objects AS o
JOIN sys.columns AS c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT = 10);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 10Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MAX_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 10: در مورد MAX_GRANT_PERCENT صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش می‌دهد یا فقط یک اجرای منفرد را سریع‌تر نشان می‌دهد.

خطاهای رایج و روش عیب‌یابی

یکی از خطاهای رایج این است که MAX_GRANT_PERCENT بدون مشاهده Actual Execution Plan به Query اضافه شود. خطای دیگر، نتیجه‌گیری بر اساس یک اجرای گرم یا سرد Cache است. در بعضی Hintها اگر محدودیت اعمال‌شده مانع ساخت Plan معتبر شود، Optimizer می‌تواند با خطا مواجه شود. همچنین تغییر نسخه، Compatibility Level، Statistics و حجم داده ممکن است نتیجه‌ای را که امروز خوب است در آینده تغییر دهد.

  • Baseline را با SET STATISTICS IO, TIME و Query Store ثبت کنید.
  • Plan بدون Hint و با Hint را در شرایط پارامترهای متفاوت مقایسه کنید.
  • Spill، Memory Grant، Parallelism، Warnings و Recompile را بررسی کنید.
  • در محیط Production مسیر بازگشت سریع و امکان حذف Hint داشته باشید.

نکات Performance و Best Practices

بهترین روش برای MAX_GRANT_PERCENT این است که آن را یک ابزار جراحی بدانیم، نه تنظیم پیش‌فرض. ابتدا Index و Statistics و Query Design را اصلاح کنید؛ سپس اگر هنوز محدودیت Optimizer یا نوسان Plan باقی بود Hint را با شواهد اعمال کنید. برای Queryهای پرتکرار اثر کوچک روی CPU یا Reads می‌تواند در مقیاس کل سیستم بسیار مهم باشد.

در تست A/B، فقط Duration را نبینید. ممکن است MAX_GRANT_PERCENT زمان Query را کم کند ولی Memory Grant، Thread مصرفی یا فشار tempdb را بالا ببرد. معیار درست، هزینه کل workload و پایداری در ساعات اوج است. همچنین پس از ارتقای SQL Server، تغییر Cardinality Estimator یا افزودن Index جدید، Hintهای قدیمی باید دوباره بازبینی شوند.

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

آیا MAX_GRANT_PERCENT همیشه Performance را بهتر می‌کند؟

خیر. Hint فضای تصمیم Optimizer را تغییر می‌دهد و فقط در سناریویی که علت مشکل دقیقاً شناخته شده باشد می‌تواند مفید باشد. استفاده کورکورانه ممکن است Regression ایجاد کند.

چطور اثر Hint را اندازه‌گیری کنیم؟

Actual Execution Plan، Query Store، Duration، CPU time، Logical Reads، Memory Grant، Spill و تعداد اجرا را قبل و بعد مقایسه کنید و چند مقدار پارامتر متفاوت را آزمایش کنید.

آیا بعد از رفع مشکل می‌توان Hint را حذف کرد؟

بله و حتی باید دوره‌ای نیاز آن را بازبینی کنید. تغییر Statistics، Index، نسخه SQL Server یا توزیع داده می‌تواند علت اولیه را از بین ببرد.

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

  • MAX_GRANT_PERCENT چه مشکلی را حل می‌کند و چه زمانی نباید از آن استفاده کرد؟
  • چطور برای MAX_GRANT_PERCENT یک Baseline قابل اعتماد می‌سازید؟
  • اگر Plan با Hint سریع‌تر ولی CPU کل بیشتر شود چه تصمیمی می‌گیرید؟
  • Query Store چگونه در ارزیابی یا جایگزینی Hint کمک می‌کند؟

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

  • علت ریشه‌ای مشکل مشخص شده است.
  • Plan و Runtime Metrics قبل از تغییر ذخیره شده‌اند.
  • حداقل چند مقدار پارامتر و حجم داده تست شده است.
  • اثر روی Concurrent workload و tempdb بررسی شده است.
  • Rollback و بازبینی دوره‌ای Hint تعریف شده است.

جمع‌بندی

MAX_GRANT_PERCENT می‌تواند برای تعیین سقف درصد Memory Grant برای Query ابزار قدرتمندی باشد، اما ارزش واقعی آن زمانی مشخص می‌شود که با اندازه‌گیری و شناخت Optimizer همراه باشد. اصل حرفه‌ای این است: ابتدا علت را پیدا کنید، سپس کوچک‌ترین مداخله لازم را انجام دهید و نتیجه را با داده واقعی بسنجید.

برای مقایسه این قابلیت با سایر گزینه‌ها به راهنمای جامع Query Hints در SQL Server مراجعه کنید.

خدمات برنامه‌نویسی و پایگاه داده

قبول سفارش‌های برنامه‌نویسی و پایگاه داده در مجموعه برنامه‌نویسی در اصفهان با شماره سفارش 09131253620. مجموعه‌ای معتبر با سابقه فعالیت حرفه‌ای از سال ۱۳۷۵ شمسی تاکنون؛ انجام پروژه، آموزش برنامه‌نویسی و آموزش پایگاه داده SQL Server.

برای سفارش پروژه‌های برنامه‌نویسی و پایگاه داده، سیستم‌های تحت وب، وب‌سایت و راهکارهای نرم‌افزاری جدید با شماره 09131253620 تماس حاصل فرمایید. ایتا، واتساپ و تماس مستقیم: +989131253620 — تماس با ما

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر

اطلاعات تماس

  • آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
  • آدرس ایمیل:najafzade@gmail.com
  • وب سایت:http://www.a00b.com/
  • تلفن ثابت:(+98)9131253620
  • تلفن همراه:09131253620