راهنمای جامع DMVهای عملکرد ورودی/خروجی در SQL Server
عملکرد ورودی/خروجی یکی از مهمترین پایههای پایداری و سرعت SQL Server است. کندی Storage، فشار tempdb، رشد Transaction Log، کمبود فضای Volume یا پیکربندی نادرست Shared Storage میتواند تجربه کاربر و زمان پاسخ Queryها را بهشدت تحت تأثیر قرار دهد. مجموعه DMVها و DMFهای این مقاله ابزارهای اصلی برای مشاهده این لایهها هستند.
هدف این راهنما فقط معرفی نام Viewها نیست. ابتدا یک مدل ذهنی از File، Request، Volume، Database، Transaction Log و Failover Cluster میسازیم، سپس هر ابزار را با کاربرد و لینک مقاله مستقل معرفی میکنیم. در ادامه مثالهای ترکیبی، روش Baseline، تفسیر Counterهای تجمعی، Snapshotهای لحظهای و Best Practice مانیتورینگ را بررسی خواهیم کرد.
جدول مقایسه ابزارها
| تابع یا نما | کاربرد اصلی | نوع خروجی یا نکته مهم | لینک آموزش کامل |
|---|
| sys.dm_io_virtual_file_stats | Latency و حجم Read/Write فایلها | Counter تجمعی | آموزش کامل |
| sys.dm_io_pending_io_requests | Pending I/O لحظهای | Snapshot لحظهای | آموزش کامل |
| sys.dm_io_cluster_shared_drives | Driveهای اشتراکی FCI | View قدیمیتر | آموزش کامل |
| sys.dm_io_cluster_valid_path_names | Pathهای معتبر و CSV در FCI | مناسب Mount Point/CSV | آموزش کامل |
| sys.dm_os_volume_stats | ظرفیت و فضای آزاد Volume | Volume-level | آموزش کامل |
| sys.dm_db_file_space_usage | مصرف فضای Data و tempdb | Database-scoped | آموزش کامل |
| sys.dm_db_log_space_usage | مصرف Transaction Log | Log تجمیعی | آموزش کامل |
| sys.dm_db_log_stats | خلاصه سلامت Log و VLF | Summary سطح بالا | آموزش کامل |
| sys.dm_db_log_info | جزئیات هر VLF | جزئیات ریزدانه | آموزش کامل |
مدل ذهنی تحلیل I/O
برای تحلیل درست باید لایهها را از هم جدا کنیم. در سطح File، تعداد Read و Write، حجم انتقال و Stall اهمیت دارد. در سطح Request، درخواستهای در حال انتظار دیده میشوند. در سطح Volume، ظرفیت و ویژگی File System مطرح است. در سطح Database، تخصیص فضای فایلها و tempdb بررسی میشود. برای Transaction Log نیز مصرف فضا، علت عدم Truncation و ساختار VLFها اهمیت دارد. در FCI، معتبر بودن Shared Path بخشی از Availability است.
زمان در تحلیل نقش بنیادی دارد. بعضی Counterها تجمعیاند و یک مقدار خام نشاندهنده کل فعالیت از یک نقطه شروع است، نه نرخ لحظهای. بعضی DMVها فقط Snapshot همین لحظه را میدهند. بنابراین SampleTime، Delta، Uptime و رخدادهایی مثل Restart یا Failover باید در History ثبت شوند. در سامانههای چندمنطقهای بهتر است Repository مانیتورینگ زمان را با datetime2 استاندارد یا UTC/datetimeoffset نگهداری کند تا Logهای SQL Server، Storage و Application روی یک Timeline قابل مقایسه باشند.
مثالهای ترکیبی و کاربردی
مثال 1: رتبهبندی Latency فایلها
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
SELECT DB_NAME(v.database_id) AS DatabaseName,m.name,
1.0*v.io_stall_read_ms/NULLIF(v.num_of_reads,0) AS AvgReadMs
FROM sys.dm_io_virtual_file_stats(NULL,NULL) v
JOIN sys.master_files m ON m.database_id=v.database_id AND m.file_id=v.file_id
ORDER BY AvgReadMs DESC;
| شاخص | مقدار نمونه | تفسیر |
|---|
| رتبهبندی Latency فایلها | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
مثال 2: مشاهده Pending I/O
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
SELECT TOP(20) io_type,io_pending,io_pending_ms_ticks,io_handle_path FROM sys.dm_io_pending_io_requests ORDER BY io_pending_ms_ticks DESC;
| شاخص | مقدار نمونه | تفسیر |
|---|
| مشاهده Pending I/O | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
مثال 3: گزارش ظرفیت Volume
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
SELECT DISTINCT v.volume_mount_point,v.total_bytes/1073741824.0 AS TotalGB,v.available_bytes/1073741824.0 AS FreeGB,
100.0*v.available_bytes/NULLIF(v.total_bytes,0) AS FreePct
FROM sys.master_files m CROSS APPLY sys.dm_os_volume_stats(m.database_id,m.file_id) v;
| شاخص | مقدار نمونه | تفسیر |
|---|
| گزارش ظرفیت Volume | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
مثال 4: تحلیل مصرف tempdb
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
USE tempdb; SELECT file_id,user_object_reserved_page_count*8.0/1024 AS UserObjectMB FROM sys.dm_db_file_space_usage;
| شاخص | مقدار نمونه | تفسیر |
|---|
| تحلیل مصرف tempdb | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
مثال 5: بررسی مصرف Transaction Log
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
SELECT s.used_log_space_in_percent,l.log_truncation_holdup_reason FROM sys.dm_db_log_space_usage s CROSS JOIN sys.dm_db_log_stats(DB_ID()) l;
| شاخص | مقدار نمونه | تفسیر |
|---|
| بررسی مصرف Transaction Log | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
مثال 6: شمارش VLF دیتابیسها
این مثال یک سناریوی واقعی مانیتورینگ را سادهسازی میکند. خروجی نمونه صرفاً برای فهم شکل نتیجه است و مقدار واقعی باید از سرور شما و در زمان مشخص جمعآوری شود.
SELECT d.name,COUNT(*) AS VlfCount FROM sys.databases d CROSS APPLY sys.dm_db_log_info(d.database_id) l WHERE d.state_desc=N'ONLINE' GROUP BY d.name ORDER BY VlfCount DESC;
| شاخص | مقدار نمونه | تفسیر |
|---|
| شمارش VLF دیتابیسها | وابسته به محیط | نتیجه را با Baseline، Workload و شواهد مکمل مقایسه کنید. |
نکته عملی این است که Query را بهصورت Snapshot ذخیره کنید و فقط در صورت مشاهده روند پایدار یا همبستگی با Alertهای دیگر اقدام اصلاحی انجام دهید.
تفکیک Data و Log
فایلهای Data و Transaction Log الگوهای I/O متفاوتی دارند. Data Fileها میتوانند Random Read/Write گسترده داشته باشند، در حالی که Log به Sequential Write حساس است. بنابراین یک Average کلی برای همه فایلها مناسب نیست. نوع فایل، نقش دیتابیس و Workload را در گزارش نگه دارید و برای Log حتماً Recovery Model، Backup و Active Transaction را نیز بررسی کنید.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
ظرفیت در برابر Latency
فضای آزاد Volume و سرعت I/O دو مسئله مستقلاند. ممکن است Volume صدها گیگابایت فضای آزاد داشته باشد اما Latency بالا باشد، یا Storage سریع باشد ولی فضای آزاد برای Autogrowth باقی نمانده باشد. sys.dm_os_volume_stats برای ظرفیت، و sys.dm_io_virtual_file_stats برای رفتار I/O فایلها دادههای مکمل میدهند. Dashboard خوب این KPIها را جدا ولی در یک Context نمایش میدهد.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
tempdb و انواع مصرف
tempdb میزبان Temp Table، Worktable، Spill و Version Store است. وقتی tempdb رشد میکند، ابتدا باید نوع مصرف غالب مشخص شود. sys.dm_db_file_space_usage تفکیک User Object، Internal Object و Version Store را فراهم میکند. سپس باید Queryها، Isolation Level و عملیات Maintenance مرتبط بررسی شوند. افزودن فایل یا بزرگ کردن tempdb بدون فهم نوع مصرف ممکن است فقط علامت را پنهان کند.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
چرخه Transaction Log
پر شدن Log معمولاً یک علت مشخص دارد: Log Backup انجام نشده، تراکنش طولانی فعال است، Replication/Availability مانع Truncation است یا Workload واقعاً فضای بیشتری نیاز دارد. sys.dm_db_log_space_usage میزان مصرف را نشان میدهد، sys.dm_db_log_stats علت و خلاصه سلامت را میدهد و sys.dm_db_log_info ساختار VLF را باز میکند. استفاده ترکیبی از این سه ابزار مسیر عیبیابی را کوتاه میکند.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
VLF و رشد فایل Log
VLFها ساختار داخلی Transaction Log هستند و الگوی Growth تاریخی روی تعداد و اندازه آنها اثر میگذارد. تعداد خیلی زیاد یا توزیع نامناسب میتواند عملیات Recovery و مدیریت Log را پیچیده کند. فقط Count را معیار قرار ندهید؛ اندازه کل Log، Active VLF، Growth Setting، نسخه SQL Server و نیاز واقعی Workload نیز مهماند.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
Failover Cluster و Shared Storage
در FCI، مسیر فایلهای Data و Log باید از دید Cluster معتبر باشد. View قدیمی shared_drives بیشتر Drive Letter را نشان میدهد، در حالی که valid_path_names Path، Owner Node و CSV را بهتر پوشش میدهد. پیش از Migration یا Move File، مسیر مقصد را اعتبارسنجی کنید و بعد از Failover آزمایشی دوباره دسترسی را بررسی نمایید.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
Baseline و Delta
Counter تجمعی بدون بازه زمانی بهراحتی سوءتفسیر میشود. دو Snapshot با فاصله مشخص بگیرید، اختلاف Read، Write، Bytes و Stall را محاسبه کنید و نرخ همان بازه را بسازید. Restart سرویس یا Failover میتواند مبنای Counter را عوض کند؛ بنابراین زمان شروع SQL Server و رخدادهای زیرساختی را کنار داده History نگه دارید.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
Thresholdهای محیطی
هیچ عدد جادویی برای همه سرورها وجود ندارد. Storage NVMe، SAN، Cloud Disk و Local SSD رفتار متفاوتی دارند. Latency قابل قبول برای OLTP حساس با Data Warehouse Batch یکسان نیست. Threshold باید از Baseline سالم، SLA و Risk Appetite استخراج شود. Alert چندمرحلهای و شرط تداوم در چند Snapshot معمولاً False Positive را کاهش میدهد.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
Repository مانیتورینگ
برای History، Schema ساده و پایدار طراحی کنید: SampleTime، ServerName، DatabaseId/Name، FileId/Path و KPIهای اصلی. لازم نیست همه ستونهای همه DMVها در هر ثانیه ذخیره شوند. Retention خام کوتاهتر و Aggregate بلندمدت میتواند حجم را کنترل کند. Indexگذاری بر زمان و شناسههای اصلی برای گزارشگیری ضروری است.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
از Metric تا اقدام
گزارش حرفهای نباید فقط بگوید «I/O کند است». باید مشخص کند کدام فایل، کدام Volume، در چه بازهای، با چه Delta و همراه چه Waitهایی مشکل داشته است. سپس اقدام پیشنهادی، ریسک، Owner و معیار موفقیت تعریف شود. این زبان مشترک همکاری DBA با تیم Storage، Virtualization و توسعه را بهتر میکند.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
روش Incident Response
در Incident ابتدا Timeline بسازید: زمان شروع، Deploy، Backup، Batch، Failover و Alertها. سپس Snapshotها را با Baseline همان ساعت مقایسه کنید. Scope مشکل را از Instance به Database، File یا Volume محدود نمایید. بعد فرضیه بسازید و با داده مکمل آزمایش کنید. تغییر Production قبل از اثبات نسبی علت، احتمال ایجاد مشکل دوم را بالا میبرد.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
Best Practice تغییرات
قبل از هر تغییر Snapshot بگیرید، هدف کمی تعریف کنید و Rollback Plan داشته باشید. بعد از تغییر همان Queryهای اندازهگیری را تکرار کنید. اگر Metric هدف بهتر شد ولی تجربه کاربر تغییری نکرد، علت اصلی احتمالاً جای دیگری است. این چرخه Measure-Change-Measure از تصمیمهای سلیقهای جلوگیری میکند.
در عمل، مستندسازی Context و مقایسه قبل و بعد اهمیت زیادی دارد. هر نتیجه باید قابل بازتولید باشد؛ یعنی شخص دیگری با همان Query، همان Scope و زمان مناسب بتواند به برداشت مشابه برسد. این اصل پایه گزارشهای Health Check و مشاوره حرفهای SQL Server است.
FAQ: سؤالات متداول
از کدام DMV برای شروع تحلیل کندی I/O استفاده کنیم؟
برای File-level معمولاً sys.dm_io_virtual_file_stats شروع خوبی است. در Incident جاری pending_io_requests را نیز ببینید و سپس Waitها، Query Store و Storage را اضافه کنید.
آیا Average Latency بالا همیشه Storage را مقصر میکند؟
خیر. Workload، Queue، Backup، Checkpoint، Virtualization و الگوی Query میتوانند اثر داشته باشند. Storage فقط یک فرضیه است.
چگونه فضای آزاد Volumeهای SQL Server را ببینیم؟
sys.dm_os_volume_stats را با sys.master_files و CROSS APPLY ترکیب کنید و Volumeهای تکراری را Deduplicate نمایید.
برای tempdb کدام DMV کلیدی است؟
sys.dm_db_file_space_usage برای تفکیک User Object، Internal Object و Version Store بسیار مهم است.
برای Log پر از کجا شروع کنیم؟
ابتدا log_space_usage برای مصرف، سپس log_stats برای Holdup Reason و در صورت نیاز log_info برای ساختار VLF بررسی شود.
VLF زیاد چه معنایی دارد؟
میتواند نتیجه History نامناسب Growth باشد، اما تصمیم اصلاح فقط بر اساس Count درست نیست؛ اندازه، نسخه و Recovery requirements را هم ببینید.
در FCI از کدام View برای Path استفاده کنیم؟
valid_path_names اطلاعات کاملتری درباره Path، Owner و CSV میدهد و برای طراحی جدید مناسبتر است.
آیا DMVها را هر ثانیه Poll کنیم؟
فقط اگر هدف و نیاز واقعی دارید. Interval باید با سرعت تغییر Metric، هزینه ذخیرهسازی و SLA هماهنگ باشد.
برای Dashboard چه دادهای ذخیره کنیم؟
SampleTime، Context سرور/دیتابیس/فایل و KPIهای تبدیلشده با واحد روشن. History باید Retention و Index مناسب داشته باشد.
چگونه تفاوت نسخههای SQL Server را مدیریت کنیم؟
وجود Object و ستونها و Permissionهای نسخه هدف را تست کنید و Script را Version-aware بنویسید، مخصوصاً برای تغییرات Permission در SQL Server 2022 و بعدتر.
سؤالات مصاحبه
- تفاوت Counter تجمعی و Snapshot لحظهای را با مثال توضیح دهید.
- چگونه Average Latency فایل را محاسبه میکنید و چه محدودیتی دارد؟
- برای Root Cause رشد tempdb چه شاخصهایی را بررسی میکنید؟
- سه ابزار اصلی تحلیل Transaction Log و VLF را مقایسه کنید.
- چرا ظرفیت Volume و Latency Storage یک چیز نیستند؟
- در FCI تفاوت shared_drives و valid_path_names چیست؟
جمعبندی و مسیر مطالعه
تحلیل I/O در SQL Server یک زنجیره است: مشاهده File Statistics، Pending Requests، ظرفیت Volume، مصرف فایلهای دیتابیس، سلامت Transaction Log و در محیط Cluster اعتبارسنجی Storage. مهمتر از حفظ نام DMVها، دانستن Scope، زمان، واحد و روش همبستگی دادههاست.