آموزش جامع SET SHOWPLAN_ALL OFF در SQL Server
SET SHOWPLAN_ALL OFF یکی از موضوعات مهم در تحلیل Execution Plan است. خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها. این مقاله از تعریف و نحوه استفاده شروع میکند و سپس ده مثال مستقل، خروجی نمونه، نکات فنی، خطاهای رایج، Performance، Best Practice، FAQ و سؤالهای مصاحبه را پوشش میدهد.
برای نقشه کامل این مجموعه، راهنمای جامع Execution Plan در SQL Server را نیز ببینید.
تعریف و کاربرد
خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها؛ اجرای عادی Session ادامه پیدا میکند. ارزش این مفهوم زمانی مشخص میشود که آن را در Context کامل Query، Statistics، Indexها، پارامترها و Runtime بررسی کنیم.
Execution Plan نقشه تصمیمگیری Query Optimizer برای اجرای یک دستور SQL است. تحلیل حرفهای پلن فقط پیدا کردن اپراتور با درصد Cost بالا نیست؛ باید مسیر دسترسی به داده، تعداد ردیفهای تخمینی و واقعی، ترتیب Joinها، نیاز به Sort یا Hash، Memory Grant، Parallelism و الگوی Waitها را در کنار هدف تجاری Query بررسی کرد. یک پلن که برای حجم کم مناسب است ممکن است با رشد داده یا تغییر توزیع مقادیر رفتار دیگری نشان دهد.
در تحلیل Performance باید بین نشانه و علت تفاوت گذاشت. Index Scan همیشه بد نیست و Index Seek همیشه خوب نیست. اگر Query بخش بزرگی از جدول را نیاز داشته باشد Scan میتواند منطقیتر باشد. در مقابل، Seek کوچکی که هزاران بار در Nested Loops تکرار شود ممکن است Logical Reads زیادی ایجاد کند. بنابراین هر تصمیم باید با اندازهگیری و مقایسه قبل و بعد همراه باشد.
Cardinality Estimation یکی از پایههای تصمیم Optimizer است. وقتی تعداد ردیف اشتباه تخمین زده شود، Join Algorithm، Memory Grant، روش دسترسی و Parallelism نیز ممکن است نامناسب انتخاب شوند. اختلاف زیاد Estimated Rows و Actual Rows ارزش بررسی Statistics، دادههای Skewed، Parameter Sensitivity، Predicateهای پیچیده، تبدیل ضمنی و ساختار Query را دارد.
Syntax یا روش استفاده
SET SHOWPLAN_ALL OFF;
این دستور یا Query نمونه را در Session کنترلشده اجرا کنید. برخی قابلیتهای SHOWPLAN به مجوز لازم روی Database و اشیای مرجع نیاز دارند. در Production قبل از اجرای Query سنگین، ریسک اجرا را بسنجید و در صورت کافیبودن پلن تخمینی از اجرای غیرضروری خودداری کنید.
پارامترها و دامنه اثر
- موضوع اصلی: SET SHOWPLAN_ALL OFF.
- دامنه اثر و خروجی به نوع قابلیت و Session بستگی دارد.
- نسخه SQL Server و Compatibility Level میتوانند نتیجه را تغییر دهند.
- Statistics، Indexها، حجم داده و پارامترها باید در تحلیل ثبت شوند.
نوع خروجی
اجرای عادی Session ادامه پیدا میکند. این خروجی یک نشانه برای تحلیل است و باید با IO، CPU، Duration، Wait و هدف Query ترکیب شود.
مثالهای عملی
مثال ۱: خروج امن از SHOWPLAN
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SET SHOWPLAN_ALL ON;
GO
SELECT TOP (5) name,object_id FROM sys.objects ORDER BY object_id;
GO
SET SHOWPLAN_ALL OFF;
GO
SELECT DB_NAME();
GO
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 1 | اجرای عادی Session ادامه پیدا میکند. |
کاربرد واقعی این مثال ساختن یک فرضیه قابل اندازهگیری است؛ قبل و بعد از هر تغییر نتیجه را ثبت کنید.
مثال ۲: داده نمونه
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
DECLARE @T TABLE(Id int PRIMARY KEY,Category int,Amount decimal(12,2));
INSERT INTO @T VALUES(1,1,120),(2,1,180),(3,2,90),(4,3,450);
SELECT Category,SUM(Amount) TotalAmount FROM @T GROUP BY Category ORDER BY TotalAmount DESC;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 2 | سه گروه با Aggregate و Sort نمایش داده میشود. |
در محیط سازمانی از پارامترها و حجم داده نماینده workload واقعی استفاده کنید تا نتیجه قابل تعمیم باشد.
مثال ۳: SELECT
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SELECT TOP (20) object_id,name,type_desc FROM sys.objects ORDER BY object_id;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 3 | حداکثر 20 ردیف از sys.objects برگردانده میشود. |
اگر پلن متفاوت بود، Statistics، Index، Compatibility Level و Parameterها را قبل از نتیجهگیری بررسی کنید.
مثال ۴: WHERE
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SELECT object_id,name,type_desc FROM sys.objects WHERE object_id>0 AND type='U';
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 4 | فقط User Tableهای قابل مشاهده برگردانده میشوند. |
این مثال را بهعنوان Baseline نگه دارید و پس از تغییر Query یا Index دوباره اجرا کنید تا اثر تغییر اثبات شود.
مثال ۵: JOIN
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SELECT TOP (25) o.name ObjectName,c.name ColumnName FROM sys.objects o JOIN sys.columns c ON c.object_id=o.object_id WHERE o.type='U' ORDER BY o.name,c.column_id;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 5 | Join و Sort احتمالی در پلن قابل تحلیل است. |
کاربرد واقعی این مثال ساختن یک فرضیه قابل اندازهگیری است؛ قبل و بعد از هر تغییر نتیجه را ثبت کنید.
مثال ۶: NULL
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
DECLARE @Filter int=NULL; SELECT TOP (20) object_id,name,schema_id FROM sys.objects WHERE @Filter IS NULL OR schema_id=@Filter ORDER BY object_id;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 6 | با NULL بودن پارامتر، ردیفهای بیشتری عبور میکنند و تخمین میتواند متفاوت شود. |
در محیط سازمانی از پارامترها و حجم داده نماینده workload واقعی استفاده کنید تا نتیجه قابل تعمیم باشد.
مثال ۷: حالت مرزی
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SELECT 1 AS Value UNION ALL SELECT 2 UNION ALL SELECT 3;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 7 | سه ردیف ثابت بازگردانده میشود؛ Cost نسبی بالا لزوماً زمان زیاد نیست. |
اگر پلن متفاوت بود، Statistics، Index، Compatibility Level و Parameterها را قبل از نتیجهگیری بررسی کنید.
مثال ۸: گزارشگیری
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SELECT type_desc,COUNT_BIG(*) ObjectCount FROM sys.objects GROUP BY type_desc HAVING COUNT_BIG(*)>0 ORDER BY ObjectCount DESC;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 8 | Aggregate و Sort برای گزارش گروهی قابل مشاهده هستند. |
این مثال را بهعنوان Baseline نگه دارید و پس از تغییر Query یا Index دوباره اجرا کنید تا اثر تغییر اثبات شود.
مثال ۹: روش اشتباه و اصلاح
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
DECLARE @TargetDate date=CAST(GETDATE() AS date);
SELECT name,create_date FROM sys.objects WHERE CAST(create_date AS date)=@TargetDate;
SELECT name,create_date FROM sys.objects WHERE create_date>=@TargetDate AND create_date<DATEADD(day,1,@TargetDate);
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 9 | نسخه Range معمولاً SARGability بهتری نسبت به تابع روی ستون دارد. |
کاربرد واقعی این مثال ساختن یک فرضیه قابل اندازهگیری است؛ قبل و بعد از هر تغییر نتیجه را ثبت کنید.
مثال ۱۰: Performance
سناریوی این مثال برای بررسی مستقیم رفتار موضوع و ارتباط آن با تصمیم Optimizer طراحی شده است. Query کامل است و میتوان آن را در یک Database آزمایشی اجرا کرد.
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT TOP (100) o.object_id,o.name,c.column_id,c.name ColumnName FROM sys.objects o JOIN sys.columns c ON c.object_id=o.object_id ORDER BY o.object_id,c.column_id;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
| مورد | نتیجه نمونه یا انتظار |
|---|
| خروجی مثال 10 | Messages شامل IO و TIME است و باید کنار Execution Plan تحلیل شود. |
در محیط سازمانی از پارامترها و حجم داده نماینده workload واقعی استفاده کنید تا نتیجه قابل تعمیم باشد.
نکات فنی عمیق
برای عیبیابی قابل اعتماد، Query، پارامترها، پلن، زمان اجرا، Logical Reads، CPU و شرایط بار سیستم را بهعنوان Baseline ثبت کنید. تغییرات را یکییکی اعمال و نتیجه را دوباره اندازهگیری کنید. بهینهسازی بدون Baseline ممکن است یک Query را در تست سریعتر کند اما بهدلیل Write Overhead، Blocking، Memory Pressure یا افزایش اندازه Index عملکرد کل سامانه را بدتر کند.
نسخه SQL Server و Compatibility Level میتوانند شکل پلن را تغییر دهند. Cardinality Estimatorهای جدید، Adaptive Query Processing، Memory Grant Feedback و قابلیتهای Intelligent Query Processing رفتار Optimizer را توسعه دادهاند. هنگام مقایسه پلنها باید محیط، نسخه، Statistics و تنظیمات مهم ثبت شوند تا نتیجهگیری بر اساس Context واقعی انجام شود.
در Production نباید Queryهای آزمایشی سنگین را بدون کنترل اجرا کرد. برای برخی بررسیها Estimated Plan یا SHOWPLAN مناسبتر است و برای برخی دیگر Actual Plan ضروری است. Query Store، Extended Events و مانیتورینگ کنترلشده میتوانند شواهد مکمل فراهم کنند. هدف این است که خود فرآیند عیبیابی باعث اختلال جدید، Blocking یا مصرف ناخواسته منابع نشود.
خطاهای رایج
- تفسیر SET SHOWPLAN_ALL OFF بدون بررسی کل Execution Plan.
- اتکا به Cost Percentage بهجای IO، CPU و Duration.
- ساخت Index فقط بر اساس پیشنهاد بدون بررسی هزینه Write.
- نادیده گرفتن اختلاف Estimated Rows و Actual Rows.
- آزمایش با پارامتر یا داده غیرنماینده Production.
- فراموشکردن بازگرداندن تنظیم Session پس از بررسی SHOWPLAN در موارد مرتبط.
Performance Considerations
برای Performance ابتدا مشخص کنید Query CPU-bound، I/O-bound، Memory-bound یا Wait-bound است. Execution Plan بخشی از شواهد است. Logical Reads بالا، Spill به tempdb، Lookup تکرارشونده، Cardinality اشتباه، Blocking و Waitهای غالب باید در کنار شکل پلن بررسی شوند.
در Queryهای پرتکرار حتی بهبود کوچک میتواند اثر بزرگ روی ظرفیت سیستم داشته باشد، در حالی که بهینهسازی Query نادر ممکن است بازده تجاری کمی داشته باشد. اولویت را بر اساس مجموع مصرف منابع، SLA و تجربه کاربر تعیین کنید.
Best Practices
- قبل از تغییر Baseline شامل پلن، IO، CPU، Duration و پارامترها ثبت کنید.
- در هر مرحله فقط یک تغییر اصلی انجام دهید.
- پیشنهادهای Index را با Indexهای موجود ادغام و هزینه نگهداری را بررسی کنید.
- Statistics و Cardinality را جدی بگیرید.
- با workload نماینده Production تست کنید.
- پس از Deployment مانیتورینگ و امکان Rollback داشته باشید.
کاربرد واقعی در پروژه سازمانی
در پروژه سازمانی، SET SHOWPLAN_ALL OFF بخشی از چرخه تشخیص Queryهای پرهزینه است. تیم DBA یا توسعه Query را از Query Store یا مانیتورینگ پیدا میکند، پلن و Runtime Metrics را ثبت میکند، علت را با شواهد اثبات میکند و تغییر را در Test میسنجد. این روش برای مشاوره Performance Tuning نیز یک چارچوب قابل تکرار و قابل دفاع فراهم میکند.
سؤالات متداول
SET SHOWPLAN_ALL OFF دقیقاً چه چیزی را نشان میدهد؟
خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها؛ جزئیات نهایی به Context اجرای Query بستگی دارد.
آیا SET SHOWPLAN_ALL OFF برای مبتدیان مناسب است؟
بله؛ ابتدا اپراتورها، Estimated/Actual Rows و تفاوت پلن تخمینی و واقعی را یاد بگیرید و با Queryهای کوچک تمرین کنید.
SET SHOWPLAN_ALL OFF چه ارزش تجاری دارد؟
تشخیص دقیقتر علت کندی باعث کاهش زمان پاسخ و جلوگیری از هزینه تغییرات اشتباه میشود.
آیا در Performance Tuning باید SET SHOWPLAN_ALL OFF را بررسی کرد؟
بسته به مسئله بله؛ این موضوع باید همراه با IO، CPU، Duration و Waitها تحلیل شود.
SET SHOWPLAN_ALL OFF با سایر شاخصهای پلن چه تفاوتی دارد؟
هر شاخص زاویه متفاوتی از تصمیم Optimizer یا Runtime را نشان میدهد و باید بر اساس سؤال Performance انتخاب شود.
برای خدمات بررسی SET SHOWPLAN_ALL OFF چه اطلاعاتی لازم است؟
Query، پارامتر، پلن، نسخه SQL Server، Compatibility Level، Indexها، حجم داده و معیارهای اجرا مفید هستند.
رایجترین خطا در تحلیل SET SHOWPLAN_ALL OFF چیست؟
نتیجهگیری از یک مقدار یا Screenshot بدون Context و Baseline.
SET SHOWPLAN_ALL OFF چه اثری بر Performance دارد؟
اطلاعات آن میتواند علت مصرف CPU، IO، Memory یا تأخیر را آشکار کند و مسیر بهینهسازی را دقیقتر سازد.
Best Practice برای SET SHOWPLAN_ALL OFF چیست؟
اندازهگیری قبل و بعد، workload نماینده، مستندسازی Context و تغییر مرحلهای.
آیا SET SHOWPLAN_ALL OFF در همه نسخهها یکسان است؟
مفهوم اصلی پایدار است اما Metadata، Optimizer، Cardinality Estimator و Intelligent Query Processing بین نسخهها تغییر میکنند.
سؤالات مصاحبه
- SET SHOWPLAN_ALL OFF چیست و چه زمانی استفاده میشود؟
- برای تفسیر SET SHOWPLAN_ALL OFF چه متریکهای دیگری را بررسی میکنید؟
- اگر Estimated و Actual Rows اختلاف شدید داشته باشند چه میکنید؟
- چرا Cost Percentage بهتنهایی کافی نیست؟
- اثر Index جدید را چگونه Benchmark میکنید؟
- Estimated Plan و Actual Plan در تشخیص مشکل چه تفاوتی دارند؟
چکلیست نهایی
- Query و پارامتر واقعی ثبت شده است.
- پلن مناسب بر اساس ریسک اجرا انتخاب شده است.
- Estimated و Actual Rows بررسی شدهاند.
- Warning، Memory Grant، Parallelism و Waitها در صورت ارتباط بررسی شدهاند.
- پیشنهاد Index با ساختار موجود مقایسه شده است.
- نتیجه با Baseline تأیید شده است.
جمعبندی
SET SHOWPLAN_ALL OFF زمانی بیشترین ارزش را دارد که در یک فرآیند اندازهگیریشده استفاده شود. خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها. ترکیب این اطلاعات با Statistics، Index Design، Runtime Metrics و Wait Analysis تصمیمهای دقیقتری برای بهینهسازی SQL Server ایجاد میکند.
برای ادامه مطالعه به مقاله مادر دستورات و مفاهیم Execution Plan بازگردید.