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.

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

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)

, , , ,
Previous Post
When Was SQL Server Last Restarted? – Interview Question of the Week #225
Next Post
How to List All Memory Optimized Tables in SQL Server? – Interview Question of the Week #227

Related Posts

8 Comments. Leave new

  • I am lucky that we restart the servers once a month so I use your method to tell me both what tables get used and how every month. Actually got this from Tim Ford.

    Reply
  • Typo: MAX(ius.[last_user_update]) AS [last_user_seek]

    Reply
  • Hi Pinal,
    Very helpful query.
    But is returning just the tables on dbo schema, there is some way to get the tables stats on each schema ?

    Best regards,
    Andres

    Reply
  • Andres,

    What Pinal is doing is using the fact that once a table/index is accessed it will show up in sys.dm_db_index_usage_stats. It shouldn’t matter what the schema is.

    HTH

    Chris

    Reply
  • Is there something similar that tells the last time a procedure or function was executed?

    Reply
  • Great query! Is there something similar that identifies the last time a procedure or function is called?

    Reply
  • jennifer hounshell
    October 25, 2019 6:13 pm

    Hi Dave- Great info but would it possible to add the login or host name as well to this script?

    Reply

Leave a Reply

Menu