Occasionally, it’s possible for us in the world of database administration to clean up some databases, removing old and unused tables or stored procedures. When the developers can’t tell you which tables and procedures they aren’t using any longer—or the database simply has too many tables and procedures to determine that easily—we fall back to the next-best thing: checking access. The following procedures will let you see which tables and procedures have not been accessed since the last time the server restarted.
First, to get the server uptime, I tend to run the following query:
select datepart(year, CURRENT_TIMESTAMP - create_date)-1900 as years, datepart(month, CURRENT_TIMESTAMP - create_date) - 1 as months, datepart(day, CURRENT_TIMESTAMP - create_date) - 1 as days, datepart(hour, CURRENT_TIMESTAMP - create_date) as hours, datepart(minute, CURRENT_TIMESTAMP - create_date) as minutes, datepart(second, CURRENT_TIMESTAMP - create_date) as seconds from sys.databases where name = 'tempdb';
If the server just rebooted, you won’t get the same results as if the thing had been running for the past several months.
Next, let’s check table access since the last reboot:
declare @AccessCutoff int = 10; SELECT SCHEMA_NAME(so.schema_id) AS SchemaName, OBJECT_NAME(ius.object_id) AS TableName, SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed FROM sys.indexes i INNER JOIN sys.dm_db_index_usage_stats ius ON ius.object_id = i.object_id AND ius.index_id = i.index_id INNER JOIN sys.objects so on ius.object_id = so.object_id WHERE ius.database_id = DB_ID() AND DB_NAME(ius.database_id) = DB_NAME(DB_ID()) GROUP BY SCHEMA_NAME(so.schema_id), OBJECT_NAME(ius.object_id) HAVING SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) <= @AccessCutoff ORDER BY TimesAccessed ASC;
It’s important to note that your regular maintenance routines might access these tables automatically, so just because the TimesAccessed is greater than 0 doesn’t mean the table is really in use. This is why I added in a variable to limit the TimesAccessed to some number; in certain cases, I might be able to get away with 0, but other times, I will need to push that up to some non-zero number like 10 or 20.
Finally, let’s look at stored procedures which have not been used lately. I’m adapting code that Derek Dieter wrote to find highly-active stored procedures and doing the opposite: looking for procedures which are not in the cache. Here is my query:
with executedprocedures as ( SELECT OBJECT_SCHEMA_NAME(st.objectid,dbid) as SchemaName, OBJECT_NAME(st.objectid,dbid) as StoredProcedure FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = 'proc' ) select object_schema_name(object_id) as SchemaName, name as TableName from sys.procedures except select SchemaName, StoredProcedure from executedprocedures;
It is vital to note that there may be some tables and procedures which only get accessed periodically. Thus, even if you have a solid uptime result in the first query, you probably don’t want to create a script to take the results and automatically drop tables or procedures. Instead, take the results as the first phase of your investigation. With this technique, you might turn a needle in a haystack search into…well, a needle in a smaller haystack…