Sql-server – How to determine the read / write ratio of your server

sql serverstorage

When looking at the read / write ratio of your server would you look at the NUMBER of reads and writes or the number of BYTES read and written? For example, consider the following numbers from sys.dm_io_virtual_file_stats:

93384 reads, 1080818 writes, = 8% reads, 92% writes

30630654464 bytes read, 60955863552 bytes written, = 33% bytes read, 67% bytes written

Best Answer

For your specific question - Yes. I think looking at sys.dm_io_virtual_file_stats, as you are doing is a good way to see which tables have the most reads or writes or the highest throughput of reads/writes. And I would say that looking at the returned data is good to look at in three angles. Look at the number of operations (reads or writes), look at the stalls and stall time so you can see who is waiting on IO stalls the most and look at the total bytes transferred. I would probably look at the total size last and focus on IO Operations/second IOPs number first for what you are looking for here.

I think you should also look at sys.dm_db_index_usage_stats and look at the amount of times your tables and indexes are written to (user_updates) or read from (user_lookups, user_scans, and user_seeks) to get an idea of the distribution.

Steven brings up a good idea of looking to see the buffers as well.

For The WRITELOG Waits

I think you are heading in a good direction here to see which databases are your busiest in terms of writes. That said I would consider taking a look at a script like sp_whoisactive that Adam Machanic wrote. You can periodically run this and collect the results to a table and you can actually see which statements of yours are the ones most waiting on WRITELOG.

I would argue that you may save some time in tracking down the read/write ratios of your databases (though this is great information to know and track) in this case and you can dive into a couple of other places to start. Presuming you are seeing significant WRITELOG waits and suffering issues from it (You can see these waits happening and not have them be a problem. You should ask what kind of averages you are seeing, what frequency you see the counter and if there are symptoms - are queries taking longer than expected in an app? Are users complaining? Or are you just being proactive), you could, for instance:

  • Analyze your IO Subsystem. Ask questions like:

    • Are you logs and data files on the same drive?
    • What kind of latency (avg sec/write and avg sec/read) are you seeing to these drives? If you are seeing consistent averages of 20ms and up you could have an issue.. The higher the average and the longer the spikes, the more likely this is.
    • What do the disks look like for your log drive(s)?
    • And some other questions. Lie - Do you need to take two active log files and split them onto separate storage? Are you write cache optimized? SQL Server typically doesn't take much advantage of a read cache in IO but it loves some write cache.

  • Analyze your activity. Run sp_whoisactive. Get an idea of which queries are waiting for these waits, look at your insert activity in these databases.

  • Ensure transactions are being managed appropriately. Are you committing too frequently? Waiting too long to commit? Both could have an impact on time to write the log. Too frequently - especially in bulk operations means more commits, means more log records.. Too infrequently can mean you wait to accumulate too much.

Related Question