SQL Server takeover: What’s really being used?

When you take over management of a SQL Server with existing databases, it can be difficult to figure out which ones are really in use. If there was no monitoring system already in place, how would you figure this out?

Let’s take a look at how we can use SQL Server’s built-in index usage statistics to demystify the actual usage of our databases.

What stats are available to help us?

The index stats we’re going to use to determine database usage all come from the Dynamic Management View (DMV) sys.dm_db_index_usage_stats. If you’ve not used DMVs before, this is a good one to get started with.

There is one small caveat to utilizing this view: It contains statistics calculated only since the last SQL Server restart. Of course, if you’re working on a server that’s not restarted often, this isn’t a big deal. But if it was restarted recently, a one-time check of this DMV won’t answer the question completely. Later in the post, I’ll talk more about how to deal with this sticky issue, but for now, let’s take a look at the data available to us.

There are quite a few columns in the view, but there are just a few that are critical for the task at hand.

• User_Seeks, user_scans, and user_lookups: If you take these two added together, you’ve basically got the number of times the index was utilized by SELECT statements.
• User_Updates: Technet lists this as the number of “updates” by user queries, but goes on to explain later that it’s actually a combined total of UPDATE, INSERT, and DELETE statements that utilized the index — not the number of records affected. If we SUM() these columns, we can get totals for the whole database.
• last_user_seek, last_user_scan, last_user_update, last_user_lookup: These Datetime columns indicating when the index was last used for a seek, scan or update. If we take the MAX() of all these, we can get the last date for each over the entire database.

What’s the fastest way to figure out what’s been used recently?

There are a few more pieces of information that are useful when you’re looking at this, not least of which is the name of the database itself. The DMV only returns the database_id, so we’ll do a simple join to sys.databases to find the name of the database.

If you just want to do a one-time check of the stats, this gist will get the job done for you nicely. If you want longer-term data or regular re-checks, read on to the section below.

How do we keep an eye on this long-term? What about after multiple server restarts?

So if you want to keep an eye on this through multiple restarts or just to see how the numbers change over time, you could dump the output of the query to a table. There are a few different approaches you could take, but I prefer this one:

The process here is pretty simple:

1. Create a table to hold our results. The table is structured in such a way that there can only be one record in the database per table, per server restart.
2. Create a stored procedure that will insert and update records in the above table. The procedure will do all the gymnastics for you, to make sure new records are only created if the server has been restarted or new databases have been added since the last run.
3. Create a view of aggregated results to use in reporting.

Using the table and procedure above, you can do a few new things:

• Create a SQL Server Agent job or Windows scheduled task to run the procedure on a regular basis to keep your statistics fresh.
• Manually run the procedure before any planned server restart, to make sure relatively complete statistics are captured.
• Create reports using SSRS, Power BI, or any other mechanism to regularly review database usage, to see if any databases have fallen out of use and may need to be archived.

If you found this useful, interesting, or infuriating, I’d appreciate you hitting the heart icon below to recommend my post to others. Do you have a change you’d recommend for my approach? Do you do this some other way? I’d love to hear about it. Hit me up with a response here or on Twitter.