16 February 2010

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