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)
Thanks for the script. I ran it but I don’t know how to interpreted it.
This is my top 3:
Should I worry?
Thanks for this script and many other scripts !!! Your contribution to the sql server community is priceless- It is great to have people like you in our community.
@Paul Meems- This query gives you an idea how your indexes are being used- whether you are writing more on the indexed table or reading more from the indexes .Obviously If you have more writes than reads and if table has lots of indexes then performance may suffer. I have used this script in our environment and it helped us a lot in identifying the write intensive indexed tables.
Regarding your question- cannot say for sure whether you should worry or not- Are these three read/writes are from the same table? I would suggest you to run the “3 free scripts to instantly improve SQL Server Performance” that Pinal offers and see for yourself what action can you take to improve the performance of indexes.