راهنمای جامع Query Hints در SQL Server؛ از RECOMPILE تا کنترل Plan و Memory Grant

Query Hint چیست و چرا باید با احتیاط استفاده شود؟

Query Hint در SQL Server روشی برای اثرگذاری مستقیم بر تصمیم‌های Query Optimizer است. Optimizer معمولاً با تکیه بر Statistics، Cardinality Estimation، Cost Model، Indexها و محدودیت منابع Plan را انتخاب می‌کند. Hint به ما اجازه می‌دهد بخشی از این آزادی را محدود یا جهت‌دهی کنیم؛ برای مثال Query را دوباره Compile کنیم، روش Join را محدود کنیم، Parallelism را کنترل کنیم، Memory Grant را سقف‌گذاری کنیم یا رفتار Parameter Sniffing را تغییر دهیم.

قدرت Hint همان چیزی است که آن را خطرناک می‌کند. یک Hint که امروز روی یک حجم داده و یک نسخه SQL Server نتیجه عالی دارد، ممکن است بعد از رشد جدول، تغییر Statistics، ایجاد Index جدید یا ارتقای Compatibility Level مانع انتخاب Plan بهتر شود. بنابراین Query Hint باید آخرین مرحله یک فرایند عیب‌یابی مبتنی بر شواهد باشد، نه اولین واکنش به یک Query کند.

این مجموعه ۲۹ Hint و قابلیت مرتبط را از سطح مقدماتی تا Production بررسی می‌کند. هر موضوع صفحه مستقل با Syntax، مثال‌های عملی، خروجی نمونه، خطاها، Performance Considerations و Best Practices دارد.

دسترسی سریع به آموزش‌ها

• آموزش کامل RECOMPILE
• آموزش کامل OPTIMIZE FOR
• آموزش کامل OPTIMIZE FOR UNKNOWN
• آموزش کامل USE HINT
• آموزش کامل USE PLAN
• آموزش کامل MAXDOP
• آموزش کامل MAXRECURSION
• آموزش کامل FAST n
• آموزش کامل FORCE ORDER
• آموزش کامل LOOP JOIN
• آموزش کامل MERGE JOIN
• آموزش کامل HASH JOIN
• آموزش کامل HASH GROUP
• آموزش کامل ORDER GROUP
• آموزش کامل CONCAT UNION
• آموزش کامل HASH UNION
• آموزش کامل MERGE UNION
• آموزش کامل KEEP PLAN
• آموزش کامل KEEPFIXED PLAN
• آموزش کامل ROBUST PLAN
• آموزش کامل MAX_GRANT_PERCENT
• آموزش کامل MIN_GRANT_PERCENT
• آموزش کامل NO_PERFORMANCE_SPOOL
• آموزش کامل EXPAND VIEWS
• آموزش کامل IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
• آموزش کامل PARAMETERIZATION SIMPLE
• آموزش کامل PARAMETERIZATION FORCED
• آموزش کامل QUERYTRACEON
• آموزش کامل DISABLE_OPTIMIZED_PLAN_FORCING

نقشه مفهومی بالا نشان می‌دهد Hint بین متن Query و تصمیم Optimizer قرار می‌گیرد و اثر نهایی آن باید در Execution Plan و Runtime Metrics سنجیده شود. هیچ Hintی را فقط بر اساس نام یا توصیه عمومی انتخاب نکنید.

دسته‌بندی Query Hintها

کامپایل و پارامترها

RECOMPILE، OPTIMIZE FOR، OPTIMIZE FOR UNKNOWN، PARAMETERIZATION SIMPLE/FORCED و KEEP PLAN خانواده‌ای از گزینه‌ها هستند که روی Compilation، Plan Reuse و حساسیت به پارامترها اثر می‌گذارند. مسئله اصلی در این گروه تعادل بین هزینه Compile و کیفیت Plan برای توزیع‌های متفاوت داده است.

شکل Plan و Operatorها

FORCE ORDER، LOOP JOIN، MERGE JOIN، HASH JOIN، HASH GROUP، ORDER GROUP و انواع UNION Hint فضای انتخاب Operator را محدود می‌کنند. این گزینه‌ها باید با شناخت اندازه ورودی‌ها، ترتیب داده، Index و هزینه Sort/Hash استفاده شوند.

