Tell me more ×
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.
up vote 4 down vote favorite

Sorry I am not a DBA so bear with me. I wanted to get a better understanding of the ratio of reads to writes in our archiecture. The best to get this is at the database. We are using SQL Server 2008. Is there anyway to get this without impacting performance and to get smart summaries? For example, the daily read / write ratio?

Many thanks.

share|improve this question
2  
For physical writes and reads you can use sys.dm_io_virtual_file_stats. For logical reads not sure. – Martin Smith Mar 15 '12 at 9:53
That's a good start for answer. Thanks – dublintech Mar 15 '12 at 10:22
You could also look at sys.dm_exec_query_stats or sys.dm_os_performance_counters – Martin Smith Mar 15 '12 at 10:50

1 Answer

up vote 3 down vote

This will be incomplete, but it will give you a good idea of the ratios you're looking for. You can use sys.dm_db_index_usage_stats to retreive the access from various indexes on the system. If you combine the seeks, scans, and lookups you'll get a good idea of the reads. Something like this:

SELECT  ddius.user_updates,
        ddius.user_lookups + ddius.user_scans + ddius.user_seeks AS user_reads
FROM    sys.dm_db_index_usage_stats AS ddius

This data is cumulative, so you'll need to capture the previous day in order to find the differences between yesterday & today. As I say, this won't give you a perfect measure, but it will move you in the right direction.

share|improve this answer
These counters get incremented by 1 when a plan is executed containing (say) the lookup operator irrespective of whether 0 or 1 million lookups happened in reality. Example here – Martin Smith Mar 15 '12 at 10:47
1  
True. Same thing for the writes. But since we're just interested in ratios it's a method. There isn't a pure way to get this done that gives you all of what's needed to establish a perfect solution. That's a big part of why I said it would be incomplete. – Grant Fritchey Mar 15 '12 at 10:52
Yes I haven't found a way. sys.dm_exec_query_stats has reads and writes but not broken down by DB and only includes cached plans obviously. Couldn't see any performance counters that had this at a DB level either. – Martin Smith Mar 15 '12 at 10:54
1  
Yeah, it's just not a measure that you can go to one location and get something accurate. – Grant Fritchey Mar 15 '12 at 11:45

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.