A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project.

My solution to this is to query the sys.dm_db_index_usage_stats DMV, which maintains counters of index usage since the last SQL Server restart.

This query will provide aggregated details of user interactions with all databases on the instance:

select 
db_name(database_id) as 'database'
,sum(user_seeks) as 'user seeks'
,sum(user_scans) as 'user scans'
,sum(user_lookups) as 'user lookups'
,sum(user_updates) as 'user updates'
,max(last_user_seek) as 'last user seek'
,max(last_user_scan) as 'last user scan'
,max(last_user_lookup) as 'last user lookup'
,max(last_user_update) as 'last user update'
from sys.dm_db_index_usage_stats
group by database_id

This query will compare the databases that have recorded index usages with the databases on the instance and return databases that appear to not have been used:

select db_name(database_id) 
from sys.databases 
where database_id not in (select database_id from sys.dm_db_index_usage_stats)
and db_name(database_id) not in ('master','msdb','model','tempdb')

A question that bugged me was – will operations against heaps still be registered in sys.dm_db_index_stats?

So to test this I create a database with a single table with no indexes – i.e. a heap:

create database heap_db
go
use heap_db
go
create table heap(id int, value varchar(255))

Confirm that there are no indexes on the table:

sp_helpindex 'heap'

So let’s put some data in and see what happens:

insert into heap
values (1,'some'),(2,'values')

And see if this registers (I’m filtering to just the database, otherwise on my test instance lots of other noise is returned by this query)

And what about retrieving data?

select * from heap

And the result is a scan as we might expect:

Clean up:

use master
go
drop database heap_db

So that means that queries of sys.dm_db_index_stats will pick up heaps as well as indexed databases and can give us a good idea of database usage.

There are still a couple of caveats:

-The results are only valid from last SQL Server restart.

-Closing a database will clear any rows in the DMV for that database – i.e. autoclose databases might not get picked up correctly.