It has often been said that even the most write intensive OLTP databases have an average of 80% reads and 20% writes. I am thinking interactivity has increased in applications because technology has allowed it to get more write intensive but I am just guessing.
Run this on your OLTP Crown Jewels and post the results. While not very scientific, it will give us some anecdotal data. The query could also be useful when sizing new hardware.
I came up with 97/3, 74/26 and 60/40 on three high volume databases.
--This query has minimal impact. --Looks at index metadata to determine read\write ratio since the last restart of the instance.SELECT CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal)/CAST(SUM(user_updates)+SUM(user_seeks+user_scans+user_lookups) AS decimal) AS ReadPercent, CAST(SUM(user_updates) AS decimal)/CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) AS WriteRatioFROM sys.dm_db_index_usage_statsPost your results!email it! | | | | | Permalink 15 Comments Rate this Post: 12345COMMENTS: Interesting idea! I ran this in my largest OLTP database and came up with about a 70/30 split. Any idea if replication is included in the reads? That could inflate the numbers some. posted @ Friday, January 23, 2009 3:17 PM by Michelle Ufford 93/7We do paycheck processing among other things & have several writes per employee paycheck each week. posted @ Friday, January 23, 2009 3:19 PM by Stephen Moore Here is what I got from systems in my environment:88/1299.7/.00360/40 posted @ Friday, January 23, 2009 3:23 PM by Russ Transactional replication reads the log. Merge, hhm, maybe. posted @ Friday, January 23, 2009 3:24 PM by JasonMassie On main system, 83% read, 17% write. Pretty darn close to 80/20. Didn't expect that -- I thought it would be a bit more even. posted @ Friday, January 23, 2009 5:46 PM by sfuqua I came up with 87/13 on my primary OLTP db posted @ Saturday, January 24, 2009 10:32 AM by Coffegrl Very clever approach, Jason. Keep 'em coming! posted @ Saturday, January 24, 2009 4:09 PM by Jimmy May Jason,A minor revision to your script to show the percent between 0 and 100 (instead of 0 and 1):SELECT CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks+user_scans+user_lookups) AS decimal) * 100 AS ReadPercent, CAST(SUM(user_updates) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS WriteRatioFROM sys.dm_db_index_usage_statsA second version of the script that summarizes by database name (instead of summarizing for all databases within a DB instance):SELECT sd.name As DatabaseName, CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks+user_scans+user_lookups) AS decimal) * 100 AS ReadPercent, CAST(SUM(user_updates) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS WriteRatioFROM sys.dm_db_index_usage_stats dbiusJoin sys.databases sd On (sd.database_id = dbius.database_id)Group By sd.nameOrder By sd.nameRegarding the 80 / 20 threshold, the measured / displayed statistics reflect DBMS-level reads and writes, and not physical reads and writes (which could be masked to smaller numbers by storage-level caching or increased to larger numbers by storage protection – RAID-5, etc.). These factors are not easy to measure or repeatable in all cases (depends on configuration, workload levels and sequencing, etc.), but should be considered when evaluating if a measured 80 / 20 is really 80 / 20.Thanks for inspiring the discussion.Scott R. posted @ Sunday, January 25, 2009 8:38 AM by Scott R. Nice!My main application DB is 71/19My logging DB is 2/98 posted @ Sunday, January 25, 2009 9:32 AM by Matt M 92/8 posted @ Monday, January 26, 2009 8:34 AM by Michael Swart Comments from the following blog entry: http://davidtate.org/blog/?p=157 posted @ Tuesday, March 10, 2009 7:13 PM Comments from the following blog entry: http://davidtate.org/blog/?p=157 posted @ Tuesday, March 10, 2009 7:13 PM I get 37/63 for our big OLTP DB posted @ Saturday, May 02, 2009 11:00 AM by Glenn Berry On my worst offender (currently being rescued):0.6370442216154412115 / 0.3629557783845587884 posted @ Monday, May 04, 2009 11:27 PM by Marc Brook Comments from the following blog entry: Read/Write Ratio versus Read/Write Ratio?, located at: http://sqlblog.com/blogs/louis_davidson/archive/2009/06/20/read-write-ratio-versus-read-write-ratio.aspx posted @ Saturday, June 20, 2009 1:44 PM by Louis DavidsonName (required)Email (required) WebsiteNotify me of followup comments via e-mailSimple BBCode can be used like [url=http://example.com]Example[/url] and [B]
--This query has minimal impact.
--Looks at index metadata to determine read\write ratio since the last restart of the instance.SELECT CAST(SUM(user_seeks+user_scans+user_lookups) AS decimal)/CAST(SUM(user_updates)+SUM(user_seeks+user_scans+user_lookups) AS decimal) AS ReadPercent, CAST(SUM(user_updates) AS decimal)/CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) AS WriteRatioFROM sys.dm_db_index_usage_stats
Post your results!
posted @ Friday, January 23, 2009 3:17 PM by Michelle Ufford
posted @ Friday, January 23, 2009 3:19 PM by Stephen Moore
posted @ Friday, January 23, 2009 3:23 PM by Russ
posted @ Friday, January 23, 2009 3:24 PM by JasonMassie
posted @ Friday, January 23, 2009 5:46 PM by sfuqua
posted @ Saturday, January 24, 2009 10:32 AM by Coffegrl
posted @ Saturday, January 24, 2009 4:09 PM by Jimmy May
posted @ Sunday, January 25, 2009 8:38 AM by Scott R.
posted @ Sunday, January 25, 2009 9:32 AM by Matt M
posted @ Monday, January 26, 2009 8:34 AM by Michael Swart
posted @ Tuesday, March 10, 2009 7:13 PM
posted @ Saturday, May 02, 2009 11:00 AM by Glenn Berry
posted @ Monday, May 04, 2009 11:27 PM by Marc Brook
posted @ Saturday, June 20, 2009 1:44 PM by Louis Davidson
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail