Are you a reader or a writer?

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