TSQL Tuesday
It’s been a while since I’ve written a T-SQL Tuesday post, mainly because I just wasn’t inspired by the topic. And truth be told, this one was turning out to be that way also. But then today, I was wishing that SQL Server would track something that it doesn’t do, and I realized that I had a potential post here. Since this is a couple of days past the deadline, I reached out to Chris Yates (the host for this month) (b/t), and he told me to just bring it on, so here goes…

The theme this month is SQL Gifts… at least that’s what I’m calling it. Chris says:

With that said let’s get this party started and have some fun with it this month shall we. Treat yourself to a birthday gift and come up with a present for yourself SQL related – no limitations:

  • Is there something you’ve been wanting to do for a long time in SQL, but haven’t done it yet?
  • Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?
  • Maybe you are a consultant and know something many of us don’t as you parachute in and out of shops that a reader can benefit from?
  • Is there a co-worker struggling with something SQL related; here is your chance to share the present (knowledge).

Gift wrap your post for yourself and be innovative with what you write about.

Since I was wishing that SQL Server would do something that it doesn’t, it fits right in.

Determining Unused Objects

Have you ever been trying to clean up a database, and you can’t tell what is being used and what isn’t? My wish is for SQL Server to track the time that objects were last used.

Yes, we have sys.dm_exec_procedure_stats to tell us information about procedures that are in the plan cache. And we have sys.dm_db_index_usage_stats for index related information (and index 0/1 will tell us about the table). But both of these can have data disappear. Procedures when the plan is removed from the plan cache – and if SQL restarts. Index stats are removed if the database goes offline (including restarting SQL), when doing a CREATE with DROP_EXISTING and in SQL 2012 a bug was introduced where they were reset on a ALTER INDEX REBUILD. To handle both of these views, we need some kind of process to periodically query these views, and to update a physical table to track these.

These two views don’t take care of other types of objects – views, functions, triggers, etc. To track these, we need a custom process (for example, an Extended Event session or a server-side trace) that captures all database activity. We then need to periodically query this process and determine the objects being used, and again update a physical table to track these. If you’ve ever tried to get the objects being used from a server-side trace, then you know how much a pain-in-the-butt fun this can be.

It sure would be nice if SQL Server would track this all for us. It would make it so much easier to determine what is being used in the database, and what isn’t.

What I’d like to see is one or two columns added to sys.objects: either last_used_time_user / last_used_time_system, or just last_used_time (for both). Then for that object, when it is used, it updates these datetime columns. This will handle tables, views, procedures, functions, triggers, etc., and pretty much give me exactly what I’m looking for. This data needs to not be reset, just updated with the last time that it was used. And it needs to persist across restarts of the database / instance.

Doing this will add overhead to the system (probably less than if we were to create our own process to do this), so it makes sense to me to have this controlled by an alter database command – something like ALTER DATABASE TRACK_OBJECT_USAGE = ON.

Should this idea be extended?

Sometimes we want to know about whether a column is being used…. is it safe to drop a column? When running a query, the system verifies that all of the columns do exist, so this could potentially be an extension to that process. We could extend this to add these columns to sys.columns also. I imagine that this would create a bit of overhead, so this could also be controlled by another alter database command – something like ALTER DATABASE TRACK_COLUMN_USAGE = ON.

These columns can be added to sys.indexes also, where they will persist after a restart. This would be somewhat similar to the last_user(system)_scan/seek/update/lookup columns in the sys.dm_db_index_usage_stats view, but they won’t disappear unless the index is completely dropped. Since this data is already being tracked for sys.dm_db_index_usage_stats, I don’t know if it would really add that much of an overhead to also track it in sys.indexes – but if so, just toggle this tracking with another alter database command – say ALTER DATABASE TRACK_INDEX_USAGE = ON.

I’ve created a connect item for these suggestions. Go on out and up-vote it if you like this idea.

Caution!

Whether this is implemented or not, however you determine objects that can be potentially removed, proceed cautiously. Ensure that you have considered end-of-period (month/quarter/year) processing where objects might be used just during these times. And always have a plan for bringing these objects back. In the case of tables / columns, you don’t want to lose any data. And in the case of T-SQL coded objects, ensure that you can re-create those if necessary. You don’t want this to be why your company let you go…