Agent Jobs

Find Full Scans, User Scans, Seeks Per Database Per Table

Here’s a quick script to show you the User Scans, User Seeks, etc per Database, Per table.

use MyDatabaseName;
set nocount on
--'database' = db_name(sddius.database_id),
'table' =
, 'index' =
, sddius.user_seeks
, sddius.user_scans
, 'last_user_seek' = convert(char, sddius.last_user_seek, 9)
, 'last_user_scan' = convert(char, sddius.last_user_scan, 9)
sys.objects so join sys.indexes si on si.object_id = so.object_id
left outer join sys.dm_db_index_usage_stats sddius on si.index_id = sddius.index_id
db_name(sddius.database_id) is not null
and sddius.database_id > 4
and user_scans > 5
and so.type = 'u' and si.type_desc <> 'heap'
and last_user_scan > (select getdate() - 5)

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s