index usage

DECLARE @object_name SYSNAME
SET @object_name = 'dbo.my_table'

SELECT @object_name [object_name]
, i.type_desc
, i.name [index_name]
, c.name [column_name]
, ic.key_ordinal
, s.user_seeks
, s.user_scans
, s.user_lookups
, s.user_updates
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
, s.last_user_update
, s.system_seeks
, s.system_scans
, s.system_lookups
, s.system_updates
, s.last_system_seek
, s.last_system_scan
, s.last_system_lookup
, s.last_system_update
    FROM sys.indexes i
    INNER
    JOIN sys.index_columns ic
  ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
    INNER
    JOIN sys.columns c
  ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
    LEFT
    JOIN sys.dm_db_index_usage_stats s
  ON s.database_id = DB_ID()
AND i.object_id = s.object_id
AND i.index_id = s.index_id
    WHERE i.object_id = OBJECT_ID(@object_name)
    ORDER BY i.index_id
   , ic.key_ordinal
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