File Stalling in SQL Server

The amount of elapsed time that SQL Server spends waiting on the IO subsystem before individual read/write operations are complete is known as file stalling.

Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database is?

This can be solved by using this fn_virtualfilestats.

fn_virtualfilestats :

It allows you to get information for each physical file that is being used to hold your data including both the data and log files. The function returns read and write information as well as stall information, which is the time users had to wait for an I/O operation to complete. Each time this function is called it returns the overall numbers that SQL Server has collected since the last time the database engine was started, so to use this effectively you need to gather data from two different points of time and then do a comparison.

The avgerage latency information can be retrieved using the below query:

SELECT
        DB_NAME(vfs.database_id) [db_name],
    io_stall_read_ms / NULLIF(num_of_reads, 0) avg_read_latency,
    io_stall_write_ms / NULLIF(num_of_writes, 0) avg_write_latency,
    physical_name [file_name],
    io_stall / NULLIF(num_of_reads + num_of_writes, 0) avg_total_latency
FROM  
        sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
    JOIN sys.master_files AS mf
                ON vfs.database_id = mf.database_id AND vfs.FILE_ID = mf.FILE_ID
ORDER BY
        avg_total_latency DESC;

In terms of evaluating file stall metrics, an ideally suited server (or IO subsystem) will show latencies of 0-8ms for reads and writes against data files, and 0-4ms for writes and reads against log files.

Some finely tuned servers whose file stall metrics commonly climb above 100ms, 500ms, and even—occasionally, 2000ms across multiple databases on the same server, will not be facing disk performance.

This script lets you see the number of reads and writes on each data and log file for every database running on an instance of SQL Server. It’s sorted by average I/O stall time, in milliseconds:

-- Calculates average stalls per read, per write, and per total input/output

-- for each database file.

SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes ,

CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads

+ num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;

Collect the data at different intervals which will help you get the numbers to analyse of file stalls.

 

Dynamic Management Views (DMVs) that reference I/O performance :

sys.dm_exec_query_stats – I/O that a given query has cost over the times it has been executed

sys.dm_exec_connections – I/O that has taken place on that connection

sys.dm_exec_sessions – I/O that has taken place during that session

sys.dm_os_workers – I/O pending for a given worker thread.