36 Chambers – The Legendary Journeys: Execution to the max!

July 1, 2013

DBA Utility Belt: Table Access Counts

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

The following query tells us how many times a table has been accessed since the last reboot. This is based on sys.dm_db_index_usage_stats, so be aware that this can get reset with a server reboot/service restart or by rebuilding indexes (in 2012; previous versions did not do this).

	DB_NAME(ius.database_id) AS DBName,
	OBJECT_NAME(ius.object_id) AS TableName,
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed 
	sys.indexes i
	INNER JOIN sys.dm_db_index_usage_stats ius
		ON ius.object_id = i.object_id
		AND ius.index_id = i.index_id
	ius.database_id = DB_ID()
	AND DB_NAME(ius.database_id)=DB_NAME(db_id())
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC;
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.


Get every new post delivered to your Inbox.

Join 99 other followers

%d bloggers like this: