Data World

Posts Tagged ‘sql server waits’

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

Posted in Performance Tuning, sql server | Tagged: , , , | 2 Comments »