Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
I guess it depends on what reasons you are looking. There are a few ways to look at this. I would also look at sys.dm_db_index_usage_stats. What are you looking for? What are you hoping to drive from this? –  Mike Walsh Dec 5 '12 at 14:27
    
I was trying to put some perspective around the WRITELOG wait type observed on one server. All servers use the same storage, so I started looking at read / write ratios on the different servers. This would also be useful when choosing a RAID level. –  Henry Lee Dec 5 '12 at 15:57
1  
You pretty much answered your own question. You might also look at sys.dm_os_buffer_descriptors to see how dirty each database is in memory. That will also give you an idea of where the action is. –  Steven Dec 5 '12 at 18:21
add comment

1 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.


share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.