I stumbled across a query that gives the percentage reads versus writes and thought it might be interesting to see how some of my servers stack up.
I gave the query a couple tweaks so it returns percentage directly, as well as total reads and writes. I needed the totals so that I could calculate read and write percentages for a group of servers.
select
sum(user_seeks + user_scans + user_lookups) as total_reads,
sum(user_updates) as total_writes,
cast(
cast(sum(user_seeks + user_scans + user_lookups) as decimal) * 100.0
/
cast(Sum(user_updates) + sum(user_seeks + user_scans + user_lookups) as decimal)
as decimal(5, 2)) as read_percent,
cast(
cast(Sum(user_updates) as decimal) * 100
/
cast(Sum(user_updates) + sum(user_seeks + user_scans + user_lookups) as decimal)
as decimal(5, 2)) as write_percent
from
sys.dm_db_index_usage_stats
I recycled the original query from http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/324/Is-8020-a-90rsquos-Estimate.aspx
After running this as a multi-server query against a group of 12 instances, I put the query results into a spreadsheet so I could see how individual servers stacked up against the group.
There was quite a bit of variation, but all the instances had more reads than writes. The most read-skewed instance in the group did nearly 40 reads for each write, while the least read-skewed server only did 4 reads for every 3 writes.
As a group, these instances were reading over writing at a 5 to 1 ratio: the 80/20 rule we hear so much about is alive and well on these instances.
| Instance | Reads | Read Pct | Writes | Write Pct | Read/Write Ratio |
| instance01 | 5,299,601 | 73% | 1,961,846 | 27% | 2.7:1 |
| instance02 | 35,440,282 | 91% | 3,570,652 | 9% | 9.93:1 |
| instance03 | 9,083,137 | 79% | 2,475,755 | 21% | 3.67:1 |
| instance04 | 27,181,812 | 64% | 15,568,632 | 36% | 1.75:1 |
| instance05 | 11,438,327 | 72% | 4,384,756 | 28% | 2.61:1 |
| instance06 | 391,095,544 | 94% | 27,148,899 | 6% | 14.41:1 |
| instance07 | 295,659,557 | 57% | 220,249,874 | 43% | 1.34:1 |
| instance08 | 488,649,390 | 97% | 13,016,100 | 3% | 37.54:1 |
| instance09 | 47,661,242 | 87% | 6,896,585 | 13% | 6.91:1 |
| instance10 | 63,179,128 | 80% | 15,418,897 | 20% | 4.1:1 |
| instance11 | 195,456,589 | 87% | 28,239,952 | 13% | 6.92:1 |
| instance12 | 747,410,692 | 86% | 123,282,598 | 14% | 6.06:1 |
| Group Totals | 2,317,555,301 | 83% | 462,214,546 | 17% | 5.01:1 |
