If I were granted two ‘wishes’ for SQL Server meta-data they would be:
- The addition of a created datetime column on sys.indexes. The addition of this column would make it trivial to figure out when an index was added (or re-added) to a database. As it stands now, there’s simply NO way to determine this. (Which makes troubleshooting some distinct edge-cases very hard.)
- The addition of a last_used datetime column on sys.objects – to keep track of the last time that code (a sproc, view, or udf) was used, or the last time that a structure (table or index) was last accessed.
I assume that adding and populating a created column to sys.indexes wouldn’t be too hard to implement. It should just require a tiny bit of meta-data to be populated when initially creating indexes or statistics.
On the other hand, I’m guessing that keeping the data in a last_used column in sys.objects would add some overhead into ordinary options (such that it would probably make sense to only allow it to operate when a specific Trace Flag was put into play). This would be especially true if the purpose of this new column would be to keep the last_updated information truly up-to-date instead of using it to keep ‘rough’ details on when an object was last used.
That said, I’m also fairly confident that many DBAs would be happy to have such a feature built-in to the SQL Server engine – simply because it would provide a huge benefit when it comes to trying to track down non-used code (sprocs, views, udfs, and even triggers) as well as non-used tables and indexes.
Finding _POTENTIALLY_ Non-Used Objects in SQL Server
The internet is chock-full of different approaches to finding rarely/non-used code, tables, and indexes. Most of the approaches you’ll find to carry out these tasks online are creative – in that they have to work around the fact that there truly is no DEFINITIVE meta-data kept by SQL Server on when an object was last used.
And, of course, the idea is that if something hasn’t been used in say x days, or n months, then it’s probably safe to remove from production in most environments.
By and large, the approaches used to create lists of objects that haven’t been used or accessed in a while typically take one of two forms:
- Querying the SQL Server Plan Cache, Buffer, or other meta-data exposed by the SQL Server Engine to get a list of objects that are either in memory (or which have been in memory) over a given period of time to contrast against the ‘full list’ of objects and thereby determine which objects may NOT have been accessed recently.
- Using SQL Server Profiler (or SQL Server Extended Events and/or Auditing) to ‘trace’ or watch a SQL Server for a given amount of time, and then correlate a list of recently used/accessed objects against a full list of objects in a given database to get a feel for what hasn’t been used in a while.
Sadly, neither approach can really provide a definitive list of non-used objects with any degree of certainty – which is why it’s best to think of the lists that these approaches provide as being a list of POTENTIALLY non-used objects.
Dealing with _POTENTIALLY_ non-used Objects
Which, in turn, is why all good advice that you’ll find online (or in print) about trying to find/remove non-used code and data in target databases always urges caution in removing objects that you think aren’t being used.
Because, while it’s tempting to want to just send objects ‘straight to hell’ by DROPing them when you’re SURE they’re non-used, the reality is that some views, indexes, stored procedures, and so on might actually ONLY ever be used quarterly, bi-annually, yearly, or potentially even less-frequently. But their ‘sudden’ removal from a production system can really stymie reporting and other efforts.
As such, I typically advocate the following approach to gradually removing objects from a production system.
First, try renaming non-used objects. Many DBAs will simply rename non-used objects to zzzObjectName – which makes the ‘sort’ to the bottom of the list. This works well for tables and all other objects – as it keeps definitions and permissions intact. And, if you later find that you made a mistake, recovery is pretty easy – just remove the ‘zzz’ prefix and you’re back in business.
Second, keep tabs on when you renamed objects. If the goal is to truly get rid of them, having gobs of tables, views, sprocs, and other objects with a ‘zzz’ (or other) prefix doesn’t de-clutter the database. Instead it’s like telling everyone you’re taking out the trash when you’re just throwing it into a corner. But if you keep tabs on WHEN you renamed objects and then set a calendar reminder (say for 30 days) later to come in and remove a list of object-names, you’ll be giving yourself a buffer/window where end-users and applications may ‘manifest’ with a need to use the object you’ve slated for termination – and you can simply rename it and move on.
Third, when you DO decide to finally DROP the object, you’re doing so for a good cause – to keep your database streamlined. But don’t kid yourself, even the most banal data or most trivial sproc can be a total pain to recreate if you find out 6 months later that the data or code you nuked is needed. Accordingly, set up a folder somewhere (or source code repository) that’s regularly backed up and each time you go to drop ANYTHING, script it first and drop it into a file with the object name and some comments about when and where it came from and so on.
Finding Potentially Non-Used Views
Finally, the thing that started this post was a need to find all NON-VALID sprocs, views, and udfs in an environment where I’m preparing a database for replication. Because when you initially snapshot a database and apply the snapshot to a replicated location, you’ll bump into all sorts of excitement (er, well, pain) when you’ve got older, crufty, views/sprocs/udfs that reference invalid objects – because when the snapshot agent goes to apply your initial snapshot, you’ll bump into syntax errors when trying to create/script the creation of code that references objects that don’t exist. (Funny how that works.)
Consequently, I’m working on a process to identify views, sprocs, and udfs (maybe even triggers) that aren’t valid. And, the way to find these is sadly… tough – because SQL Server has no problem letting invalid objects ‘languish’ after the objects they reference have been changed or modified. But, my thought is that if I can find a list of objects that aren’t regularly used, I can then evaluate them, and mark any for removal as needed.
And the process I take for this is:
- Generate a List of non-recently-used code (by querying the plan cache).
- Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).
- Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.
- But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.
Getting to the Point – Finding Potentially Non-Used Views
At any rate, with that approach defined and in place, I found that finding sprocs and udfs was pretty easy. (For a great overview of some options to do that, see Looking for Unused Stored Procedures in SQL Server 2008 by Glenn Berry (blog|twitter).)
But, when it came to finding views, I had to do a bit more work – and querying of the plan cache, resulting in the following query (that uses similar semantics to Glenn’s approach for finding procs):
USE <dbname> GO SELECT v.name [ViewName] FROM sys.views v WHERE v.is_ms_shipped = 0 EXCEPT SELECT o.Name [ViewName] FROM master.sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t INNER JOIN sys.objects o ON t.objectid = o.object_id WHERE t.dbid = DB_ID() GO
And, again, those views aren’t necessarily non-used. They’re just views that aren’t in the plan cache – which makes it easier for me to know which views to focus my time on (rather than trying to script/ALTER them all).