منابع و همزمانی

MAXDOP، MAX_GRANT_PERCENT و MIN_GRANT_PERCENT مستقیماً با CPU، Worker و Memory Grant سروکار دارند. بهینه‌سازی یک Query منفرد بدون توجه به Concurrent workload ممکن است کل سرور را بدتر کند.

Plan forcing و رفتارهای تخصصی

USE PLAN، USE HINT، QUERYTRACEON، DISABLE_OPTIMIZED_PLAN_FORCING، EXPAND VIEWS و IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX برای کنترل‌های تخصصی‌تر هستند و معمولاً نیاز به آزمون دقیق‌تر و آگاهی از نسخه دارند.

جدول مقایسه‌ای همه موضوع‌ها

جریان دوم مسیر Query از متن T-SQL تا Compile، انتخاب Plan و اجرای Runtime را نشان می‌دهد. Hint ممکن است در یکی از این مراحل اثر بگذارد، بنابراین Metric مناسب برای هر Hint متفاوت است.

شش سناریوی عملی برای انتخاب Hint

سناریو 1: Parameter Sniffing متغیر

وقتی توزیع داده شدیداً نامتوازن است، RECOMPILE می‌تواند Plan را برای مقدار جاری بسازد؛ هزینه Compile باید سنجیده شود.

DECLARE @Type char(2)=N'U';
SELECT name FROM sys.objects WHERE type=@Type
OPTION (RECOMPILE);

سناریو 2: کنترل Parallelism

MAXDOP برای کنترل Worker و CPU مفید است اما مقدار بهینه به workload و تنظیمات سرور وابسته است.

SELECT o.type_desc, COUNT_BIG(*)
FROM sys.objects o JOIN sys.columns c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAXDOP 2);

سناریو 3: کنترل Join Algorithm

اجبار Hash Join فقط زمانی منطقی است که Planهای جایگزین و اندازه مجموعه‌ها بررسی شده باشند.

SELECT TOP (20) o.name,s.name
FROM sys.objects o JOIN sys.schemas s ON s.schema_id=o.schema_id
OPTION (HASH JOIN);

سناریو 4: کنترل Memory Grant

سقف حافظه می‌تواند همزمانی را بهتر کند ولی کمبود Grant احتمال Spill را افزایش می‌دهد.

SELECT o.type_desc,COUNT_BIG(*)
FROM sys.objects o JOIN sys.columns c ON c.object_id=o.object_id
GROUP BY o.type_desc
OPTION (MAX_GRANT_PERCENT=25);

سناریو 5: دریافت سریع ردیف‌های اول

FAST n برای تجربه تعاملی مفید است ولی الزاماً کل Query را سریع‌تر نمی‌کند.

SELECT name,create_date FROM sys.objects ORDER BY create_date DESC
OPTION (FAST 10);

سناریو 6: کنترل Recursive CTE

MAXRECURSION یک Guardrail مهم برای CTEهای بازگشتی است و خطاهای منطقی را زودتر آشکار می‌کند.

;WITH N AS (SELECT 1 n UNION ALL SELECT n+1 FROM N WHERE n<25)
SELECT n FROM N OPTION (MAXRECURSION 30);

روش حرفه‌ای ارزیابی Query Hint

یک فرایند قابل اعتماد با ثبت Baseline شروع می‌شود. Query را با پارامترهای نماینده و در چند وضعیت Cache اجرا کنید، Actual Plan را ذخیره کنید و Runtime Statistics را از Query Store یا ابزارهای پایش جمع‌آوری کنید. سپس دقیقاً یک تغییر اعمال کنید تا اثر آن قابل انتساب باشد. اگر چند Hint همزمان اضافه شوند، تشخیص اینکه کدام مورد علت بهبود یا Regression بوده دشوار می‌شود.

پس از یافتن Hint مؤثر، تست باید از سطح Query منفرد فراتر برود. Memory Grant، Thread، tempdb، Blocking و Throughput کل workload اهمیت دارند. گاهی Query A دو برابر سریع‌تر می‌شود اما به دلیل مصرف حافظه یا Worker بیشتر، Queryهای B تا Z کند می‌شوند. معیار نهایی موفقیت، سلامت کل سرویس است نه یک Execution Time منفرد.

