如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西 [英] How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"

问题描述

我有一个包含数百个表的非常大的数据库，经过多次产品升级后，我确信其中一半不再使用.如何判断是否正在主动选择表?我不能只使用 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.

这篇关于如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！