识别 Microsoft SQL Server 2005 中未使用的对象 [英] Identifying Unused Objects In 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.

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

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.

在六个月或其他时间结束时，检查表的内容.自从我开始监控以来，表中列出的任何内容都没有被引用过，备份和删除可能是安全的.

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?

推荐答案

在 SQL Server 2005 中，您可以使用动态管理视图 sys.dm_db_index_usage_stats.名称表示索引"，但这有点误导 - 每个表在这里都有一个条目，即使它没有任何索引.这是 SQL Magazine 中的一个有用的查询:

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:

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

这是原始文章:

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

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

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

这篇关于识别 Microsoft SQL Server 2005 中未使用的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！