Estimate recent activity in a SQL Server Database

It's a common problem SQL Server (especially) DBAs have: how do I find out when the last time this database was used? SQL Server is so prone to sprawl, and so many applications install so many databases; it's hard to keep track of what is in use and what is not. This is especially true when you're new on the job. 

Here we'll look at a quick-and-dirty method for getting a guess at whether a database has been used. This comes with a lot of caveats and cautions, but if you're looking for some kind of evidence that a system has been used, index usage stats are one place to look.


A little background is probably in order. Indexes are used to help the database engine find data. They organize (and sometimes order) the data within the database so that when you ask for something, the engine has an idea as to where it might be. That helps speed things up. The database engine keeps track of some information about the indexes in the database: last user seek, scan, and update, among other things. We can use this information to see if any indexes have been utilized in the recent past.

 You can see one of the problems you might run into with using this method immediately: what if there aren't any indexes to use? Then it won't work. True, too, if a user is accessing data that isn't indexed. Another problem is that index usage stats aren't persisted across SQL Server reboots, so no usage will show up that was prior to the last time SQL Server was restarted.

 So you see, it's not a perfect solution, but it might give you some confirmation of activity (or the lack thereof), instead of just relying on your gut.

 So, to the script:

declare @dbname varchar (100),
@sql varchar (120)
set @dbname='<DBNAME>' -- Change this to match the database you want to check.
set @sql ='use ['+@dbname+'];'
exec (@sql)
select object_name(ius.object_id) as table_name
, ind.name as index_name
, obj.type_desc
, last_user_seek
, last_user_scan
, last_user_update
from sys.indexes ind
join sys.objects obj on ind.object_id=obj.object_id
left join sys.dm_db_index_usage_stats ius on ind.index_id=ius.index_id
where database_id = (DB_ID(@dbname))
and obj.is_ms_shipped=0
order by last_user_seek DESC,
last_user_scan DESC,
last_user_update DESC

In short, what we're doing is listing the index usage stats from the dm_db_index_usage_stats DMV. If the last user seek, scan, and update rows are all NULL for the indexes in the database, then those indexes have not been used by a user session. That would suggest, if it's a well-indexed database, that the database has not been used since SQL Server was started.


On the other hand, if those columns do have dates in them, then you know that at least on that date, a user was querying or modifying indexed data, and you have confirmation that it is in use.