Querying Database Usage (SQL Server 2005+)

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 

Select all Open in new window

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')

Select all Open in new window

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))

Select all Open in new window

Confirm that there are no indexes on the table:

sp_helpindex 'heap'

Select all Open in new window

The error message returned from this will tell you that either the table has no indexes or you do not have permissions. I going to assume that if your playing with scripts that you are on a test machine and will have sys admin privileges.

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

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

Select all Open in new window

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)

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
                      where db_name(database_id) = 'heap_db'
                      group by database_id

Select all Open in new window

And what about retrieving data?

select * from heap

Select all Open in new window

And the result is a scan as we might expect:

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
                      where db_name(database_id) = 'heap_db'
                      group by database_id

Select all Open in new window

Clean up:

use master
                      go
                      drop database heap_db

Select all Open in new window

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.

-The DMVs are only available from SQL Server 2005 onwards.