IO Usage by data file

.

/* SQL Server 2008 and R2 Diagnostic Information Queries
 Glenn Berry
 June 2012
 Last Modified: June 19, 2012
 

DESCRIPTION:  List IO usage for of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues and identify the busiest DB

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

--Calculates average stalls per read, per write, and per total input/output for each database file  
SELECT
DB_NAME(fs.database_id) AS [Database Name]
, mf.physical_name
, 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) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

-- Helps you determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location


.

Leave a Reply

Your email address will not be published. Required fields are marked *