راهنمای جامع توابع قدیمی SQL Server؛ fn_virtualfilestats و مسیر مهاجرت

در SQL Server بسیاری از رابط‌های مدیریتی در طول سال‌ها تکامل یافته‌اند. بعضی توابع قدیمی هنوز برای سازگاری با اسکریپت‌های گذشته وجود دارند، اما برای توسعه جدید معمولاً یک DMV یا DMF مدرن‌تر در دسترس است. شناخت این توابع برای محیط‌های سازمانی مهم است، زیرا Jobها، Stored Procedureها و ابزارهای مانیتورینگ ممکن است سال‌ها بدون تغییر به آن‌ها وابسته مانده باشند.

این مجموعه با عنوان Legacy Function روی همین موضوع تمرکز دارد و نخستین مورد آن fn_virtualfilestats است؛ تابعی سیستمی برای مشاهده آمار ورودی و خروجی فایل‌های Data و Log. رابط مدرن sys.dm_io_virtual_file_stats همان حوزه اطلاعاتی را با ساختاری مناسب‌تر برای کدهای جدید ارائه می‌کند. هدف این مقاله فقط معرفی Syntax نیست، بلکه توضیح روش تحلیل Counterهای تجمعی، محاسبه Latency و مهاجرت امن کدهای قدیمی است.

در بررسی Legacy باید میان «قدیمی»، «Deprecated» و «حذف‌شده» تفاوت گذاشت. قدیمی بودن یک قابلیت به‌تنهایی دلیل حذف فوری آن نیست. تصمیم درست باید بر اساس مستندات نسخه، Dependencyهای واقعی، هزینه مهاجرت و ریسک عملیاتی گرفته شود. در مورد fn_virtualfilestats، ارزش اصلی آن در شناخت کدهای موجود و فهم مدل آمار I/O است، در حالی که برای طراحی تازه معمولاً DMF جدید ترجیح دارد.

دسترسی سریع

• آموزش کامل fn_virtualfilestats با ۱۰ مثال عملی
• شناخت Counterهای تجمعی I/O و تفاوت آن‌ها با نرخ لحظه‌ای
• محاسبه Read Latency و Write Latency
• اتصال آمار فایل به sys.master_files
• روش مهاجرت مرحله‌ای به sys.dm_io_virtual_file_stats
• نکات Performance، Baseline و مانیتورینگ دوره‌ای

موضوع این مجموعه

چرا آمار فایل برای Performance مهم است

هر Query در نهایت با صفحات داده، Buffer Pool، فایل‌های Data و فایل Log ارتباط دارد. اگر Storage کند باشد، اگر یک فایل روی Volume نامناسب قرار گیرد یا اگر بار به‌صورت نامتوازن روی فایل‌ها توزیع شود، اثر آن در زمان انتظار خواندن و نوشتن دیده می‌شود. Virtual File Stats به DBA اجازه می‌دهد تعداد عملیات، حجم بایت‌ها و Stall را در سطح فایل مشاهده کند و تحلیل را از حد حدس فراتر ببرد. با این حال عدد بزرگ لزوماً مشکل نیست؛ باید حجم فعالیت، مدت Uptime و نوع Workload نیز در نظر گرفته شود.

Counter تجمعی در برابر نرخ بازه‌ای

مقادیر NumberReads، NumberWrites و Stallها به‌صورت تجمعی رشد می‌کنند. بنابراین برای مانیتورینگ حرفه‌ای بهتر است دو Snapshot با زمان مشخص ذخیره شود و اختلاف آن‌ها محاسبه گردد. از Delta تعداد عملیات می‌توان IOPS ساخت، از Delta بایت‌ها Throughput به دست آورد و از Delta Stall تقسیم بر Delta تعداد عملیات Latency بازه‌ای را محاسبه کرد. این روش مشکل کوتاه‌مدت را بهتر از میانگین تجمعی چند هفته نشان می‌دهد.

ارتباط با فایل Data و Log

فایل Data و فایل Log الگوی I/O یکسانی ندارند. Data File معمولاً Read و Writeهای متنوع‌تری دارد، در حالی که Log به Flushهای مرتبط با Commit بسیار حساس است. به همین دلیل Threshold یکسان برای هر دو نوع فایل همیشه معنی‌دار نیست. تحلیل باید نوع فایل، Recovery Model، الگوی Transaction، Checkpoint و ویژگی Storage را کنار هم ببیند.

اهمیت نام و مسیر فیزیکی فایل

شناسه‌های database_id و file_id برای موتور مناسب‌اند اما برای تیم عملیات کافی نیستند. هنگام Incident باید بدانیم فایل کند روی کدام Drive، Mount Point یا Storage Pool قرار دارد. Join با sys.master_files نام منطقی و physical_name را به Counterهای I/O متصل می‌کند. در محیط‌های بزرگ بهتر است Mapping زیرساخت نیز در CMDB یا جدول مانیتورینگ نگهداری شود تا چند Database مشترک روی یک Volume سریع شناسایی شوند.

Baseline و تشخیص رفتار غیرعادی

Baseline قابل اعتماد باید رفتار معمول سامانه را در Windowهای مختلف ثبت کند. ساعات کاری، Batch شبانه، Backup و Maintenance هر کدام الگوی جداگانه‌ای دارند. اگر فقط یک Average روزانه نگهداری شود، Spikeهای مهم پنهان می‌شوند. بهتر است شاخص‌های Read، Write، Throughput و Latency در بازه‌های هم‌نوع مقایسه شوند و رویدادهایی مانند Deployment یا Storage Migration نیز روی Timeline ثبت گردند.

رابطه با Wait Statistics

Virtual File Stats به‌تنهایی علت قطعی کندی را ثابت نمی‌کند. برای نمونه PAGEIOLATCH می‌تواند با Read کند مرتبط باشد، اما ممکن است Query Plan نامناسب حجم بسیار زیادی داده بخواند. WRITELOG نیز ممکن است از Storage کند یا Transactionهای بسیار ریز ناشی شود. تحلیل حرفه‌ای Wait Statistics، Query Store، Execution Plan و آمار فایل را کنار هم می‌گذارد و به دنبال همبستگی زمانی می‌گردد.

ظرفیت‌سنجی Storage

برای انتخاب Storage جدید، فقط Peak IOPS یا Average کافی نیست. باید توزیع بار، Read/Write Ratio، Throughput و Latency در ساعات مختلف دیده شود. داده‌های Virtual File Stats در بازه‌های زمانی واقعی ورودی خوبی برای Capacity Planning هستند. این اطلاعات می‌توانند قبل و بعد از انتقال به SSD، SAN، Cloud Disk یا Tier جدید مقایسه شوند و اثر واقعی تغییر زیرساخت را نشان دهند.

ریسک SELECT ستاره در کد Legacy

یکی از مشکلات رایج اسکریپت‌های قدیمی استفاده از SELECT * است. چنین کدی به ترتیب و شکل Result Set وابسته می‌شود و مهاجرت را شکننده می‌کند. بهتر است ستون‌های مورد نیاز صریح انتخاب شوند، Aliasهای مورد انتظار مصرف‌کننده حفظ گردند و Contract خروجی مستند شود. این کار به‌خصوص زمانی مهم است که یک گزارش، ETL یا ابزار مانیتورینگ خروجی را بر اساس موقعیت ستون‌ها می‌خواند.

Compatibility Layer در مهاجرت

در سامانه‌های بزرگ تغییر مستقیم همه مصرف‌کننده‌ها پرریسک است. می‌توان یک Stored Procedure یا View داخلی ساخت که داده DMF جدید را با Aliasهای قدیمی برگرداند. سپس مصرف‌کننده‌ها مرحله‌ای منتقل شوند. این لایه موقت Rollback ساده‌تری ایجاد می‌کند و اجازه می‌دهد Regression Test بدون فشار زمانی روی Production انجام شود. پس از تثبیت همه مصرف‌کننده‌ها، لایه سازگاری حذف می‌شود.

Permission و امنیت

Queryهای Performance معمولاً به Permissionهای سطح سرور یا سطح Performance نیاز دارند و جزئیات می‌تواند با نسخه SQL Server تفاوت داشته باشد. اصل Least Privilege باید رعایت شود؛ به‌جای اعطای دسترسی گسترده، فقط مجوز لازم برای ابزار مانیتورینگ داده شود. در محیط‌های حساس می‌توان جمع‌آوری را از طریق Stored Procedure امضاشده یا حساب سرویس کنترل‌شده انجام داد.

