How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"

Question

I have a very large database with hundreds of tables, and after many, many product upgrades, I'm sure half of them aren't being used anymore. How can I tell if a table is is actively being selected from? I can't just use Profiler - not only do I want to watch for more than a few days, but there are thousands of stored procedures as well, and profiler won't translate the SP calls into table access calls.

The only thing I can think of is to create a clustered index on the tables of interest, and then monitor the sys.dm_db_index_usage_stats to see if there are any seeks or scans on the clustered index, meaning that data from the table was loaded. However, adding a clustered index on every table is a bad idea (for any number of reasons), as isn't really feasible.

Are there other options I have? I've always wanted a feature like a "SELECT trigger", but there are probably other reasons why SQL Server doesn't have that feature either.

SOLUTION:

Thanks, Remus, for pointing me in the right direction. Using those columns, I've created the following SELECT, which does exactly what I want.

  WITH LastActivity (ObjectID, LastAction) AS    (        SELECT object_id AS TableName,               last_user_seek as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())         UNION         SELECT object_id AS TableName,               last_user_scan as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())         UNION        SELECT object_id AS TableName,               last_user_lookup as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())   )   SELECT OBJECT_NAME(so.object_id) AS TableName,          MAX(la.LastAction) as LastSelect     FROM sys.objects so     LEFT     JOIN LastActivity la       on so.object_id = la.ObjectID    WHERE so.type = 'U'      AND so.object_id > 100 GROUP BY OBJECT_NAME(so.object_id) ORDER BY OBJECT_NAME(so.object_id) 

Answer 1

Look in sys.dm_db_index_usage_stats. The columns last_user_xxx will contain the last time the table was accessed from user requests. This table resets its tracking after a server restart, so you must leave it running for a while before relying on its data.