آموزش کامل fn_virtualfilestats در SQL Server و جایگزینی با sys.dm_io_virtual_file_stats
تابع fn_virtualfilestats یک System Table-Valued Function برای مشاهده آمار I/O فایلهای SQL Server است. خروجی آن شامل تعداد Read و Write، بایتهای خوانده و نوشتهشده و زمانهای Stall است. این اطلاعات برای شناسایی فایل پرترافیک، تحلیل رفتار Storage و فهم علتهای احتمالی کندی بسیار مفید هستند.
برای توسعه جدید، sys.dm_io_virtual_file_stats معمولاً رابط مناسبتری است. با این حال بسیاری از Jobها و گزارشهای قدیمی هنوز fn_virtualfilestats را فراخوانی میکنند؛ بنابراین DBA باید هم ساختار قدیمی را بشناسد و هم بتواند آن را به DMF جدید مهاجرت دهد بدون اینکه معنی محاسبات یا Contract خروجی شکسته شود.
نکته مهم این است که Counterهای این تابع تجمعی هستند. عدد بزرگ NumberReads یا IoStallReadMS بهتنهایی نشانه مشکل نیست. تحلیل دقیق با Snapshotهای زماندار، Delta، Average Latency و مقایسه با Baseline انجام میشود.
برای دیدن جایگاه این تابع در مجموعه توابع قدیمی، راهنمای جامع Legacy Function در SQL Server را نیز بخوانید.
تعریف و Syntax
دو پارامتر اصلی database_id و file_id هستند. با NULL میتوان دامنه گستردهتری از فایلها را مشاهده کرد. در کدهای جدید بهتر است ستونها صریح انتخاب شوند و از SELECT * برای Integration دائمی استفاده نشود.
SELECT *
FROM sys.fn_virtualfilestats
(
{ database_id | NULL },
{ file_id | NULL }
);
پارامترها
- database_id: شناسه Database؛ برای خوانایی میتوان DB_ID را به کار برد.
- file_id: شناسه فایل در Database.
- NULL: برای دریافت چند فایل یا دامنه گستردهتر استفاده میشود.
نوع خروجی و ستونهای کلیدی
| ستون قدیمی | معنا | ستون مدرن | نکته |
|---|
| DbId | شناسه Database | database_id | Join با metadata |
| FileId | شناسه فایل | file_id | Join با sys.master_files |
| NumberReads | تعداد Read | num_of_reads | تجمعی |
| BytesRead | بایت Read | num_of_bytes_read | حجم خواندن |
| IoStallReadMS | Stall خواندن | io_stall_read_ms | Latency |
| NumberWrites | تعداد Write | num_of_writes | تجمعی |
| BytesWritten | بایت Write | num_of_bytes_written | حجم نوشتن |
| IoStallWriteMS | Stall نوشتن | io_stall_write_ms | Latency |
تفسیر Counterها
Counterهای این تابع Event لحظهای نیستند. برای یک بازه مشخص، مقدار ابتدا و انتها را ذخیره و اختلاف را محاسبه کنید. این روش کمک میکند IOPS، Throughput و Latency همان Window به دست آید و رفتار قدیمی سیستم با رخداد جدید مخلوط نشود.
تفاوت Data و Log
فایل Data و Log رفتار متفاوتی دارند. Log به Flushهای Commit حساس است و Data الگوی Read و Write متنوعتری دارد. بنابراین تحلیل باید نوع فایل، Workload، Recovery Model و Storage Layout را در نظر بگیرد.
مهاجرت بدون ریسک
قبل از جایگزینی تابع قدیمی، ستونهای مصرفشده را Inventory کنید. Query جدید را با Aliasهای لازم بسازید، خروجی را در Test مقایسه کنید و Jobها و گزارشهای وابسته را Regression Test نمایید.
مانیتورینگ دورهای
برای داشبورد حرفهای، SampleTime و Counterها را در History ذخیره کنید. با LAG یا منطق مشابه Delta بسازید و Retention را کنترل نمایید. داده خام کوتاهمدت و Aggregate بلندمدت ترکیب مناسبی برای Incident و Trend هستند.
۱۰ مثال عملی مستقل
مثال 1: همه فایلها
سادهترین Query برای شناخت ساختار خروجی.
SELECT * FROM sys.fn_virtualfilestats(NULL,NULL);
| معیار | خروجی نمونه | توضیح |
|---|
| DbId | 5 | نمونه |
| FileId | 1 | نمونه |
| NumberReads | 12450 | تجمعی |
برای استفاده دائمی ستونها را صریح انتخاب کنید.
مثال 2: یک Database و File
با DB_ID نام Database را به شناسه تبدیل کنید.
SELECT DbId,FileId,NumberReads,NumberWrites
FROM sys.fn_virtualfilestats(DB_ID(N'master'),1);
| معیار | خروجی نمونه | توضیح |
|---|
| DbId | 1 | master نمونه |
| FileId | 1 | فایل اصلی |
وجود file_id را در Database هدف کنترل کنید.
مثال 3: مجموع عملیات
Read و Write را برای شاخص حجم فعالیت جمع میکنیم.
SELECT DbId,FileId,NumberReads+NumberWrites AS TotalIoOperations
FROM sys.fn_virtualfilestats(NULL,NULL);
| معیار | خروجی نمونه | توضیح |
|---|
| Read | 8000 | نمونه |
| Write | 2000 | نمونه |
| Total | 10000 | مجموع |
این شاخص Latency را نشان نمیدهد.
مثال 4: فیلتر فایل پرترافیک
نتیجه تابع مانند Table Source در WHERE قابل فیلتر است.
SELECT DbId,FileId,NumberReads,NumberWrites
FROM sys.fn_virtualfilestats(NULL,NULL)
WHERE NumberReads+NumberWrites>100000;
| معیار | خروجی نمونه | توضیح |
|---|
| وضعیت | High Activity | نمونه |
| Threshold | 100000 | نمونه |
Threshold را از Baseline محیط استخراج کنید.
مثال 5: نمایش نام Database
DB_NAME خروجی را برای گزارش انسانی خواناتر میکند.
SELECT DB_NAME(DbId) AS DatabaseName,FileId,NumberReads
FROM sys.fn_virtualfilestats(NULL,NULL);
| معیار | خروجی نمونه | توضیح |
|---|
| DatabaseName | SalesDB | نمونه |
| FileId | 1 | نمونه |
برای مسیر فایل از sys.master_files استفاده کنید.
مثال 6: مدیریت NULL و تقسیم بر صفر
NULLIF از Divide by zero در Latency جلوگیری میکند.
SELECT DbId,FileId,
CAST(IoStallReadMS*1.0/NULLIF(NumberReads,0) AS decimal(18,2)) AS AvgReadLatencyMs
FROM sys.fn_virtualfilestats(NULL,NULL);
| معیار | خروجی نمونه | توضیح |
|---|
| NumberReads | 0 | حالت مرزی |
| Latency | NULL | بدون خطا |
NULL یعنی داده کافی برای محاسبه وجود ندارد.
مثال 7: بیشترین Stall
فایلها را بر اساس مجموع Stall مرتب میکنیم.
SELECT TOP (10) DbId,FileId,IoStallReadMS+IoStallWriteMS AS TotalStallMs
FROM sys.fn_virtualfilestats(NULL,NULL)
ORDER BY TotalStallMs DESC;
| معیار | خروجی نمونه | توضیح |
|---|
| رتبه | 1 | نمونه |
| TotalStallMs | 982345 | تجمعی |
برای نتیجهگیری آن را با تعداد I/O نرمال کنید.
مثال 8: گزارش با مسیر فیزیکی
Join با sys.master_files نام و مسیر فایل را اضافه میکند.
SELECT DB_NAME(v.DbId) AS DatabaseName,m.name,m.physical_name,v.NumberReads,v.NumberWrites
FROM sys.fn_virtualfilestats(NULL,NULL) AS v
JOIN sys.master_files AS m ON m.database_id=v.DbId AND m.file_id=v.FileId;
| معیار | خروجی نمونه | توضیح |
|---|
| DatabaseName | ERP | نمونه |
| physical_name | E:\MSSQL\ERP.mdf | نمونه |
این خروجی برای تیم Storage بسیار مفید است.
مثال 9: روش اشتباه و اصلاحشده
تقسیم Integer یا مخرج صفر میتواند نتیجه را خراب کند.
-- ضعیف
SELECT IoStallReadMS/NumberReads FROM sys.fn_virtualfilestats(NULL,NULL);
-- بهتر
SELECT CAST(IoStallReadMS*1.0/NULLIF(NumberReads,0) AS decimal(18,2))
FROM sys.fn_virtualfilestats(NULL,NULL);
| معیار | خروجی نمونه | توضیح |
|---|
| ضعیف | گردشدن یا خطا | ریسک |
| بهتر | 12.47 | نمونه |
در KPIهای Performance نوع داده محاسبه را کنترل کنید.
مثال 10: مهاجرت به DMF جدید
نسخه مدرن همان تحلیل را با نامهای جدید انجام میدهد.
SELECT DB_NAME(v.database_id) AS DatabaseName,m.name,
CAST(v.io_stall_read_ms*1.0/NULLIF(v.num_of_reads,0) AS decimal(18,2)) AS AvgReadLatencyMs,
CAST(v.io_stall_write_ms*1.0/NULLIF(v.num_of_writes,0) AS decimal(18,2)) AS AvgWriteLatencyMs
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;
| معیار | خروجی نمونه | توضیح |
|---|
| DatabaseName | Warehouse | نمونه |
| Read Latency | 21.36 | ms |
| Write Latency | 6.42 | ms |
این الگو برای توسعه جدید خواناتر و قابل نگهداریتر است.
خطاهای رایج
- استفاده از Syntax قدیمی :: برای Table-Valued Function.
- اتکا به SELECT * در Integration دائمی.
- تفسیر Counter تجمعی بهعنوان وضعیت لحظهای.
- تقسیم Integer یا تقسیم بر صفر.
- مقایسه Data و Log بدون توجه به تفاوت Workload.
- نادیدهگرفتن Permissionهای نسخه مقصد.
Performance Considerations
خواندن مستقیم این آمار معمولاً سبک است، اما جمعآوری بسیار پرتکرار روی Instance بزرگ و نگهداری History نامحدود میتواند هزینه ایجاد کند. نرخ نمونهبرداری را بر اساس هدف انتخاب کنید و فقط ستونهای لازم را ذخیره کنید. Restart، Failover یا تغییر زیرساخت نیز باید در منطق Delta لحاظ شود تا اختلافهای منفی یا غیرعادی اشتباه تفسیر نشوند.
Best Practices
- برای توسعه جدید از sys.dm_io_virtual_file_stats استفاده کنید.
- ستونها را صریح انتخاب و Mapping قدیم به جدید را مستند کنید.
- Metric را با نام Database، File و Physical Path ذخیره کنید.
- از Snapshot و Delta برای تحلیل Window استفاده کنید.
- Threshold را بر اساس Baseline و Storage واقعی تعیین کنید.
- مهاجرت را با Test و Rollback Plan انجام دهید.
سؤالات متداول
fn_virtualfilestats چیست؟
یک تابع سیستمی جدولی برای مشاهده Counterهای I/O فایلهای SQL Server است.
چگونه یک Database خاص را ببینم؟
database_id را مشخص کنید یا از DB_ID با نام Database استفاده نمایید.
برای پروژه جدید چه کنم؟
معمولاً sys.dm_io_virtual_file_stats انتخاب مدرنتر است.
آیا این داده برای Performance Tuning مفید است؟
بله، بهویژه در کنار Wait Statistics، Query Store و Execution Plan.
تفاوت اصلی با DMF جدید چیست؟
هدف مشابه است اما نام ستونها و رابط جدیدتر برای توسعه معاصر مناسبتر است.
برای داشبورد چه ذخیره کنیم؟
SampleTime، شناسهها، Read/Write، Bytes و Stallها را ذخیره کنید.
چرا Latency گاهی NULL میشود؟
اگر تعداد عملیات صفر باشد، میانگین قابل محاسبه نیست و NULLIF از خطا جلوگیری میکند.
آیا Query سنگین است؟
خود Query معمولاً سبک است؛ طراحی History و فرکانس جمعآوری مهمتر است.
بهترین روش مهاجرت چیست؟
Inventory، Mapping، Test، Deploy مرحلهای و Rollback Plan.
Permissionها در نسخهها یکساناند؟
ممکن است تفاوت داشته باشند و باید روی نسخه مقصد بررسی شوند.
سؤالات مصاحبه
- fn_virtualfilestats چه نوع تابعی است؟
- AvgReadLatency چگونه محاسبه میشود؟
- چرا Snapshot دوم لازم است؟
- NULL در پارامترها چه کاربردی دارد؟
- چگونه مسیر فیزیکی فایل را پیدا میکنید؟
- چرا DMF جدید ترجیح داده میشود؟
- تفاوت IOPS، Throughput و Latency چیست؟
چکلیست نهایی
- Syntax و پارامترها شناخته شد.
- ستونهای Read، Write و Stall Mapping شدند.
- محاسبات با NULLIF و decimal انجام میشوند.
- شناسهها به نام و مسیر فایل متصل میشوند.
- برای کد جدید DMF مدرن استفاده میشود.
- Snapshot و Delta برای مانیتورینگ طراحی میشوند.
جمعبندی
fn_virtualfilestats برای نگهداری کدهای قدیمی و فهم آمار I/O ارزشمند است، اما Counterها باید درست تفسیر شوند. عدد تجمعی با نرخ لحظهای فرق دارد و یک Snapshot برای قضاوت درباره Storage کافی نیست. در طراحی جدید، مهاجرت به sys.dm_io_virtual_file_stats همراه با Baseline و Delta رویکرد قابل نگهداریتری ایجاد میکند.
برای مرور اصول کلی خانواده، به مقاله مادر توابع قدیمی SQL Server بازگردید.