Hintهای قدیمی نیز بدهی فنی هستند. هر ارتقای نسخه SQL Server، تغییر Compatibility Level، بازطراحی Index یا رشد بزرگ داده باید Trigger بازبینی Hintها باشد. Query Store و تاریخچه Planها کمک می‌کند بفهمید آیا محدودیتی که سال قبل لازم بود هنوز هم لازم است یا نه.

دیاگرام سوم چرخه تصمیم Production را نشان می‌دهد: Baseline، انتخاب Candidate، تست A/B، کنترل Regression، پایش Query Store و داشتن Rollback. این چرخه باید برای هر Hint مستقل تکرار شود.

معرفی تک‌تک Query Hintها و لینک آموزش کامل

RECOMPILE در SQL Server

بازکامپایل طرح اجرا برای هر بار اجرای Query. کاربرد شاخص: پارامتر اسنیفینگ، تغییر شدید توزیع داده و Queryهای حساس به مقدار ورودی. نکته احتیاطی: هزینه CPU کامپایل افزایش می‌یابد و امکان استفاده مجدد از Plan کاهش پیدا می‌کند.

مطالعه آموزش کامل RECOMPILE با مثال‌های عملی و نکات Performance

OPTIMIZE FOR در SQL Server

بهینه‌سازی Query بر اساس یک مقدار مشخص برای پارامتر. کاربرد شاخص: زمانی که یک مقدار نماینده برای توزیع داده دارید و Plan پایدارتر می‌خواهید. نکته احتیاطی: انتخاب مقدار نامناسب می‌تواند برای بخش بزرگی از ورودی‌ها Plan ضعیف ایجاد کند.

مطالعه آموزش کامل OPTIMIZE FOR با مثال‌های عملی و نکات Performance

OPTIMIZE FOR UNKNOWN در SQL Server

بهینه‌سازی بر اساس تخمین عمومی به‌جای مقدار واقعی پارامتر. کاربرد شاخص: کاهش اثر Parameter Sniffing در Queryهایی که ورودی‌های بسیار متنوع دارند. نکته احتیاطی: ممکن است Plan متوسطی بسازد که برای هیچ مقدار خاصی بهترین نباشد.

مطالعه آموزش کامل OPTIMIZE FOR UNKNOWN با مثال‌های عملی و نکات Performance

USE HINT در SQL Server

فعال‌کردن Hintهای نام‌دار و کنترل رفتار Optimizer. کاربرد شاخص: کنترل رفتارهای خاص Optimizer بدون استفاده مستقیم از Trace Flag. نکته احتیاطی: نام Hint باید در نسخه SQL Server شما پشتیبانی شود.

مطالعه آموزش کامل USE HINT با مثال‌های عملی و نکات Performance

USE PLAN در SQL Server

وادارکردن Optimizer به استفاده از یک ShowPlan XML مشخص. کاربرد شاخص: شرایط بسیار کنترل‌شده‌ای که یک Plan معتبر و آزموده‌شده در اختیار دارید. نکته احتیاطی: Plan XML باید دقیقاً معتبر و سازگار باشد؛ نگهداری آن دشوار است.

مطالعه آموزش کامل USE PLAN با مثال‌های عملی و نکات Performance

MAXDOP در SQL Server

محدودکردن درجه Parallelism برای یک Query. کاربرد شاخص: کنترل مصرف CPU، کاهش رقابت Workerها و پایدارکردن Queryهای سنگین. نکته احتیاطی: مقدار خیلی پایین می‌تواند Queryهای تحلیلی را کند و مقدار بالا سیستم را اشباع کند.

مطالعه آموزش کامل MAXDOP با مثال‌های عملی و نکات Performance

MAXRECURSION در SQL Server

محدودکردن تعداد تکرار CTE بازگشتی. کاربرد شاخص: کنترل Recursive CTE و جلوگیری از حلقه بازگشتی بی‌نهایت. نکته احتیاطی: مقدار 0 محدودیت را حذف می‌کند و باید با احتیاط استفاده شود.

مطالعه آموزش کامل MAXRECURSION با مثال‌های عملی و نکات Performance

FAST n در SQL Server

بهینه‌سازی برای بازگرداندن سریع تعداد اولیه‌ای از ردیف‌ها. کاربرد شاخص: صفحات تعاملی و سناریوهایی که Time-to-first-rows مهم‌تر از کل زمان Query است. نکته احتیاطی: ممکن است زمان کامل اجرای Query یا مصرف منابع برای کل نتیجه بدتر شود.

مطالعه آموزش کامل FAST n با مثال‌های عملی و نکات Performance

FORCE ORDER در SQL Server

حفظ ترتیب Joinهای نوشته‌شده در Query. کاربرد شاخص: وقتی ترتیب Join بر اساس شناخت دقیق داده و پس از اندازه‌گیری انتخاب شده است. نکته احتیاطی: ترتیب نامناسب می‌تواند فضای جستجوی Optimizer را محدود و Plan را بدتر کند.

مطالعه آموزش کامل FORCE ORDER با مثال‌های عملی و نکات Performance

LOOP JOIN در SQL Server

محدودکردن روش Join به Nested Loops در سطح Query. کاربرد شاخص: Joinهای Selective با ورودی کوچک و ایندکس مناسب روی سمت داخلی. نکته احتیاطی: برای مجموعه‌های بزرگ یا نبود ایندکس مناسب می‌تواند I/O را شدیداً بالا ببرد.

مطالعه آموزش کامل LOOP JOIN با مثال‌های عملی و نکات Performance

MERGE JOIN در SQL Server

محدودکردن روش Join به Merge Join. کاربرد شاخص: داده‌های مرتب یا دارای ایندکس مناسب و Join روی مجموعه‌های بزرگ. نکته احتیاطی: Sort اضافی می‌تواند هزینه زیادی ایجاد کند.

مطالعه آموزش کامل MERGE JOIN با مثال‌های عملی و نکات Performance

HASH JOIN در SQL Server

محدودکردن روش Join به Hash Join. کاربرد شاخص: Join مجموعه‌های بزرگ، نبود ترتیب مفید و Predicateهای برابری. نکته احتیاطی: Memory Grant زیاد یا Spill به tempdb از ریسک‌های اصلی است.

مطالعه آموزش کامل HASH JOIN با مثال‌های عملی و نکات Performance

HASH GROUP در SQL Server

وادارکردن Aggregation به Hash-based grouping. کاربرد شاخص: GROUP BY روی داده‌های بزرگ و بدون ترتیب مناسب. نکته احتیاطی: برآورد اشتباه حافظه می‌تواند باعث Spill و فشار tempdb شود.

مطالعه آموزش کامل HASH GROUP با مثال‌های عملی و نکات Performance

ORDER GROUP در SQL Server

وادارکردن Aggregation مبتنی بر Sort/Stream. کاربرد شاخص: زمانی که ورودی از قبل مرتب است یا ایندکس ترتیب مناسب فراهم می‌کند. نکته احتیاطی: اگر Sort اجباری شود، هزینه CPU و حافظه افزایش می‌یابد.

مطالعه آموزش کامل ORDER GROUP با مثال‌های عملی و نکات Performance

CONCAT UNION در SQL Server

وادارکردن روش Concatenation برای پردازش UNION. کاربرد شاخص: کنترل شکل فیزیکی پردازش مجموعه‌ها در Queryهای UNION. نکته احتیاطی: فقط پس از بررسی Execution Plan استفاده شود و همیشه بهترین انتخاب نیست.

مطالعه آموزش کامل CONCAT UNION با مثال‌های عملی و نکات Performance

HASH UNION در SQL Server

وادارکردن روش Hash برای عملیات UNION. کاربرد شاخص: حذف Duplicate در مجموعه‌های بزرگ بدون ترتیب مناسب. نکته احتیاطی: Hash table بزرگ می‌تواند Memory Grant و tempdb را تحت فشار بگذارد.

مطالعه آموزش کامل HASH UNION با مثال‌های عملی و نکات Performance

MERGE UNION در SQL Server

وادارکردن روش Merge برای عملیات UNION. کاربرد شاخص: ورودی‌های مرتب که امکان ادغام کارآمد مجموعه‌ها را فراهم می‌کنند. نکته احتیاطی: نیاز به Sort می‌تواند مزیت Merge را از بین ببرد.

