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:
- Top tables by IO writes overall and for a given time window?
- 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.
Exactly what you asked does not seem to be fully achievable
Alternatively, here is what you can do:
- 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
- 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
- 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 🙂