SQL Tips #8 - List Read and Write Counts per Table

It's often handy to see how frequently each table in a database is being read from and written to - a quick way to spot your hottest tables.

The Query

SELECT OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName,
    OBJECT_NAME(s.object_id) 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
WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = db_ID()
GROUP BY OBJECT_SCHEMA_NAME(s.object_id), OBJECT_NAME(s.object_id)
ORDER BY Writes DESC, Reads DESC

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags