<

Querying Database Usage (SQL Server 2005+)

Published on
9,343 Points
3,343 Views
Last Modified:
Approved
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 

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

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

Open in new window

Confirm that there are no indexes on the table:

sp_helpindex 'heap'

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

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

Open in new window


And what about retrieving data?

select * from heap

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

Open in new window


Clean up:

use master
go
drop database heap_db

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.
0
Comment
Author:Matt Bowler
  • 2
2 Comments
 
LVL 10

Author Comment

by:Matt Bowler
Pardon my ignorance Mark - but how do I put the code into code blocks?
0
 
LVL 10

Author Comment

by:Matt Bowler
Thanks for your help Mark :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month