EDIT: Please also see in depth comment from Brent Ozar on my main site which extends the query provided below by adding extra columns to give the data more context and general advice when interpreting this type of data.

Just a quick post this time. Recently I needed to find out the read/write ratio for some of our customer databases as part of a bigger project of understanding the workload and performance of the databases. The project actually through up a few surprises, where databases that I thought had fairly low reads or writes actually turned out to be some of the most intensive on the system and conversely databases that I thought were packing a punch weren’t read/writing that much at all in comparison.

At the heart of this project is the DMV sys.dm_io_virtual_file_stats which I am using to gather latency and data throughput metrics and this data can easily be used to find out the read/write balance. There have been some great recent posts on this DMV such as this one by Erin Stellato.

The following is a simple query against this DMV and uses the bytes read and bytes written to calculate the percentage. In this query, the higher the number then the more read heavy it is.

SELECT m.Name
    , m.type_desc AS 'FileType'
    , CEILING(num_of_bytes_read*1.0 / (num_of_bytes_read*1.0 + num_of_bytes_written*1.0) * 100) AS 'Read/Write %'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) v
INNER JOIN sys.master_files m on m.database_id = v.database_id AND v.file_id = m.file_id

Returns

Note that the figures in this DMV are cumulative and are reset with an instance restart or database being taken offline/closed etc so to get an accurate numbers you should run this after the databases have been running for a few days under normal working conditions.

Enjoy!