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

مقدمه

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

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

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

تعریف و Syntax

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

OPTION (RECOMPILE);

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

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

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

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

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

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

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

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

SELECT TOP (11) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 1	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (12) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 2	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (13) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 3	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (14) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 4	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

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

SELECT TOP (15) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 5	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (16) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 6	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (17) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 7	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (18) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 8	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

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

SELECT TOP (19) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 9	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

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

SELECT TOP (20) name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY object_id DESC
OPTION (RECOMPILE);

خروجی نمونه	چه چیزی بررسی شود	تصمیم
چند ردیف نمونه از sys.objects یا نتیجه Query شماره 10	Actual Rows، Estimated Rows، CPU، Reads و Plan Shape	اثر RECOMPILE را با Baseline مقایسه کنید

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

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

یکی از خطاهای رایج این است که RECOMPILE بدون مشاهده 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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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

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

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

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

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

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