DBA Utility Belt: Table Access Counts

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

SELECT 
	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 
FROM 
	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
WHERE
	ius.database_id = DB_ID()
	AND DB_NAME(ius.database_id)=DB_NAME(db_id())
GROUP BY 
	DB_NAME(ius.database_id),
	OBJECT_NAME(ius.object_id)
ORDER BY 
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC;
About these ads

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