Index Usage: What’s Being Used, What’s NOT Being Used

Always trying to figure out “if” and “how much” my indexes are being used.

This posting has the information:

http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Conversely, which indexes are being UPDATED but NEVER used, not mirrored, and is not disabled already (since the system has been up):

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
         inner join sys.objects as o 
            on i.object_id = o.object_id     
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 and
user_updates > 100 and
is_disabled <> 1 and
(USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0
 AND o.is_ms_shipped = 0

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