如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西
我有一个包含数百个表的非常大的数据库，经过多次产品升级后，我确信其中一半不再使用.如何判断是否正在主动选择表?我不能只使用 Profiler - 我不仅要观察几天以上，而且还有数千个存储过程，而且 Profiler 不会将 SP 调用转换为表访问调用.
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.
我唯一能想到的就是在感兴趣的表上创建一个聚簇索引，然后监控sys.dm_db_index_usage_stats看是否有对聚簇索引的seek或scans，表示已加载表中的数据.但是，在每个表上添加聚集索引是一个坏主意(出于多种原因)，因为这实际上并不可行.
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.
我还有其他选择吗?我一直想要像SELECT 触发器"这样的功能，但可能还有其他原因导致 SQL Server 也没有该功能.
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.
解决方案:
感谢 Remus，为我指明了正确的方向.使用这些列，我创建了以下 SELECT，这正是我想要的.
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)
推荐答案
查看 sys.dm_db_index_usage_stats.last_user_xxx 列将包含上次从用户请求访问表的时间.此表会在服务器重启后重置其跟踪，因此您必须让它运行一段时间，然后才能依赖其数据.
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.
相关文章