آموزش NO_PERFORMANCE_SPOOL در SQL Server؛ کاربرد، مثال و نکات Performance
مقدمه
NO_PERFORMANCE_SPOOL یکی از ابزارهای کنترل رفتار Query Optimizer در Microsoft SQL Server است. هدف اصلی آن جلوگیری از افزودن بعضی Spoolهای صرفاً Performance است. استفاده حرفهای از Hint زمانی ارزشمند است که ابتدا Query، Statistics، Indexها، Cardinality Estimation و Execution Plan اندازهگیری شده باشند؛ Hint نباید جایگزین طراحی صحیح Schema یا رفع علت ریشهای مشکل شود.
در سناریوی واقعی، عیبیابی Spoolهای پرهزینه یا مصرف زیاد tempdb در شرایط خاص. با این حال باید بدانیم که Spool ممکن است برای جلوگیری از تکرار کار مفید باشد؛ حذف آن همیشه بهتر نیست. بنابراین قبل و بعد از اعمال NO_PERFORMANCE_SPOOL لازم است زمان CPU، Logical Reads، Duration، Memory Grant، Spill، تعداد Recompile و پایداری Plan مقایسه شود. این مقاله از مثالهای ساده شروع میکند و سپس به تصمیمگیری Production، خطاهای رایج و روش آزمون امن میرسد.
راهنمای جامع همه Query Hintهای این مجموعه در مقاله مادر Query Hints در SQL Server قرار دارد و برای مقایسه این Hint با گزینههای دیگر میتوانید به آن برگردید.
تعریف و Syntax
NO_PERFORMANCE_SPOOL به Optimizer یا موتور اجرای SQL Server یک محدودیت، ترجیح یا رفتار خاص را اعلام میکند. Syntax مرجع این مقاله به شکل زیر است؛ جزئیات دقیق Scope به نوع Hint وابسته است و بعضی گزینهها فقط در OPTION، برخی در Database Setting یا Plan Guide معنا دارند.
OPTION (NO_PERFORMANCE_SPOOL);
| مولفه | توضیح | نکته عملی |
|---|
| NO_PERFORMANCE_SPOOL | جلوگیری از افزودن بعضی Spoolهای صرفاً Performance | فقط پس از اندازهگیری اعمال شود |
| Scope | Query یا Scope مخصوص همان قابلیت | عیبیابی Spoolهای پرهزینه یا مصرف زیاد tempdb در شرایط خاص |
| ریسک اصلی | Spool ممکن است برای جلوگیری از تکرار کار مفید باشد؛ حذف آن همیشه بهتر نیست. | Rollback plan داشته باشید |
تصویر اول ارتباط NO_PERFORMANCE_SPOOL با Query Optimizer، Plan، برآوردها و منابع Runtime را نشان میدهد. نقطه تصمیم اصلی این است که Hint باید بر مبنای مشاهده Plan و Metricها انتخاب شود، نه صرفاً برای حذف یک علامت هشدار.
پارامترها، خروجی و اثر روی Plan
خروجی مستقیم NO_PERFORMANCE_SPOOL یک مقدار Scalar نیست؛ اثر آن در شکل Execution Plan، انتخاب Operator، نحوه Compilation یا مصرف منابع دیده میشود. برای ارزیابی صحیح باید Actual Execution Plan و آمار اجرای Query را کنار هم قرار دهید. در Queryهای پارامتری، تفاوت مقادیر ورودی و Skew داده اهمیت ویژه دارد.
- Syntax پایه: OPTION (NO_PERFORMANCE_SPOOL)
- قبل از 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 1 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 1: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 2 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 2: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 3 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 3: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 4 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 4: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش میدهد یا فقط یک اجرای منفرد را سریعتر نشان میدهد.
تصویر دوم جریان اجرای Query دارای NO_PERFORMANCE_SPOOL را از مرحله 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 5 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 5: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 6 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 6: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 7 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 7: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 8 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 8: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش میدهد یا فقط یک اجرای منفرد را سریعتر نشان میدهد.
تصویر سوم یک تصمیم Production را برای NO_PERFORMANCE_SPOOL نمایش میدهد: ابتدا 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 9 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 9: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق 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 (NO_PERFORMANCE_SPOOL);
| خروجی نمونه | چه چیزی بررسی شود | تصمیم |
|---|
| چند ردیف نمونه از sys.objects یا نتیجه Query شماره 10 | Actual Rows، Estimated Rows، CPU، Reads و Plan Shape | اثر NO_PERFORMANCE_SPOOL را با Baseline مقایسه کنید |
نکته کاربردی مثال 10: در مورد NO_PERFORMANCE_SPOOL صرف اجرای موفق Query کافی نیست. هدف این است که بفهمیم آیا Hint در همین الگوی داده، همین پارامتر و همین فشار همزمانی واقعاً هزینه کل سیستم را کاهش میدهد یا فقط یک اجرای منفرد را سریعتر نشان میدهد.
خطاهای رایج و روش عیبیابی
یکی از خطاهای رایج این است که NO_PERFORMANCE_SPOOL بدون مشاهده 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
بهترین روش برای NO_PERFORMANCE_SPOOL این است که آن را یک ابزار جراحی بدانیم، نه تنظیم پیشفرض. ابتدا Index و Statistics و Query Design را اصلاح کنید؛ سپس اگر هنوز محدودیت Optimizer یا نوسان Plan باقی بود Hint را با شواهد اعمال کنید. برای Queryهای پرتکرار اثر کوچک روی CPU یا Reads میتواند در مقیاس کل سیستم بسیار مهم باشد.
در تست A/B، فقط Duration را نبینید. ممکن است NO_PERFORMANCE_SPOOL زمان Query را کم کند ولی Memory Grant، Thread مصرفی یا فشار tempdb را بالا ببرد. معیار درست، هزینه کل workload و پایداری در ساعات اوج است. همچنین پس از ارتقای SQL Server، تغییر Cardinality Estimator یا افزودن Index جدید، Hintهای قدیمی باید دوباره بازبینی شوند.
سؤالات متداول
آیا NO_PERFORMANCE_SPOOL همیشه Performance را بهتر میکند؟
خیر. Hint فضای تصمیم Optimizer را تغییر میدهد و فقط در سناریویی که علت مشکل دقیقاً شناخته شده باشد میتواند مفید باشد. استفاده کورکورانه ممکن است Regression ایجاد کند.
چطور اثر Hint را اندازهگیری کنیم؟
Actual Execution Plan، Query Store، Duration، CPU time، Logical Reads، Memory Grant، Spill و تعداد اجرا را قبل و بعد مقایسه کنید و چند مقدار پارامتر متفاوت را آزمایش کنید.
آیا بعد از رفع مشکل میتوان Hint را حذف کرد؟
بله و حتی باید دورهای نیاز آن را بازبینی کنید. تغییر Statistics، Index، نسخه SQL Server یا توزیع داده میتواند علت اولیه را از بین ببرد.
سؤالات مصاحبه
- NO_PERFORMANCE_SPOOL چه مشکلی را حل میکند و چه زمانی نباید از آن استفاده کرد؟
- چطور برای NO_PERFORMANCE_SPOOL یک Baseline قابل اعتماد میسازید؟
- اگر Plan با Hint سریعتر ولی CPU کل بیشتر شود چه تصمیمی میگیرید؟
- Query Store چگونه در ارزیابی یا جایگزینی Hint کمک میکند؟
چکلیست نهایی
- علت ریشهای مشکل مشخص شده است.
- Plan و Runtime Metrics قبل از تغییر ذخیره شدهاند.
- حداقل چند مقدار پارامتر و حجم داده تست شده است.
- اثر روی Concurrent workload و tempdb بررسی شده است.
- Rollback و بازبینی دورهای Hint تعریف شده است.
جمعبندی
NO_PERFORMANCE_SPOOL میتواند برای جلوگیری از افزودن بعضی Spoolهای صرفاً Performance ابزار قدرتمندی باشد، اما ارزش واقعی آن زمانی مشخص میشود که با اندازهگیری و شناخت Optimizer همراه باشد. اصل حرفهای این است: ابتدا علت را پیدا کنید، سپس کوچکترین مداخله لازم را انجام دهید و نتیجه را با داده واقعی بسنجید.
برای مقایسه این قابلیت با سایر گزینهها به راهنمای جامع Query Hints در SQL Server مراجعه کنید.
خدمات برنامهنویسی و پایگاه داده
قبول سفارشهای برنامهنویسی و پایگاه داده در مجموعه برنامهنویسی در اصفهان با شماره سفارش 09131253620. مجموعهای معتبر با سابقه فعالیت حرفهای از سال ۱۳۷۵ شمسی تاکنون؛ انجام پروژه، آموزش برنامهنویسی و آموزش پایگاه داده SQL Server.
برای سفارش پروژههای برنامهنویسی و پایگاه داده، سیستمهای تحت وب، وبسایت و راهکارهای نرمافزاری جدید با شماره 09131253620 تماس حاصل فرمایید. ایتا، واتساپ و تماس مستقیم: +989131253620 — تماس با ما