Query tables by IO stats?

All we need is an easy explanation of the problem, so here it is.

Is there a MSSQLServer query I can run to show me:

  1. Top tables by IO writes overall and for a given time window?
  2. Top tables by IO reads overall and for a given time window?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Exactly what you asked does not seem to be fully achievable
Alternatively, here is what you can do:

  1. Check virtual file IO stats (reads, writes, GB read, written, average reads, writes, etc.) for each database and log files on a server – since server restart

https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Storage%20-%20VirtualFileStats.sql

  1. Use below query to gather info on table usage (how heavy each table is used – inserts, updates, deletes, row locks, page locks, range locks, lookups) inside given user database – since server restart
SELECT 
    s.[name]                            [SchemaName],
    t.[name]                            [TableName],
       
    sum(iop.leaf_insert_count)          [Inserts],
    sum(iop.leaf_delete_count)          [Deletes],
    sum(iop.leaf_update_count)          [Updates],
      
    sum(iop.row_lock_count)             [RowLocks],
    sum(iop.page_lock_count)            [PageLocks],

    sum(iop.row_lock_count) +  sum(iop.page_lock_count)  [Row+Page Locks],

    sum(iop.range_scan_count)           [RangeScans],
    sum(iop.singleton_lookup_count)     [Lookups]
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
    JOIN sys.indexes AS i 
        ON iop.index_id = i.index_id 
        AND iop.object_id = i.object_id
    JOIN sys.tables AS t ON 
        i.object_id = t.object_id 
        AND i.type_desc IN ('CLUSTERED', 'HEAP')
    join sys.schemas s on
        t.schema_id = s.schema_id
group by s.[name], t.name
order by [Row+Page Locks] desc

update:

  1. In monitoring tools (Redgate SQL Monitor, Idera, etc.), you can actually select a time window, and view Top Queries for that time window, sorted by IO metrics (Logical Reads, Logical Writes, CPU, etc.). These queries can hint which tables actually been used, and you can do some calculations / approximations from there

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply