راهنمای جامع دستورات آماری اجرای Query در SQL Server؛ Statistics Execution Commands
مقدمه
Statistics Execution Commands مجموعهای از تنظیمات Session-Level برای مشاهده هزینه I/O، زمان CPU و Elapsed، Execution Plan XML و اطلاعات Profile اجرایی هستند. این ابزارها به DBA و توسعهدهنده کمک میکنند عملکرد Query را بر اساس شواهد واقعی تحلیل کنند.
چهار خانواده اصلی IO، TIME، XML و PROFILE هر کدام زاویه متفاوتی از اجرا را نشان میدهند. روشنکردن ابزار فقط نیمی از کار است؛ خاموشکردن آگاهانه آن نیز برای کنترل خروجی، کاهش سربار و جلوگیری از اثر ناخواسته روی Client اهمیت دارد.
دسترسی سریع
مدل ذهنی درست برای تحلیل Performance
Query کند میتواند حاصل I/O زیاد، CPU بالا، Lock، Memory Grant، Cardinality Estimate نامناسب، Parallelism یا شبکه باشد. بنابراین هیچیک از دستورات Statistics بهتنهایی پاسخ کامل نیست و باید در کنار Execution Plan، Waitها و Context Workload تفسیر شود.
دستورات SET در SQL Server معمولاً در سطح Session اثر میگذارند؛ بنابراین همان Connection که فرمان تشخیصی را دریافت کرده باید Query مورد آزمایش را نیز اجرا کند. در ابزارهایی که Connection Pool دارند، این موضوع اهمیت بیشتری پیدا میکند چون اتصال بعدی الزاماً همان Session قبلی نیست.
یک Benchmark معتبر باید متن Query، پارامترها، حجم داده، وضعیت Cache، نسخه SQL Server و شرایط همزمانی را ثبت کند. بدون این اطلاعات، اختلاف دو عدد ممکن است ناشی از شرایط محیط باشد نه تغییر واقعی در Query یا Index.
برای بهینهسازی حرفهای، یک شاخص بهتنهایی کافی نیست. Logical Reads، CPU، Elapsed Time، Execution Plan، Waitها، Memory Grant و تعداد اجرا باید در کنار الگوی واقعی Workload تحلیل شوند تا علت اصلی هزینه مشخص شود.
در Production ابزارهای تشخیصی را هدفمند و کوتاهمدت فعال کنید. خروجی بزرگ XML یا Profile میتواند روی Client، شبکه و حافظه اثر بگذارد و اندازهگیری بیش از حد حتی رفتار مسئلهای را که میخواهید بررسی کنید تغییر دهد.
کاهش زمان یک اجرای آزمایشگاهی همیشه به معنی بهبود واقعی نیست. Queryای که میلیونها بار در روز اجرا میشود ممکن است با کاهش کوچک CPU ارزش بیشتری از گزارشی داشته باشد که روزی یک بار اجرا میشود؛ بنابراین هزینه تجمعی و KPI کسبوکار را نیز بسنجید.
پس از هر تغییر، اثر جانبی را بررسی کنید. ایندکس جدید میتواند SELECT را سریعتر کند اما هزینه INSERT و UPDATE را بالا ببرد؛ Hint ممکن است در توزیع دیگری از داده Plan ضعیفی ایجاد کند؛ و Recompile ممکن است هزینه Compilation را افزایش دهد.
برای همکاری تیمی بهتر، Baseline و نتایج قبل و بعد را مستند کنید. نگهداری Query، پارامتر، Plan، Reads، CPU، Duration و نسخه Deploy باعث میشود تصمیم فنی قابل بازبینی و قابل تکرار باشد.
Query Store و Extended Events مکمل خوبی برای اندازهگیریهای Session-Level هستند. SET STATISTICS برای آزمایش تعاملی و هدفمند عالی است، اما دید تاریخی و تجمعی Workload را باید از ابزارهای مناسب مانیتورینگ دریافت کرد.
معرفی و دستهبندی دستورات
SET STATISTICS IO ON
SET STATISTICS IO ON برای نمایش Scan Count، Logical Reads، Physical Reads و Read-Ahead برای تحلیل هزینه دسترسی به صفحات استفاده میشود. حالت ON مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS IO ON با مثالهای عملی
SET STATISTICS IO OFF
SET STATISTICS IO OFF برای توقف پیامهای آماری I/O و بازگرداندن Session به خروجی معمول استفاده میشود. حالت OFF مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS IO OFF با مثالهای عملی
SET STATISTICS TIME ON
SET STATISTICS TIME ON برای نمایش CPU Time و Elapsed Time برای تحلیل هزینه پردازش و انتظار استفاده میشود. حالت ON مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS TIME ON با مثالهای عملی
SET STATISTICS TIME OFF
SET STATISTICS TIME OFF برای توقف پیامهای CPU و زمان سپریشده پس از پایان Benchmark استفاده میشود. حالت OFF مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS TIME OFF با مثالهای عملی
SET STATISTICS XML ON
SET STATISTICS XML ON برای دریافت Actual Execution Plan در قالب Showplan XML برای تحلیل اپراتورها و Runtime استفاده میشود. حالت ON مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS XML ON با مثالهای عملی
SET STATISTICS XML OFF
SET STATISTICS XML OFF برای توقف تولید Plan XML و کاهش خروجی تشخیصی غیرضروری استفاده میشود. حالت OFF مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS XML OFF با مثالهای عملی
SET STATISTICS PROFILE ON
SET STATISTICS PROFILE ON برای دریافت Result Set اضافی شامل اپراتورها، Rows و Executes برای تحلیل Runtime استفاده میشود. حالت ON مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS PROFILE ON با مثالهای عملی
SET STATISTICS PROFILE OFF
SET STATISTICS PROFILE OFF برای توقف Result Set پروفایل و بازگرداندن خروجی Query به حالت معمول استفاده میشود. حالت OFF مشخص میکند این خروجی تشخیصی در Session جاری فعال یا غیرفعال باشد. نتیجه را همراه Plan و شرایط واقعی Workload بررسی کنید.
آموزش کامل SET STATISTICS PROFILE OFF با مثالهای عملی
روش استاندارد Benchmark
ابتدا مسئله را دقیق تعریف کنید: Query، Endpoint، Report، بازه وقوع مشکل و KPI فنی. سپس Baseline شامل Reads، CPU، Duration، Plan و پارامترها را ثبت کنید. فقط یک متغیر را تغییر دهید و همان سناریو را چند بار در شرایط مشابه اجرا کنید.
Warm Cache و Cold Cache را مخلوط نکنید. در Production برای شبیهسازی Cold Cache بدون ارزیابی ریسک Cache کل سرور را پاک نکنید. اختلاف Estimated و Actual Rows، Spill، Lookup، Scan، Sort و Hash را در Plan بررسی کنید و تغییر را روی کل Workload اعتبارسنجی نمایید.
شش مثال ترکیبی کاربردی
مثال 1: مقایسه I/O
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS IO ON;
SELECT TOP (100) * FROM sys.objects ORDER BY object_id;
SET STATISTICS IO OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | شمارندههای I/O در Messages ثبت میشوند. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
مثال 2: اندازهگیری CPU و زمان
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
SET STATISTICS TIME OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | CPU و Elapsed در Messages گزارش میشوند. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
مثال 3: Actual Plan XML
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS XML ON;
SELECT TOP (10) name,object_id FROM sys.objects ORDER BY object_id;
SET STATISTICS XML OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | Showplan XML تولید میشود. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
مثال 4: Profile کنترلشده
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS PROFILE ON;
SELECT TOP (10) name FROM sys.objects ORDER BY name;
SET STATISTICS PROFILE OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | Result Set پروفایل اضافه ظاهر میشود. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
مثال 5: ترکیب IO و TIME
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT type_desc,COUNT_BIG(*) AS Cnt FROM sys.objects GROUP BY type_desc;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | I/O و زمان همزمان قابل مقایسهاند. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
مثال 6: الگوی پایان امن
این مثال یک الگوی مستقل و قابل اجرا برای اندازهگیری هدفمند ارائه میکند. خروجی دقیق به نسخه، داده، Cache و شرایط سرور وابسته است.
SET STATISTICS IO ON;
SELECT DB_NAME(),COUNT_BIG(*) FROM sys.objects;
SET STATISTICS IO OFF;
| بخش | نتیجه نمونه |
|---|
| خروجی | پس از تست گزینه تشخیصی صریحاً خاموش میشود. |
| نکته | نتیجه را با Baseline و Execution Plan مقایسه کنید. |
هدف، ایجاد فرآیند تکرارپذیر است: فعالسازی، اجرای Query، ثبت نتیجه و بازگرداندن وضعیت Session.
تفسیر IO، TIME، XML و PROFILE
در IO، Logical Reads تعداد صفحات 8KB لمسشده در Buffer Pool را نشان میدهد. Physical Reads به Storage وابسته است و در اجرای گرم ممکن است بسیار کم شود. Scan Count را بدون توجه به حجم داده و Plan خوب یا بد تلقی نکنید.
در TIME، CPU Time مصرف پردازنده و Elapsed Time زمان دیواری است. در Query موازی مجموع CPU میتواند از Elapsed بیشتر باشد؛ در مقابل Elapsed بسیار بیشتر از CPU میتواند نشانه انتظار باشد.
XML برای تحلیل عمیق Actual Plan مناسب است و اختلاف Estimated و Actual Rows، Warning، Predicate و جزئیات اپراتورها را آشکار میکند. PROFILE خروجی جدولی اضافه میدهد و باید اثر آن روی Client را در نظر گرفت.
خطاهای رایج و Best Practices
- اجرای SET در Session متفاوت از Query.
- مقایسه Warm و Cold Cache بدون کنترل شرایط.
- تکیه بر یک شاخص و نادیده گرفتن Plan و Wait.
- روشن ماندن XML یا PROFILE در مسیر برنامه.
- ساخت Index بر اساس یک Query بدون بررسی DML و Workload.
- نتیجهگیری از یک اجرای تصادفی.
یک Runbook استاندارد برای جمعآوری Baseline، ذخیره Plan، نامگذاری نتایج و بازگرداندن تنظیمات ایجاد کنید. برای Queryهای بحرانی، تاریخچه Performance را کنار نسخه Deploy و تغییرات Schema نگهداری کنید.
سؤالات متداول
Statistics Execution Commands چیست؟
مجموعه دستورات SET برای کنترل خروجیهای تشخیصی I/O، زمان، XML Plan و Profile است.
برای شروع تحلیل Query کند چه کنم؟
اغلب IO و TIME همراه Actual Plan نقطه شروع خوبی است، اما انتخاب ابزار به نوع مسئله بستگی دارد.
آیا برای پروژه تجاری ارزش دارد؟
بله، تشخیص مبتنی بر داده ریسک تغییر و هزینه عیبیابی را کاهش میدهد.
در مشاوره SQL Server چگونه استفاده میشود؟
برای Baseline و مقایسه قبل و بعد از اصلاح Query یا Index در سناریوی قابل تکرار.
تفاوت IO و TIME چیست؟
IO روی دسترسی صفحات و TIME روی CPU و Elapsed تمرکز دارد.
آیا در Production قابل استفاده است؟
بله، اما هدفمند، کوتاهمدت و با ارزیابی سربار.
خطای رایج چیست؟
Connection متفاوت یا Benchmark غیرقابل تکرار.
کدام گزینه میتواند خروجی سنگینتری بدهد؟
XML و PROFILE در Queryهای بزرگ ممکن است خروجی و سربار بیشتری ایجاد کنند.
Best Practice چیست؟
Baseline، چند اجرای قابل مقایسه، ثبت Context و بازگرداندن تنظیمات.
آیا بین نسخهها تفاوت هست؟
Syntax اصلی پایدار است اما جزئیات Plan و خروجی با نسخه و Compatibility Level تغییر میکند.
سؤالات مصاحبه
- Logical Read و Physical Read چه تفاوتی دارند؟
- چرا CPU ممکن است از Elapsed بیشتر باشد؟
- Actual Plan XML چه اطلاعاتی میدهد؟
- Session-Level بودن SET STATISTICS چرا مهم است؟
- چگونه Benchmark منصفانه طراحی میکنید؟
- چه زمانی Scan از Seek بهتر است؟
- چرا Index پیشنهادی را نباید بدون بررسی Workload ساخت؟
جمعبندی
Statistics Execution Commands ابزارهای پایه اما قدرتمند تحلیل Performance هستند. متخصص حرفهای IO، زمان، Plan، Wait، حجم داده و الگوی Workload را کنار هم میگذارد و تغییر را با Baseline قابل تکرار اعتبارسنجی میکند.