This blog post is in the response to the T-SQL Tuesday #004: IO post by Mike Walsh.

I ran into a problem recently whereby I was seeing some disk queuing and it was impacting performance. Nothing much new there, these things happen. However I was fortunate in that some new disk had just been added to the server and there was an opportunity to offload some of the i/o on to some new spindles. 

 Rather than just throw a couple of databases or logs onto the new disk I decided to take a look and see where the majority of my i/o was coming from. I thought that I knew where it was and knew what I was going to move. I was surprised by what I actually found out.

There’s a new function in SQL 2005/2008 called sys.dm_io_virtual_file_stats. You pass in the database id and file id to get a list of statistics including i/o bytes read and written, number of i/o reads and writes and any i/o stalls or waits. What’s great is that if you pass in null for the two function paramters you get back information for every file on the SQL Instance.

SELECT * FROM sys.dm_io_virtual_file_stats(null, null)

This information would be very useful if you could make head or tail of half the information. This is where the system view sys.master_files comes in very useful. This view contains information about every file on the SQL Instance; what it is, where it is, it’s state, size, growth, max size and more.

SELECT * FROM sys.master_files

If we join the two together we can easily see what files are showing the heaviest i/o on reads, writes or combined. 

SELECT DB_NAME(svs.database_id) AS DatabaseName

, smf.name AS InternalName

, smf.physical_name AS PhysicalFile

, smf.type_desc AS FileType

, CONVERT(NUMERIC(15,2), CONVERT(NUMERIC(15,2),io_stall_read_ms) / num_of_reads) AS AverageReadWait_ms

, CONVERT(NUMERIC(15,2), CONVERT(NUMERIC(15,2),io_stall_write_ms) / num_of_writes) AS AverageWriteWait_ms

, CONVERT(NUMERIC(15,2), ((CONVERT(NUMERIC(15,2),io_stall_read_ms) / num_of_reads) 

       +  (CONVERT(NUMERIC(15,2),io_stall_write_ms) / num_of_writes)) / 2) AS AverageWait_ms

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) svs

INNER JOIN sys.master_files smf

ON svs.database_id = smf.database_id 

AND svs.file_id = smf.file_id

ORDER BY AverageWait_ms DESC

Using this I was able to quickly identify the worst performing database, what file within that database was being hardest hit and move it out to it’s own set of spindles and greatly improve read/write times across the board.

One thing to note, this is based upon the understanding that the indexes are correctly set and utilized on the database in question and that files and filegroups allocations are adjusted already for the best performance. This is far from a catchall and simply adding a covering index to an existing table could provide significant improvements. This is just something to help along the road.