As a SQL Server administrator, it is important to have the ability to monitor the performance of your SQL Server instance. One area that often causes performance issues is IO, or input/output. When the server becomes slow, it can be difficult to pinpoint the exact cause of the problem. However, with the help of Dynamic Management Views (DMVs), you can easily monitor the IO activity of your SQL Server.

In a recent conversation with a friend who was troubleshooting a SQL Server instance running inside a VM on Azure, he was not sure what suddenly went wrong and was curious to know what is going on LIVE with his SQL Server. This was an interesting question and I asked what does he want to monitor. From his experience he was quick to bounce and let me know it was around IO.

One of the DMVs that can be used to monitor IO activity is sys.dm_io_pending_io_requests. This DMV provides information about the current active waits that are IO related. By querying this DMV, you can get details about the IO stalls, average latency, and percentage of IO stalls in relation to the overall sample.

Here is an example script that can be used to monitor IO activity:

SELECT f.database_id , f.[file_id] , DB_NAME ( f.database_id ) AS database_name , f.name AS logical_file_name , f.type_desc , CAST ( CASE -- Handle UNC paths (e.g. '\\fileserver\DBs\readonly_dw.ndf') WHEN LEFT ( LTRIM ( f.physical_name ), 2 ) = '\\' THEN LEFT ( LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) + 1 ) - 1 ) -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf') WHEN CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) > 0 THEN UPPER ( LEFT ( LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) - 1 )) ELSE f.physical_name END AS NVARCHAR ( 255 )) AS logical_disk , fs.io_stall / 1000 / 60 AS io_stall_min , fs.io_stall_read_ms / 1000 / 60 AS io_stall_read_min , fs.io_stall_write_ms / 1000 / 60 AS io_stall_write_min , ( fs.io_stall_read_ms / ( 1.0 + fs.num_of_reads )) AS avg_read_latency_ms , ( fs.io_stall_write_ms / ( 1.0 + fs.num_of_writes )) AS avg_write_latency_ms , (( fs.io_stall_read_ms / 1000 / 60 )* 100 )/( CASE WHEN fs.io_stall / 1000 / 60 = 0 THEN 1 ELSE fs.io_stall / 1000 / 60 END ) AS io_stall_read_pct , (( fs.io_stall_write_ms / 1000 / 60 )* 100 )/( CASE WHEN fs.io_stall / 1000 / 60 = 0 THEN 1 ELSE fs.io_stall / 1000 / 60 END ) AS io_stall_write_pct , ABS (( fs.sample_ms / 1000 )/ 60 / 60 ) AS 'sample_HH' , (( fs.io_stall / 1000 / 60 )* 100 )/( ABS (( fs.sample_ms / 1000 )/ 60 )) AS 'io_stall_pct_of_overall_sample' , PIO.io_pending_ms_ticks , PIO.scheduler_address FROM sys.dm_io_pending_io_requests AS PIO INNER JOIN sys.dm_io_virtual_file_stats (NULL,NULL) AS fs ON fs.file_handle = PIO.io_handle INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]

This script will provide you with information about the IO stalls, average latency, and percentage of IO stalls in relation to the overall sample for each file in your SQL Server instance.

Additionally, you can also monitor the usage of the schedulers in your SQL Server instance to determine if there is any skew in usage. This can be done by querying the sys.dm_os_schedulers DMV and joining it with the previous script. Here is an example of the modified script:

SELECT f.database_id , f.[file_id] , DB_NAME ( f.database_id ) AS database_name , f.name AS logical_file_name , f.type_desc , CAST ( CASE -- Handle UNC paths (e.g. '\\fileserver\DBs\readonly_dw.ndf') WHEN LEFT ( LTRIM ( f.physical_name ), 2 ) = '\\' THEN LEFT ( LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) + 1 ) - 1 ) -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf') WHEN CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) > 0 THEN UPPER ( LEFT ( LTRIM ( f.physical_name ), CHARINDEX ( '\' , LTRIM ( f.physical_name ), 3 ) - 1 )) ELSE f.physical_name END AS NVARCHAR ( 255 )) AS logical_disk , fs.io_stall / 1000 / 60 AS io_stall_min , fs.io_stall_read_ms / 1000 / 60 AS io_stall_read_min , fs.io_stall_write_ms / 1000 / 60 AS io_stall_write_min , ( fs.io_stall_read_ms / ( 1.0 + fs.num_of_reads )) AS avg_read_latency_ms , ( fs.io_stall_write_ms / ( 1.0 + fs.num_of_writes )) AS avg_write_latency_ms , (( fs.io_stall_read_ms / 1000 / 60 )* 100 )/( CASE WHEN fs.io_stall / 1000 / 60 = 0 THEN 1 ELSE fs.io_stall / 1000 / 60 END ) AS io_stall_read_pct , (( fs.io_stall_write_ms / 1000 / 60 )* 100 )/( CASE WHEN fs.io_stall / 1000 / 60 = 0 THEN 1 ELSE fs.io_stall / 1000 / 60 END ) AS io_stall_write_pct , ABS (( fs.sample_ms / 1000 )/ 60 / 60 ) AS 'sample_HH' , (( fs.io_stall / 1000 / 60 )* 100 )/( ABS (( fs.sample_ms / 1000 )/ 60 )) AS 'io_stall_pct_of_overall_sample' , PIO.io_pending_ms_ticks , PIO.scheduler_address , os.scheduler_id , os.pending_disk_io_count , os.work_queue_count FROM sys.dm_io_pending_io_requests AS PIO INNER JOIN sys.dm_io_virtual_file_stats (NULL,NULL) AS fs ON fs.file_handle = PIO.io_handle INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id] INNER JOIN sys.dm_os_schedulers AS os ON PIO.scheduler_address = os.scheduler_address

This modified script will provide you with additional information about the schedulers, including the pending disk IO count and work queue count.

By using these DMVs and scripts, you can easily monitor the IO activity of your SQL Server instance and identify any potential performance issues related to IO. It is important to regularly monitor and optimize the IO activity to ensure the smooth operation of your SQL Server.

Has IO been a point of problem for you anytime? Have you used scripts similar to this in your environments? Let me know in the comments!