在Microsoft SQL Server 2005中识别未使用的对象

It's a trivial task to find out if an object is referenced by something else or not. What I'd like to do is identify whether or not it's actually being used.

找出一个对象是否被其他东西引用是一项微不足道的任务。我想做的是确定它是否真正被使用。

My solution originally involved a combination of a table that held a list of objects in the database and an hourly job.

我的解决方案最初涉及一个表，它包含数据库中的对象列表和每小时的工作。

The job did two things. First, it looked for new objects that had been added to the database since the last run. Secondly, it looked at sql's object cache. If an object in the table was listed in the cache, it was marked off in the table as having been recently "seen" in use.

这项工作做了两件事。首先，它查找自上次运行以来已添加到数据库的新对象。其次，它查看了sql的对象缓存。如果表中的对象列在缓存中，则表中已将其标记为最近“正在使用”。

At the end of a six month period or whatever, the contents of the table were examined. Anything listed in the table that hadn't been seen referenced since I started monitoring were probably safe to backup and remove.

在六个月结束时或其他任何时候，检查表的内容。自我开始监控以来，表中列出的任何未被引用的内容都可以安全备份和删除。

Sure, there is the possibility of objects that are only used, say, once a year or whatever, but it seemed to work for the most part.

当然，有可能只使用一年一次或其他任何东西的物体，但它似乎在大多数情况下起作用。

It was kind of a pain to work with, though.

但是，与之合作是一种痛苦。

There are about a half dozen databases I'm working with, the majority of which have tons of legacy tables on them, which remain long after their original creators moved on to other companies.

我正在使用大约六个数据库，其中大部分都有大量遗留表，这些数据库在原始创作者转移到其他公司之后仍然存在很长时间。

What I'm looking for is a fairly reliable method of keeping track of when an object (table, view, stored procedure, or function) is getting called.

我正在寻找的是一种相当可靠的方法来跟踪何时调用对象（表，视图，存储过程或函数）。

For those of you who currently monitor this sort of thing, what method/code do you use and would you recommend it?

对于那些目前正在监控此类事物的人，你使用什么方法/代码，你会推荐它吗？

1 个解决方案

#1

With SQL Server 2005, you can use the dynamic management view sys.dm_db_index_usage_stats. The name says "index" but that's a little misleading - every table has an entry in here, even if it doesn't have any indexes. Here's a useful query from SQL Magazine:

使用SQL Server 2005，您可以使用动态管理视图sys.dm_db_index_usage_stats。这个名字说“索引”，但这有点误导 - 每个表都有一个条目，即使它没有任何索引。这是SQL Magazine的一个有用的查询：

SELECT 
  t.name AS 'Table', 
  SUM(i.user_seeks + i.user_scans + i.user_lookups) 
    AS 'Total accesses',
  SUM(i.user_seeks) AS 'Seeks',
  SUM(i.user_scans) AS 'Scans',
  SUM(i.user_lookups) AS 'Lookups'
FROM 
  sys.dm_db_index_usage_stats i RIGHT OUTER JOIN 
    sys.tables t ON (t.object_id = i.object_id)
GROUP BY 
  i.object_id, 
  t.name
ORDER BY [Total accesses] DESC

Here's the original article:

这是原始文章：

http://www.sqlmag.com/Article/ArticleID/53878/sql_server_53878.html

Keep in mind that these usage statistics reset when SQL Server restarts.

请记住，SQL Server重新启动时会重置这些使用情况统计信息。

#1

SELECT 
  t.name AS 'Table', 
  SUM(i.user_seeks + i.user_scans + i.user_lookups) 
    AS 'Total accesses',
  SUM(i.user_seeks) AS 'Seeks',
  SUM(i.user_scans) AS 'Scans',
  SUM(i.user_lookups) AS 'Lookups'
FROM 
  sys.dm_db_index_usage_stats i RIGHT OUTER JOIN 
    sys.tables t ON (t.object_id = i.object_id)
GROUP BY 
  i.object_id, 
  t.name
ORDER BY [Total accesses] DESC