Identifying Unused Objects In Microsoft SQL Server 2005

Question

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.

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?

Answer 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:

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.

Answer 2

is there any option to log sp and data table usage(etc. trigger or something else)