On the second Tuesday of each month, the SQL Server universe all comes together to blog about a topic. Known as T-SQL Tuesday and started by Adam Machanic (Blog | @AdamMachanic), this month’s T-SQL Tuesday topic is hosted by friend and fellow MCM Brent Ozar. The theme that Brent has chosen for this month is “SQL Server Bugs & Enhancement Requests”. Specifically, Brent wants us to visit connect.microsoft.com, find an interesting request, and blog about it. Now Brent, this is a fantastic topic for a T-SQL Tuesday. I’m really looking forward to seeing what others blog about. I plan on upvoting those I like to get the attention of the SQL Server team at Microsoft. For what it’s worth, I think that this should be a yearly event to do with T-SQL Tuesday!

My favorite connect item

The connect item that I want to blog about is one that I submitted a few months back. Now, I’ve submitted a few enhancement requests over time (see this link,) and there are two current ones that I’m really keen on. However, Brent only wants us to talk about one, so let’s explore what my favorite one is. It can be found at this link.

Track object last used data

At almost every place that I’ve worked (and many that I’ve consulted at), I’ve been asked to figure out what objects in the database are no longer being used. I’ll bet that you’ve been asked this also. While we can tell when objects were created, it’s not so easy to determine when they were last used.

Now, there are a couple of DMVs that can help us out some. Specifically, sys.dm_exec_procedure_stats will show us information about the procedures that are in the plan cache. In BOL there is some information about what this DMV does:

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.

Another DMV that can be helpful is sys.dm_exec_index_usage_stats, which shows us various types of index operations and when they were last performed. However, this data is only available as long as the database is online – when the database is shutdown or detached (or when the SQL Server service is shutdown), this data is lost.

In both of these DMVs, the data is not persisted. Now, we can persist this data by running a job every minute or so to write this information out to a table. However, this only tells us a little bit of the picture: when procedures and indexes are used. If an index is being used, then the table (or indexed view) that the index belongs to has been utilized.

What is missing is all of the other objects: triggers, functions, non-indexed views, user-defined data-types, etc. In order to look for these, you would have to get an extended event session to look at all of the sql commands going on, and then to parse the code to find out what objects are being used. Yuck.

My proposal

What I have proposed is to add two columns to sys.objects: last_used_time_user and last_used_time_system. Two columns are proposed to be consistent with how the sys.dm_exec_index_usage_stats DMV tracks usage by user or system objects. I also suggest adding these columns to sys.indexes. For sys.indexes, this could just be the max of the appropriate columns from the sys.dm_exec_index_usage_stats DMV.

I would imagine that tracking all of this information would cause a performance hit, so this should not be on all the time. I suggest an ALTER DATABASE command (ALTER DATABASE TRACK OBJECT USAGE = ON/OFF) to control whether this information is being collected (now if they can figure out a way to do this where the overhead is minimal, just ignore this command).

Taking this further?

Would it be advantageous to know whether a column is being used in a table? Do you have a column in a table that you believe is obsolete? Have you left it in the table because you’re afraid of breaking the application somewhere? So how about adding these same columns to sys.columns? I’d suggest another ALTER DATABASE command to control whether column usage is being tracked (ALTER DATABASE TRACK COLUMN USAGE = ON/OFF).

Summary

SQL Server can make it easier for us to know whether objects (or even columns) are being used. Having this information will make it easier for us to eliminate obsolete stuff hanging around. This stuff takes up space (including backups) and increases time for maintenance.