How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251

Question: How can you identify tables with mostly read or write activity? The index-usage DMV gives a useful starting point, but its counters measure index operations, not rows or physical I/O.

A client asked this during a health check. I had used an activity script for other clients and shared it with them. The original four-column summary is still useful when you understand what its totals represent.

SELECT sc.name AS SchemaName, t.name AS TableName,
       SUM(s.user_seeks + s.user_scans + s.user_lookups) AS Reads,
       SUM(s.user_updates) AS Writes
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.tables AS t ON t.object_id = s.object_id
JOIN sys.schemas AS sc ON sc.schema_id = t.schema_id
WHERE s.database_id = DB_ID()
GROUP BY sc.name, t.name
ORDER BY Reads DESC, Writes DESC;

Reads sums seeks, scans and lookups across the table’s index entries. Writes sums user_updates across those entries. One statement updating a thousand rows can increment a given index’s update counter once; maintaining several indexes can contribute several increments to this table-level sum.

That makes Writes an index-maintenance activity total, not a count of distinct write statements. Reads isn’t a count of rows, pages read or physical disk operations either. Don’t turn this quick ranking into a precise read/write percentage for an application.

The counters reset on an engine restart and can disappear for other database lifecycle events. A table with no DMV entry isn’t represented here; that is not proof it will never be used. Collect snapshots over a representative business interval, including less frequent reporting, and compare deltas before changing indexes or classifying a table permanently.

I kept the original output because the four columns make the answer tangible. The query now joins the current database’s catalog explicitly, while keeping its original activity calculation and names.

Previous question: an INNER JOIN that produces a Cartesian product.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.