Data World

SQL Server Tuning: Find IO wait or Virtual File Stats

Posted by Pramod Singla on March 8, 2017


Problem Statement: Script to get the virtual file waits for IO waits.
Solution: Virtual File waits are useful in identifying the IO waits occurring on any of the database file. This script is used along with SQL server wait stats script to understand the IO waits well.

SELECT Db_name(vfs.database_id)                           AS database_name,
vfs.database_id,
vfs.FILE_ID,
io_stall_read_ms / NULLIF(num_of_reads, 0)         AS avg_read_latency,
io_stall_write_ms / NULLIF(num_of_writes, 0)       AS avg_write_latency,
io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency,
num_of_bytes_read / NULLIF(num_of_reads, 0)        AS avg_bytes_per_read,
num_of_bytes_written / NULLIF(num_of_writes, 0)    AS avg_bytes_per_write
,
vfs.io_stall,
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,
size_on_disk_bytes / 1024 / 1024.                  AS size_on_disk_mbytes
,
physical_name
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

Advertisements

2 Responses to “SQL Server Tuning: Find IO wait or Virtual File Stats”

  1. Hemant said

    Good info Pramod!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: