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

Question: When did a user last access a table? Index-usage statistics report the latest recorded user seeks, scans, lookups and updates. They provide recent diagnostic history, not a permanent access audit.

During a Comprehensive Database Performance Health Check, customers often tell me which tables they consider important or lightly used. I value that knowledge from developers and DBAs. This report helps us compare it with the activity SQL Server has recorded.

SELECT DB_NAME() AS database_name,
       s.name AS schema_name, t.name AS table_name,
       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_update
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS ius
  ON ius.database_id = DB_ID() AND ius.object_id = t.object_id
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

The report includes schema names and all user tables in the current database, even those with no matching usage row. It aggregates the latest timestamp across the table’s reported indexes. The update column has its own correct name; it isn’t another seek timestamp.

NULL means there is no corresponding recorded timestamp in this observation history. It does not prove the table is unused. The engine clears counters on restart, and database shutdown or detach can also remove entries. Memory-optimized and spatial index use is outside this DMV’s coverage.

Check the engine’s startup time and ask whether the observation period included month-end, scheduled jobs and other infrequent work. A table that supports a monthly task can look idle on an ordinary Tuesday.

These timestamps don’t identify the person who ran a query. “User” separates user-submitted work from internal system operations. Use appropriate auditing if you need a durable record of who accessed what.

I use this as a conversation starter before index changes, not as permission to drop a table or index. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE; earlier versions require VIEW SERVER STATE.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.