.
/* 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
.