How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"
Solution 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.
Solution 2
Re: Profiler, if you monitor for SP:StmtCompleted, that will capture all statements executing within a stored procedure, so that will catch table accesses within a sproc. If not everything goes through stored procedures, you may also need the SQL:StmtCompleted event.
There will be a large number of events so it's probably still not practical to trace over a long time due to the size of trace. However, you could apply a filter - e.g. where TextData contains the name of your table you want to check for. You could give a list of table names to filter on at any one time and work through them gradually. So you should not get any trace events if none of those tables have been accessed.
Even if you feel it's not a suitable/viable approach for you, I thought it was worth expanding on.
Another solution would be to do a global search of your source code to find references to the tables. You can query the stored procedure definitions to check for matches for a given table, or just generate a complete database script and do a Find on that for table names.
Solution 3
For SQL Server 2008 you should take a look at SQL Auditing. This allows you to audit many things including selects on a table and reports to a file or Events Log.
Solution 4
The following query uses the query plan cache to see if there's a reference to a table in any of the existing plans in cache. This is not guaranteed to be 100% accurate (since query plans are flushed out if there are memory constraints) but can be used to get some insights on table use.
SELECT schema_name(schema_id) as schemaName, t.name as tableName,
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
RIGHT JOIN sys.tables t (NOLOCK) ON cast(dm_exec_query_plan.query_plan as varchar(max)) like '%' + t.name + '%'
eugened
My name is Ryan McCauley, and I'm a database/reporting manager for a mid-size cable company. I spent a number of years as a .NET developer (mostly of the VB.NET variety), but now largely focus on T-SQL and the reporting with the Microsoft BI stack. On my own time, I build small apps to help get things done a little better, and have a couple posted at Codeplex: SQL Space Map - if you have some large SQL Server databases and you'd like a visual picture of which tables and indexes are taking up that space, it's the tool for you. SQL Server Contention Monitor - watches as may SQL Servers as you want and alerts you to blocked SPIDs, showing you the block tree (which process is blocking which, an what others are affected). It's still in a pretty alpha-ish phase, but it despite some instability at times, it gets the job done. Check them out and let me know what feedback you have!
Updated on July 08, 2022Comments
-
eugened almost 2 years
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)