مقدمه

SET STATISTICS IO OFF یکی از دستورات تشخیصی مهم SQL Server برای توقف پیام‌های آماری I/O و بازگرداندن Session به خروجی معمول است. Syntax آن ساده است، اما تفسیر حرفه‌ای نتیجه به شناخت Session، Query Plan، Cache و Workload نیاز دارد.

STATISTICS IO الگوی دسترسی به صفحات داده و ایندکس را نشان می‌دهد. Logical Reads معمولاً شاخص مهمی برای مقایسه دو نسخه Query است زیرا تعداد صفحات 8KB لمس‌شده در Buffer Pool را منعکس می‌کند.

دستورات SET در SQL Server معمولاً در سطح Session اثر می‌گذارند؛ بنابراین همان Connection که فرمان تشخیصی را دریافت کرده باید Query مورد آزمایش را نیز اجرا کند. در ابزارهایی که Connection Pool دارند، این موضوع اهمیت بیشتری پیدا می‌کند چون اتصال بعدی الزاماً همان Session قبلی نیست.

برای مشاهده جایگاه این فرمان در کنار سایر گزینه‌ها، راهنمای جامع Statistics Execution Commands در SQL Server را مطالعه کنید.

تعریف، Syntax، پارامترها و نوع خروجی

این فرمان وضعیت گزینه STATISTICS IO را در Connection جاری روی OFF قرار می‌دهد. حالت انتخاب‌شده تا زمانی که Session برقرار است یا با فرمان دیگری تغییر کند، بر رفتار تشخیصی همان Session اثر می‌گذارد.

Syntax

SET STATISTICS IO OFF;
GO
SELECT DB_NAME() AS CurrentDatabase;

پارامترها

• STATISTICS IO: خانواده اطلاعات تشخیصی.
• OFF: وضعیت انتخاب‌شده برای Session جاری.
• فرمان Return Value تابعی ندارد و اثر آن Session-Level است.
• فرمان و Query مورد آزمایش باید در همان Connection اجرا شوند.

نوع خروجی

Messages شامل Scan count، logical reads، physical reads، read-ahead reads و در برخی سناریوها شمارنده‌های LOB است.

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

مثال 1: مثال پایه با Query ساده

هدف، کنترل وضعیت فرمان در همان Session پیش از اجرای Query است.

SET STATISTICS IO OFF;
SELECT TOP (10) object_id, name FROM sys.objects ORDER BY object_id;

نکته کاربردی مثال 1: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 2: مثال روی داده شبیه جدول واقعی

Catalog Viewها نمونه قابل اجرا بدون وابستگی به Schema اختصاصی می‌سازند.

SET STATISTICS IO OFF;
SELECT t.name, SUM(p.rows) AS RowCount FROM sys.tables AS t JOIN sys.partitions AS p ON p.object_id=t.object_id AND p.index_id IN (0,1) GROUP BY t.name ORDER BY RowCount DESC;

نکته کاربردی مثال 2: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 3: استفاده در SELECT پارامتری

متغیر، مثال را به الگوی Queryهای پارامتری نزدیک می‌کند.

SET STATISTICS IO OFF;
DECLARE @ObjectName sysname=N'tblNewsContent'; SELECT OBJECT_ID(@ObjectName) AS ObjectID,@ObjectName AS ObjectName;

نکته کاربردی مثال 3: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 4: کاربرد همراه WHERE

فیلتر زمانی برای سناریوهای گزارش‌گیری و توجه به SARGability مناسب است.

SET STATISTICS IO OFF;
SELECT name,create_date FROM sys.objects WHERE create_date>=DATEADD(DAY,-30,SYSDATETIME()) ORDER BY create_date DESC;

نکته کاربردی مثال 4: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

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

Aggregate می‌تواند CPU و Memory Grant بیشتری ایجاد کند و باید در Context تحلیل شود.

SET STATISTICS IO OFF;
SELECT DB_NAME() AS DatabaseName,COUNT_BIG(*) AS ObjectCount FROM sys.objects HAVING COUNT_BIG(*)>0;

نکته کاربردی مثال 5: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 6: رفتار در سناریوی NULL

NULL نباید با خطای تشخیصی اشتباه گرفته شود و منطق داده باید جداگانه بررسی گردد.

SET STATISTICS IO OFF;
DECLARE @Name sysname=NULL; SELECT COALESCE(@Name,N'بدون نام') AS SafeName;

نکته کاربردی مثال 6: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 7: حالت مرزی و داده غیرمعمول

Boundary Caseها برای کشف رفتارهای متفاوت Plan و تبدیل نوع مهم هستند.

SET STATISTICS IO OFF;
SELECT TOP (1) CAST(9223372036854775807 AS bigint) AS BoundaryValue,SYSDATETIME() AS CapturedAt;

نکته کاربردی مثال 7: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 8: سناریوی واقعی تحلیل ایندکس

بررسی ایندکس یک سناریوی نزدیک به نگهداری واقعی سامانه است.

SET STATISTICS IO OFF;
SELECT i.name,i.type_desc,i.is_disabled FROM sys.indexes AS i WHERE i.object_id=OBJECT_ID(N'dbo.tblNewsContent') ORDER BY i.index_id;

نکته کاربردی مثال 8: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 9: روش اشتباه و نسخه اصلاح‌شده

روشن‌گذاشتن ابزار تشخیصی یا مقایسه بدون Baseline روش مناسبی نیست.

-- روش کنترل‌نشده
SET STATISTICS IO ON;
SELECT COUNT_BIG(*) FROM sys.objects;
-- وضعیت مورد نظر
SET STATISTICS IO OFF;
SELECT COUNT_BIG(*) FROM sys.objects;

نکته کاربردی مثال 9: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

مثال 10: مثال Performance با DMV

DMV تصویر تجمعی می‌دهد و می‌تواند تست Session را به رفتار واقعی Workload مرتبط کند.

SET STATISTICS IO OFF;
SELECT TOP (20) qs.execution_count,qs.total_worker_time,qs.total_logical_reads,SUBSTRING(st.text,1,200) AS QueryText FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_worker_time DESC;

نکته کاربردی مثال 10: نتیجه SET STATISTICS IO OFF را نسبی و در مقایسه با Baseline تفسیر کنید. تغییر هم‌زمان Cache، Plan، داده یا بار سیستم می‌تواند مقایسه را مخدوش کند.

خطاهای رایج

اجرای SET در Session متفاوت از Query، یکی از خطاهای کلاسیک است. در SSMS هر پنجره و در برنامه هر Connection می‌تواند SPID مستقل داشته باشد. پیش از تحلیل، Context را کنترل کنید.

خطای دیگر، تکیه بر یک شاخص است. کاهش زمان یک اجرا بدون بررسی Reads، CPU، Plan و Waitها می‌تواند نتیجه گمراه‌کننده بدهد. یک تحلیل حرفه‌ای از چند منبع شواهد استفاده می‌کند.

باقی‌گذاشتن گزینه‌های تشخیصی در اسکریپت عملیاتی نیز می‌تواند خروجی اضافه یا سربار ایجاد کند. وضعیت پایان تست را صریحاً مشخص کنید.

Performance Considerations و Best Practices

یک Benchmark معتبر باید متن Query، پارامترها، حجم داده، وضعیت Cache، نسخه SQL Server و شرایط هم‌زمانی را ثبت کند. بدون این اطلاعات، اختلاف دو عدد ممکن است ناشی از شرایط محیط باشد نه تغییر واقعی در Query یا Index.

برای بهینه‌سازی حرفه‌ای، یک شاخص به‌تنهایی کافی نیست. Logical Reads، CPU، Elapsed Time، Execution Plan، Waitها، Memory Grant و تعداد اجرا باید در کنار الگوی واقعی Workload تحلیل شوند تا علت اصلی هزینه مشخص شود.

در Production ابزارهای تشخیصی را هدفمند و کوتاه‌مدت فعال کنید. خروجی بزرگ XML یا Profile می‌تواند روی Client، شبکه و حافظه اثر بگذارد و اندازه‌گیری بیش از حد حتی رفتار مسئله‌ای را که می‌خواهید بررسی کنید تغییر دهد.

• Baseline قبل از تغییر ثبت شود.
• پارامترها و Plan مستند شوند.
• Warm Cache و Cold Cache مخلوط نشوند.
• چند اجرای قابل مقایسه انجام شود.
• پس از پایان تست وضعیت Session کنترل شود.
• تغییر Index یا Hint روی کل Workload ارزیابی شود.

کاهش زمان یک اجرای آزمایشگاهی همیشه به معنی بهبود واقعی نیست. Queryای که میلیون‌ها بار در روز اجرا می‌شود ممکن است با کاهش کوچک CPU ارزش بیشتری از گزارشی داشته باشد که روزی یک بار اجرا می‌شود؛ بنابراین هزینه تجمعی و KPI کسب‌وکار را نیز بسنجید.

پس از هر تغییر، اثر جانبی را بررسی کنید. ایندکس جدید می‌تواند SELECT را سریع‌تر کند اما هزینه INSERT و UPDATE را بالا ببرد؛ Hint ممکن است در توزیع دیگری از داده Plan ضعیفی ایجاد کند؛ و Recompile ممکن است هزینه Compilation را افزایش دهد.

کاربرد واقعی در پروژه

در ERP، سامانه مالی، فروشگاه و گزارش‌گیری، این فرمان می‌تواند بخشی از Runbook عیب‌یابی Query کند باشد. تیم ابتدا Query و پارامتر واقعی را بازتولید می‌کند، Baseline می‌گیرد، تغییر پیشنهادی را اعمال می‌کند و همان سناریو را دوباره می‌سنجد.

برای همکاری تیمی بهتر، Baseline و نتایج قبل و بعد را مستند کنید. نگهداری Query، پارامتر، Plan، Reads، CPU، Duration و نسخه Deploy باعث می‌شود تصمیم فنی قابل بازبینی و قابل تکرار باشد.

Query Store و Extended Events مکمل خوبی برای اندازه‌گیری‌های Session-Level هستند. SET STATISTICS برای آزمایش تعاملی و هدفمند عالی است، اما دید تاریخی و تجمعی Workload را باید از ابزارهای مناسب مانیتورینگ دریافت کرد.

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

SET STATISTICS IO OFF دقیقاً چه کاری انجام می‌دهد؟

این فرمان وضعیت STATISTICS IO را در Session جاری کنترل می‌کند و برای توقف پیام‌های آماری I/O و بازگرداندن Session به خروجی معمول به کار می‌رود.

بهترین روش شروع کار با SET STATISTICS IO OFF چیست؟

یک Query نماینده انتخاب کنید، Baseline بگیرید، فرمان را در همان Session اجرا کنید و نتیجه را همراه Plan و چند اجرای قابل مقایسه تحلیل کنید.

آیا SET STATISTICS IO OFF در پروژه‌های تجاری مفید است؟

بله، تشخیص مبتنی بر داده می‌تواند زمان عیب‌یابی و هزینه تغییرات اشتباه را کاهش دهد و تصمیم درباره Query، Index و منابع را دقیق‌تر کند.

چه زمانی در پروژه سازمانی از SET STATISTICS IO OFF استفاده کنیم؟

هنگام بررسی Query کند، Regression پس از Deploy، مقایسه دو نسخه Query یا ارزیابی اثر یک Index؛ در Production دامنه و مدت استفاده را محدود کنید.

تفاوت SET STATISTICS IO OFF با Actual Execution Plan چیست؟

Actual Plan ساختار اپراتورها و اطلاعات Runtime را نشان می‌دهد؛ این فرمان بسته به خانواده خود اطلاعات مکمل دیگری می‌دهد. بهترین تحلیل از ترکیب شواهد حاصل می‌شود.

آیا SET STATISTICS IO OFF برای خدمات مشاوره SQL Server مناسب است؟

بله، به‌عنوان بخشی از Runbook تشخیصی قابل تکرار که Baseline، شرایط اجرا و روش بازگرداندن تنظیمات را مشخص می‌کند.

خطای رایج در استفاده از SET STATISTICS IO OFF چیست؟

اجرای فرمان در Connection متفاوت، فراموش‌کردن وضعیت نهایی Session، مقایسه شرایط Cache متفاوت و نتیجه‌گیری از یک اجرای تصادفی از خطاهای متداول هستند.

آیا SET STATISTICS IO OFF روی Performance اثر دارد؟

هر ابزار اندازه‌گیری مقداری سربار دارد. شدت آن به نوع خروجی، اندازه Query و Client بستگی دارد؛ استفاده هدفمند و کوتاه‌مدت توصیه می‌شود.

Best Practice برای SET STATISTICS IO OFF چیست؟

Baseline ثبت کنید، یک متغیر را در هر آزمایش تغییر دهید، چند اجرا انجام دهید، Plan را ذخیره کنید و بعد از پایان تست وضعیت Session را کنترل نمایید.

SET STATISTICS IO OFF با کدام نسخه‌های SQL Server سازگار است؟

خانواده SET STATISTICS سال‌ها در SQL Server وجود دارد، اما جزئیات خروجی و Plan میان نسخه‌ها و Compatibility Levelها می‌تواند تغییر کند؛ مستندات نسخه نصب‌شده را نیز بررسی کنید.

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

• SET STATISTICS IO OFF در چه سطحی اثر می‌گذارد؟
• برای تحلیل STATISTICS IO چه شاخص‌هایی را کنار Plan بررسی می‌کنید؟
• چگونه Warm Cache و Cold Cache را در Benchmark تفکیک می‌کنید؟
• چرا اجرای سریع‌تر الزاماً به معنی Query بهتر برای Production نیست؟
• چه زمانی ابزار تشخیصی Session-Level می‌تواند برای Client مشکل ایجاد کند؟

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

• Session و Database صحیح کنترل شد.
• وضعیت SET STATISTICS IO OFF آگاهانه انتخاب شد.
• Baseline و پارامترها ثبت شدند.
• چند اجرای قابل مقایسه انجام شد.
• Execution Plan و شاخص‌های مکمل بررسی شدند.
• وضعیت نهایی Session کنترل شد.

جمع‌بندی

SET STATISTICS IO OFF از نظر Syntax ساده اما از نظر کاربرد تشخیصی مهم است. ارزش آن زمانی ایجاد می‌شود که در یک روش اندازه‌گیری کنترل‌شده و همراه Context واقعی Workload استفاده شود.

برای مقایسه با سایر دستورات، به مقاله مادر Statistics Execution Commands بازگردید.