[Video] How Can I Measure If My Database Does More Reads Than Writes?

When someone asks you, “Is this database read-intensive or write-intensive?” you probably look at sys.dm_io_virtual_file_stats to measure the file activity – but that isn’t really correct. After all, your users run a lot of queries all day that are simply satisfied from the buffer pool. SQL Server caches your data so it doesn’t have to hit the disks, but … those are reads, right? (Write? Get it?)

There’s a better way.

SQL Server is constantly tracking how many reads & writes each index gets. You can query sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats – in this blog post, I’m going to choose the former because it’s a little easier to write about. For the differences between those two and which one you should use when, hit this module of Mastering Index Tuning.

Here are the contents of sys.dm_db_index_usage_stats on my lab server:

That’s a good start because it shows seeks, scans, lookups, and updates – but we’re only seeing database, object, and index IDs. You, dear reader, probably want a little more data about which databases & tables we’re looking at, so here you go:

Here are my results:

Oops: we have our first problem. As I was writing this post, I booted up my lab VM for the first time today – it’s a bright eyed 6:14AM Pacific as I write this, being watched by countless strangers. Because my database only has activity in a couple of tables, these numbers only reflect a couple of tables. However, in terms of measuring reads vs writes, I only really wanna know about tables that have had recent activity, so these numbers will work just fine for me.

Next, I probably want to group this data together by database, so here’s a query for that:

Voila:

Some of you are doubtlessly going to say, “Hey Bert, I need this rounded to two decimal places and totaled across the entire server. Please do my job for me.” To which I will reply, “I am busy making coffee, please do the needful.” Instead, I’m going to give you a video of me writing this very blog post:

Related links to keep the training going:

I’m not going to schedule these ad-hoc live streams ahead of time – it’s just whenever they fit into my schedule – but if you want to be alerted when I start broadcasting, here’s how – I live stream simultaneously in all of these places:

Previous Post
Announcing Two Free Live Fundamentals Courses This Wednesday & Thursday
Next Post
Want SQL Server Training? Copy/Paste This Email To Your Boss.

14 Comments. Leave new

  • Slightly off topic, but is there a way to use sp_BlitzCache to find which queries are using an Index?

    Reply
    • Slightly, huh? 😉 This isn’t really a good place for tech support for unrelated questions. Hit the readme file for where to get support. Thanks for understanding!

      Reply
  • haha, I have alot of dead databases, getting divide by zero error. Running the query in the cte, I see zero activity. This is a third party application. There are some databases used only to point to their new schema that they implemented in a rewrite to .NET

    Reply
  • Hi Brent,

    I am a big fan you. I love watching your blogs everyday!!!

    There is a glitch in the second query if you run the this query against a database which is not your targeted database .
    This query woks perfectly fine if an object_ID is unique across the database instance ( Which is not true ). For example if we have a same object_ID in more than one database and want to check Reads/writes on any database ( other than Querying database ) by using a where clause (db.name= mydatabase ), it end up showing unintended results. Result shows targeted database name but Reads/Writes for the database against which we ran the query. Below is the result for just one specific object and it is showing same object_ID for 3 databases.

    name table_name index_name reads writes object_id
    tempdb paarInvcHdr PK__paarInvcHdr__1920BF5C 44 2108 373576369
    MyDatabase paarInvcHdr PK__paarInvcHdr__1920BF5C 160 0 373576369
    msdb paarInvcHdr PK__paarInvcHdr__1920BF5C 36648 10 373576369

    we end up getting Index names from targeted database (filtered database ) and Reads/Writes from the databases against which we are running the query.

    Targeted database as well as Querying database must be name same in order to get the correct result. We cannot get the result for all databases by running this query in any particular database.

    Reply
  • My table messed up here after posting!!!

    Reply
  • Your site is very cool, i have bookmarked it.

    Reply
  • Rafael Colon
    April 8, 2020 8:15 am

    Great catch Yogish, I am just happy having a general idea of all databases read and writes on an SQL instance for the moment. I just rounded the results to get two decimal places, like some people asked during the recording of the video and also added a little check to avoid divide by zero when there were no reads for a database, this is not rare but can happen, it happened to me.

    WITH reads_and_writes AS (
    SELECT db.name AS database_name,
    SUM(user_seeks + user_scans + user_lookups) AS reads,
    SUM(user_updates) AS writes,
    SUM(user_seeks + user_scans + user_lookups + user_updates) AS all_activity
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.databases db ON us.database_id = db.database_id
    GROUP BY db.name)
    SELECT database_name, reads,
    ROUND(((reads * 1.0) / all_activity) * 100,2) AS reads_percent,
    writes,
    ROUND(((writes * 1.0) / all_activity) * 100,2) AS writes_percent
    FROM reads_and_writes rw
    WHERE reads > 0 — To avoid divide by Zero
    ORDER BY database_name;

    Reply
  • Andrew Alumkal
    April 8, 2020 12:08 pm

    Hi Brent,
    Not sure how accurate this query is for a couple reasons:
    1) [user_updates] represents number of operations, not number of rows affected. So a statement that updates a million rows shows up as 1 operation.
    2) [user_scans] can be scanning a massive table. According to the query, a seek/scan/lookup all have the same weight – although in terms of data scanned, can be very different.
    So in terms of actual data read/written – this wouldn’t be accurate. Perhaps total logical reads / writes of all plans in the database might be a better fit?

    Reply
  • My opinion is that sometimes you do not need exact information about these type things. Sometimes you only want to see trends and behaviors to have a better idea where things are either for monitoring purposes, policy guidance, or just simple curiosity, like we all know “It depends”. To me ,database administration is not an exact science but a beautiful art.

    Reply
  • Hello Brent,

    How can I know my Load is IO bound or CPU bound. through query

    Reply
  • so for storage testing with Microsoft Disk Spd tool on each logical drives can we use these Read and write percentages ratio specially when upgrade a SQL Server to new Server . Please advice

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}