مطالعه آموزش کامل MERGE UNION با مثال‌های عملی و نکات Performance

KEEP PLAN در SQL Server

نزدیک‌کردن آستانه Recompile جدول‌های موقت به جدول‌های دائمی. کاربرد شاخص: کاهش Recompileهای زیاد ناشی از تغییرات مکرر آماری در برخی workloadها. نکته احتیاطی: ممکن است Plan قدیمی دیرتر بازکامپایل شود و با توزیع جدید داده سازگار نباشد.

مطالعه آموزش کامل KEEP PLAN با مثال‌های عملی و نکات Performance

KEEPFIXED PLAN در SQL Server

جلوگیری از Recompile ناشی از تغییرات Statistics. کاربرد شاخص: سناریوهای بسیار خاص با نیاز به ثبات Plan و کنترل دقیق DBA. نکته احتیاطی: می‌تواند Plan نامناسب را طولانی‌تر نگه دارد؛ تغییر Schema همچنان می‌تواند Recompile ایجاد کند.

مطالعه آموزش کامل KEEPFIXED PLAN با مثال‌های عملی و نکات Performance

ROBUST PLAN در SQL Server

درخواست Plan مقاوم در برابر ردیف‌های با اندازه بالقوه بزرگ. کاربرد شاخص: Queryهایی با متغیرهای طولی بزرگ که احتمال خطای پردازش ردیف میانی وجود دارد. نکته احتیاطی: ممکن است Optimizer نتواند Plan بسازد یا گزینه‌ای پرهزینه‌تر انتخاب کند.

مطالعه آموزش کامل ROBUST PLAN با مثال‌های عملی و نکات Performance

MAX_GRANT_PERCENT در SQL Server

تعیین سقف درصد Memory Grant برای Query. کاربرد شاخص: کنترل Queryهایی که حافظه بیش از حد رزرو می‌کنند و Concurrent workload را مختل می‌کنند. نکته احتیاطی: سقف خیلی کم می‌تواند Spill به tempdb و افت شدید کارایی ایجاد کند.

مطالعه آموزش کامل MAX_GRANT_PERCENT با مثال‌های عملی و نکات Performance

MIN_GRANT_PERCENT در SQL Server

تعیین کف درصد Memory Grant برای Query. کاربرد شاخص: Queryهای حساس به کمبود حافظه که با Grant ناکافی مرتب Spill می‌کنند. نکته احتیاطی: رزرو حداقل حافظه بالا می‌تواند همزمانی Queryها را کاهش دهد.

مطالعه آموزش کامل MIN_GRANT_PERCENT با مثال‌های عملی و نکات Performance

NO_PERFORMANCE_SPOOL در SQL Server

جلوگیری از افزودن بعضی Spoolهای صرفاً Performance. کاربرد شاخص: عیب‌یابی Spoolهای پرهزینه یا مصرف زیاد tempdb در شرایط خاص. نکته احتیاطی: Spool ممکن است برای جلوگیری از تکرار کار مفید باشد؛ حذف آن همیشه بهتر نیست.

مطالعه آموزش کامل NO_PERFORMANCE_SPOOL با مثال‌های عملی و نکات Performance

EXPAND VIEWS در SQL Server

وادارکردن Optimizer به Expand کردن Indexed View به تعریف پایه. کاربرد شاخص: مقایسه رفتار Query با و بدون استفاده مستقیم از Indexed View. نکته احتیاطی: ممکن است مزیت Materialization نمای ایندکس‌شده از دست برود.

مطالعه آموزش کامل EXPAND VIEWS با مثال‌های عملی و نکات Performance

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX در SQL Server

نادیده‌گرفتن Nonclustered Columnstore Index برای Query. کاربرد شاخص: عیب‌یابی یا مقایسه Planهای Rowstore و Columnstore در workloadهای ترکیبی. نکته احتیاطی: محروم‌کردن Query تحلیلی از Columnstore ممکن است کارایی را به‌شدت کاهش دهد.

مطالعه آموزش کامل IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX با مثال‌های عملی و نکات Performance

PARAMETERIZATION SIMPLE در SQL Server

کنترل Parameterization ساده، عمدتاً در Database Option یا Plan Guide از نوع TEMPLATE. کاربرد شاخص: زمانی که می‌خواهید SQL Server فقط الگوهای واجد شرایط را خودکار Parameterize کند. نکته احتیاطی: این موضوع مانند Hint عادی OPTION برای هر Query آزادانه استفاده نمی‌شود و Scope آن مهم است.

مطالعه آموزش کامل PARAMETERIZATION SIMPLE با مثال‌های عملی و نکات Performance

PARAMETERIZATION FORCED در SQL Server

فعال‌کردن Forced Parameterization در سطح Database یا Plan Guide مناسب. کاربرد شاخص: کاهش Plan Cache bloat در workloadهای Ad-hoc با Literalهای فراوان. نکته احتیاطی: برای Queryهای دارای Skew شدید ممکن است Parameter Sniffing و Plan نامناسب تشدید شود.

مطالعه آموزش کامل PARAMETERIZATION FORCED با مثال‌های عملی و نکات Performance

QUERYTRACEON در SQL Server

فعال‌کردن Trace Flag پشتیبانی‌شده در Scope همان Query. کاربرد شاخص: کنترل رفتار Optimizer برای Trace Flagهای مستند و مناسب Query scope. نکته احتیاطی: همه Trace Flagها قابل استفاده در Query scope نیستند و باید نسخه/مستندات بررسی شود.

مطالعه آموزش کامل QUERYTRACEON با مثال‌های عملی و نکات Performance

DISABLE_OPTIMIZED_PLAN_FORCING در SQL Server

غیرفعال‌کردن Optimized Plan Forcing برای Query. کاربرد شاخص: عیب‌یابی یا کنترل Recompile/Plan forcing در نسخه‌های جدیدی که این قابلیت فعال است. نکته احتیاطی: قابلیت وابسته به نسخه است و فقط در سناریوی Plan forcing مرتبط معنا دارد.

مطالعه آموزش کامل DISABLE_OPTIMIZED_PLAN_FORCING با مثال‌های عملی و نکات Performance

FAQ

آیا Query Hint جایگزین Index Tuning است؟

خیر. بسیاری از مشکلات با Index مناسب، Statistics سالم، بازنویسی Query و طراحی درست Schema حل می‌شوند. Hint زمانی مطرح می‌شود که پس از این بررسی‌ها هنوز رفتار Optimizer نیاز به کنترل هدفمند داشته باشد.

آیا می‌توان چند Hint را ترکیب کرد؟

در بسیاری موارد بله، اما سازگاری Syntax و تضاد Hintها باید بررسی شود. ترکیب زیاد Hintها فضای Optimizer را شدیداً محدود می‌کند و عیب‌یابی را سخت‌تر می‌سازد.

بهترین ابزار برای پایش اثر Hint چیست؟

Actual Execution Plan، Query Store، Extended Events و STATISTICS IO/TIME مکمل یکدیگر هستند. Query Store برای مقایسه روند زمانی و Regression بسیار مفید است.

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

• Query Hint چه تفاوتی با Table Hint دارد؟
• RECOMPILE چه اثری روی Plan Cache و Parameter Sniffing دارد؟
• چه زمانی MAXDOP در سطح Query از تنظیم Server/Database مهم‌تر می‌شود؟
• چرا اجبار Join Algorithm می‌تواند بعد از رشد داده خطرناک شود؟
• Memory Grant Hint چه ارتباطی با Spill و Concurrency دارد؟

جمع‌بندی

Query Hintها ابزارهای پیشرفته‌ای برای کنترل Optimizer هستند. استفاده موفق از آن‌ها نیازمند Baseline، Execution Plan، Metricهای Runtime، آزمون روی چند پارامتر و بازبینی دوره‌ای است. در صفحات مستقل این مجموعه، هر Hint با Syntax، سناریوهای واقعی، خطاها و روش تصمیم‌گیری Production بررسی شده است.

برای مطالعه جزئیات، از فهرست آموزش‌های همین صفحه استفاده کنید؛ تمام لینک‌ها به مقاله‌های مستقل این مجموعه متصل هستند.