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

Question: How to Determine Read Intensive and Write Intensive Tables in SQL Server?

Answer: I was really asked this question in the recent consulting engagement. What I love most about my job as a Comprehensive Database Performance Health Check is that every single day I get to meet new people and help them with their SQL Server Performance troubles. After working for so many years, I can easily say that I have seen pretty much every single problem out there on this subject. Recently one of my clients asked me that if I can help them to Determine Read Intensive and Write Intensive Tables from their SQL Server database.

Of course, I can. I have already prepared the script for it for other clients many years ago and I was easily able to share the same with them. Let me share the same script with all of you today here as well.

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 Reads DESC, Writes DESC

When you run the above script, it returns the following resultset with the four columns.

The first column stands for the schema name and the second column stands for the table name. The next two columns indicate the number of the reads and writes to the table. Please note that it is quite possible that the query may read 100s of the rows or update 1000s of the rows but the counter of read and write will update with only one number as the DMV tracks the data access in a single number increments.

Let me know what you think of this script. I will be happy to hear from you and if you have any such script, please send me an email and I will be happy to post it on the blog with due credit to you.

Here is the previous blog post in the same series: How to Write INNER JOIN Which is Actually CROSS JOIN? – Interview Question of the Week #250

Reference: Pinal Dave (https://blog.sqlauthority.com)