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

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

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

نظرات 0

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

مقدمه

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

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

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

تعریف و Syntax

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

OPTION (MIN_GRANT_PERCENT = 5);
مولفهتوضیحنکته عملی
MIN_GRANT_PERCENTتعیین کف درصد Memory Grant برای Queryفقط پس از اندازه‌گیری اعمال شود
ScopeQuery یا Scope مخصوص همان قابلیتQueryهای حساس به کمبود حافظه که با Grant ناکافی مرتب Spill می‌کنند
ریسک اصلیرزرو حداقل حافظه بالا می‌تواند همزمانی Queryها را کاهش دهد.Rollback plan داشته باشید
نقشه مفهومی و معماری برای MIN_GRANT_PERCENT در SQL Serverدیاگرام فنی اختصاصی MIN_GRANT_PERCENT شامل Optimizer، Execution Plan، ورودی، خروجی، خطا و ملاحظات کارایی.MIN_GRANT_PERCENT — نقشه مفهومی و معماریMIN_GRANT_PERCENTMIN_GRANT_PERCENTQuery OptimizerMIN_GRANT_PERCENTExecution PlanMIN_GRANT_PERCENTCardinality EstimateMIN_GRANT_PERCENTMemory/CPUMIN_GRANT_PERCENTQuery StoreMIN_GRANT_PERCENTQuery → Optimizer → Plan → Runtime Metrics → Decision | MIN_GRANT_PERCENT

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

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

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

  • Syntax پایه: OPTION (MIN_GRANT_PERCENT = 5)
  • قبل از 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 (MIN_GRANT_PERCENT = 2);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 1Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 1: در مورد MIN_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 (MIN_GRANT_PERCENT = 5);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 2Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 2: در مورد MIN_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 (MIN_GRANT_PERCENT = 8);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 3Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 3: در مورد MIN_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 (MIN_GRANT_PERCENT = 10);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 4Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

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

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

تصویر دوم جریان اجرای Query دارای MIN_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 (MIN_GRANT_PERCENT = 1);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 5Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 5: در مورد MIN_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 (MIN_GRANT_PERCENT = 2);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 6Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 6: در مورد MIN_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 (MIN_GRANT_PERCENT = 5);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 7Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 7: در مورد MIN_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 (MIN_GRANT_PERCENT = 8);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 8Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

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

سناریوی تصمیم‌گیری، خطا و Performance برای MIN_GRANT_PERCENT در SQL Serverدیاگرام فنی اختصاصی MIN_GRANT_PERCENT شامل Optimizer، Execution Plan، ورودی، خروجی، خطا و ملاحظات کارایی.MIN_GRANT_PERCENT — سناریوی تصمیم‌گیری، خطا و PerformanceMIN_GRANT_PERCENTMIN_GRANT_PERCENTBaselineMIN_GRANT_PERCENTRegression RiskMIN_GRANT_PERCENTCPU/ReadsMIN_GRANT_PERCENTSpill/RecompileMIN_GRANT_PERCENTBest PracticeMIN_GRANT_PERCENTQuery → Optimizer → Plan → Runtime Metrics → Decision | MIN_GRANT_PERCENT

تصویر سوم یک تصمیم Production را برای MIN_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 (MIN_GRANT_PERCENT = 10);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 9Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

نکته کاربردی مثال 9: در مورد MIN_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 (MIN_GRANT_PERCENT = 1);
خروجی نمونهچه چیزی بررسی شودتصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 10Actual Rows، Estimated Rows، CPU، Reads و Plan Shapeاثر MIN_GRANT_PERCENT را با Baseline مقایسه کنید

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

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

یکی از خطاهای رایج این است که MIN_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

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

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

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

آیا MIN_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 یا توزیع داده می‌تواند علت اولیه را از بین ببرد.

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

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

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

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

جمع‌بندی

MIN_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