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
select
distinct
--'database' = db_name(sddius.database_id),
'table' = so.name
, 'index' = si.name
, 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)
from
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
where
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)
Advertisements

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