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

问题描述

我有一个非常大的数据库，有数百个表，经过很多次的产品升级，我确定其中一半没有被使用。我如何判断表是否正在被选中？我不能仅仅使用Profiler - 不仅要观看超过几天，还有数千个存储过程，并且分析器不会将SP调用转换为表访问调用。

我唯一可以想到的是在感兴趣的表上创建一个聚集索引，然后监视 sys.dm_db_index_usage_stats 以查看聚集索引中是否存在任何查找或扫描，这意味着表中的数据已加载。然而，在每个表上添加聚集索引是一个坏主意（由于任何原因），这是不可行的。

有其他选项吗？我一直想要一个像SELECT触发器这样的功能，但也有可能是SQL Server没有这个功能的其他原因。

解决方案：

谢谢Remus，指出我正确的方向。使用这些列，我创建了以下SELECT，它完全符合我的要求。

  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将包含上次从用户请求访问表的时间。服务器重新启动后，此表重置其跟踪，因此您必须在运行一段时间才能依赖其数据。

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)

解决方案

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.

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