When was Table Accessed Last By User? – Interview Question of the Week #226

Question: When was Table Accessed Last By User?

Answer: Every time when I go to the Comprehensive Database Performance Health Check. One of the conversations which we usually do is about creating and removing the indexes. When I run my diagnosis scripts and start analyzing the table, my customers often give their opinion about the importance of the table. Sometimes they say the table is important and often they say that table is not used much.

I totally personally value their opinion very much as there is no better insight than what you learn from the developers and DBA. I often run the following report to help them out as the report lists all the details about the table access.

When you run the following query it will give you essential information about the table seek, table scan, table lookup as well as table update. Looking at the report we can decide which table is the busiest table for your customer and how you can start attacking the busiest table to get maximum performance.

SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek],
MAX(ius.[last_user_update]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
--AND ius.[object_id] = OBJECT_ID('YourTableName')
GROUP BY ius.[database_id], ius.[object_id]

Please note that if any table is not included in this table, that means that the table has absolutely no information collected and also unused. If you want you can also write a different query which lists all the table which are absolutely not used. However, my goal of this query was to list all the tables which are frequently used.

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