نگهداری تاریخچه

اگر هر دقیقه Snapshot ذخیره شود، در Instanceهای بزرگ حجم History سریع رشد می‌کند. طراحی باید Retention مشخص داشته باشد. داده خام کوتاه‌مدت برای Incident نگهداری شود و Aggregate ساعتی یا روزانه برای Trend بلندمدت باقی بماند. ایندکس روی SampleTime، database_id و file_id نیز برای گزارش‌گیری ضروری است.

روش تست قبل از حذف کد قدیمی

پیش از حذف Dependency قدیمی باید Test Plan داشته باشید: ورودی‌ها و فیلترهای فعلی ثبت شوند، Query جدید در محیط مشابه اجرا شود، Schema خروجی و نوع داده‌ها مقایسه گردد و Jobها و گزارش‌های وابسته Regression Test شوند. تفاوت طبیعی ناشی از اجرای Queryها در زمان‌های مختلف نباید با اختلاف واقعی Mapping اشتباه گرفته شود. در پایان Rollback Plan مشخص باشد.

نگاشت مفهومی ستون‌های قدیمی و جدید

شش مثال کاربردی

مثال 1: نمایش همه فایل‌ها با DMF مدرن

نمای کلی Counterهای I/O همه فایل‌ها را می‌دهد. NULL,NULL یعنی محدودیت مشخصی برای Database و File اعمال نشده است.

SELECT database_id, file_id, num_of_reads, num_of_writes,
       io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

این خروجی Snapshot تجمعی است و برای نرخ واقعی باید با Snapshot قبلی مقایسه شود.

مثال 2: محاسبه متوسط Read Latency

با تقسیم Stall خواندن بر تعداد Read می‌توان میانگین ساده تأخیر هر عملیات را به دست آورد. NULLIF از تقسیم بر صفر جلوگیری می‌کند.

SELECT database_id, file_id,
CAST(io_stall_read_ms * 1.0 / NULLIF(num_of_reads,0) AS decimal(18,2)) AS AvgReadLatencyMs
FROM sys.dm_io_virtual_file_stats(NULL,NULL);

Threshold را بر اساس Baseline و نوع Storage تفسیر کنید.

مثال 3: محاسبه متوسط Write Latency

این مثال همان منطق را برای عملیات نوشتن اجرا می‌کند و برای بررسی Data و Log مفید است.

SELECT database_id, file_id,
CAST(io_stall_write_ms * 1.0 / NULLIF(num_of_writes,0) AS decimal(18,2)) AS AvgWriteLatencyMs
FROM sys.dm_io_virtual_file_stats(NULL,NULL);

مقایسه Data و Log باید با توجه به الگوی متفاوت I/O انجام شود.

مثال 4: اتصال به نام و مسیر فایل

برای تحلیل عملیاتی، Counterها را به نام Database، Logical File و Physical Path متصل می‌کنیم.

SELECT DB_NAME(v.database_id) AS DatabaseName,
       m.name, m.physical_name, v.num_of_reads, v.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
JOIN sys.master_files AS m
  ON m.database_id=v.database_id AND m.file_id=v.file_id;

این Query نقطه شروع مناسبی برای ارتباط DBA و تیم Storage است.

مثال 5: مقایسه رابط قدیمی و جدید

برای اعتبارسنجی مهاجرت، خروجی مفهومی هر دو رابط را در محیط Test بررسی کنید.

SELECT TOP (5) DbId,FileId,NumberReads,NumberWrites
FROM sys.fn_virtualfilestats(NULL,NULL);

SELECT TOP (5) database_id,file_id,num_of_reads,num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL,NULL);

به‌دلیل گذر زمان انتظار برابری عددی مطلق بین دو اجرای جداگانه نداشته باشید.

مثال 6: رتبه‌بندی فایل‌ها بر اساس Stall

این Query فایل‌هایی را که بیشترین Stall تجمعی دارند مرتب می‌کند تا بررسی اولیه سریع‌تر شود.

SELECT TOP (10) DB_NAME(v.database_id) AS DatabaseName,
m.name, v.io_stall_read_ms + v.io_stall_write_ms AS TotalStallMs
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
JOIN sys.master_files AS m
ON m.database_id=v.database_id AND m.file_id=v.file_id
ORDER BY TotalStallMs DESC;

Stall تجمعی زیاد الزاماً مشکل نیست؛ آن را با تعداد عملیات و Delta زمانی نرمال کنید.

خطاهای رایج

• تفسیر Counter تجمعی به‌عنوان نرخ لحظه‌ای.
• تقسیم بدون NULLIF و بدون تبدیل به decimal.
• استفاده از یک Threshold برای Data File و Log File.
• اتکا به SELECT * در ابزارهای مانیتورینگ.
• نتیجه‌گیری از یک Snapshot بدون Baseline.
• فراموش‌کردن Permissionهای نسخه مقصد.

بهترین روش‌ها

• برای کد جدید از sys.dm_io_virtual_file_stats استفاده کنید.
• Counterها را در Snapshotهای زمان‌دار ذخیره و Delta محاسبه کنید.
• نام Database و مسیر فایل را همراه Metric ذخیره کنید.
• Alert را بر اساس Baseline و چند نمونه متوالی تعریف کنید.
• مهاجرت را مرحله‌ای و با Regression Test انجام دهید.
• Retention تاریخچه را کنترل و Aggregate بلندمدت تولید کنید.

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

Legacy Function چیست؟

قابلیتی از نسل قدیمی SQL Server است که ممکن است هنوز وجود داشته باشد اما برای آن رابط جدیدتری ارائه شده باشد. Legacy الزاماً به معنی حذف‌شده نیست.

fn_virtualfilestats چه کاربردی دارد؟

برای مشاهده آمار تجمعی I/O فایل‌ها مانند Read، Write، بایت‌ها و زمان Stall به کار می‌رود.

برای کد جدید چه جایگزینی مناسب است؟

sys.dm_io_virtual_file_stats رابط مدرن‌تری برای همین حوزه اطلاعاتی است.

آیا مهاجرت باید فوری باشد؟

خیر. ابتدا Dependency، ریسک و سازگاری نسخه بررسی و سپس مهاجرت مرحله‌ای انجام شود.

چرا Counter تجمعی کافی نیست؟

چون رفتار لحظه‌ای را پنهان می‌کند. Delta بین دو Snapshot تصویر بهتری از یک بازه زمانی می‌دهد.

برای پروژه مانیتورینگ چه داده‌ای ذخیره کنیم؟

SampleTime، شناسه Database و File، Read/Write، Bytes و Stallها حداقل داده‌های مفید هستند.

رایج‌ترین خطای محاسبه چیست؟

تقسیم Integer یا تقسیم بر صفر در محاسبه Latency.

آیا این Queryها سنگین‌اند؟

خواندن مستقیم معمولاً سبک است، اما جمع‌آوری پرتکرار و History بزرگ باید طراحی شود.

بهترین روش مهاجرت چیست؟

Inventory، Mapping، Compatibility Layer موقت، Test و Deploy مرحله‌ای.

سازگاری نسخه‌ای چگونه بررسی می‌شود؟

Permission، نوع داده ستون‌ها و رفتار Query باید روی همان نسخه مقصد آزمایش شود.

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

• چرا Delta از Counter تجمعی برای Incident مفیدتر است؟
• چگونه AvgReadLatency را بدون Divide by zero محاسبه می‌کنید؟
• چرا Data و Log را جدا تحلیل می‌کنیم؟
• خطر SELECT * در مهاجرت چیست؟
• چگونه Virtual File Stats را با Wait Statistics ترکیب می‌کنید؟
• برای ساخت Baseline چه Windowهایی را جدا می‌کنید؟

جمع‌بندی

شناخت کد Legacy بخشی از نگهداری حرفه‌ای SQL Server است. fn_virtualfilestats نمونه‌ای مهم است، زیرا هم در اسکریپت‌های قدیمی دیده می‌شود و هم مفاهیم بنیادی تحلیل I/O را نشان می‌دهد. برای توسعه جدید، استفاده از DMF مدرن همراه با Snapshot، Delta و Baseline ساختار قابل نگهداری‌تری می‌سازد.

برای جزئیات Syntax، ستون‌ها، Permissionها، ۱۰ مثال عملی و مهاجرت قدم‌به‌قدم، مقاله کامل fn_virtualfilestats را مطالعه کنید.