Find Disk performance with sys.Dm_io_virtual_file_stats
“sys.Dm_io_virtual_file_stats” is one of the excellent DMV in SQL Server. Generally people used talk about processor, memory and network a lot but always overlook disk capacity. You can’t read/write more than the capacity of your disk, no matter how many processor/core you have, how much RAM you assign to SQL Server or how fast your network is.
Whenever I have a situation to deal with performance issue of SQL Server, I never forget to use “sys.Dm_io_virtual_file_stats” DMV. I have very small script but it can save so many hours which you suppose to spend in finding a bottleneck of performance in SQL Server whenever disk is a culprit.
Each disk has capacity to read and write, if your workload is generating more read/write then the capacity of your disk, you can’t achieve optimal performance of other hardware you have.
Here is the script to measure disk IO capacity, have a look at it:
SELECT DB_Name(VFS.Database_id) AS DataBaseName ,DF.Physical_Name AS FileName ,VFS.Sample_Ms ,VFS.Num_Of_Reads ,VFS.Num_Of_Bytes_Read ,VFS.IO_Stall_Read_ms ,VFS.Num_Of_Writes ,VFS.Num_Of_Bytes_Written ,VFS.IO_Stall_Write_ms ,VFS.IO_Stall ,GETDATE() AS CurrentDate FROM sys.Dm_io_virtual_file_stats(NULL, NULL) VFS INNER JOIN sys.master_files DF ON (DF.FILE_ID = VFS.FILE_ID) AND (DF.DataBase_ID = VFS.DataBase_ID)
Let us look what information comes from each column written by above given script.
DababaseName: This column obviously, as name explains, gives the name of database from the SQL Server instance where you have ran the above query in.
FileName: Again this column obviously, as name explains, gives the name of data/log file.
Sample_ms: This column shows the number of milliseconds that have passed since the last server restarts. This DMV reset all column values when server restarts.
Num_Of_Reads: This column shows number of physical reads performs after the last server/services restarts.
Num_Of_Bytes_Read: This column shows the number of total bytes read as against total number of read recorded under Num_Of_Reads column.
IO_Stall_Read_ms: This column shows total number of milliseconds which has been spent in wait. For example you have received 30 reads in one millisecond in your data/log file but your disk is able to process only 5 reads per milliseconds then you will get as much increase in the number of this column as time taken to complete those 25 reads.
Num_Of_Writes: This column shows number of write performs after the last server/services restarts.
Num_Of_Bytes_Written: This column shows the number of total bytes write as against total number of write recorded under Num_Of_Write column.
IO_Stall_Write_ms: This column shows total number of milliseconds which has been spent in wait. For example you have received 30 write in one millisecond in your data/log file but your disk is able to process only 5 writes per milliseconds then you will get as much increase in the number of this column as time taken to complete those 25 writes.
IO_Stall: This column shows the sum of IO_Stall_Read_ms and IO_Stall_Write_ms.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles .
Pingback: Analyze IO disk pressure in SQL Server
Pingback: Extreme-Advice : Analyze IO disk pressure in SQL Server « Ritesh’s Blog for SQL-SERVER & .NET
Pingback: Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats
This is awesome thank you!