DMV_Queries - Get Index Usage

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        28 Jun 2012
                24 Jun 2013 - ChillyDBA - Added an SP version that takes Database Name as a parameter

Description

Retrieves a list of all indexes in a database (including those that are not used and tables without indexes) along with the number of updates and hits on the indexes.
 
Alternatively, there is another function that returns data only on indexes in a database that are not used

Code

Function:

DROP FUNCTION dbo.udf_GetIndexUsage
Go

CREATE FUNCTION dbo.udf_GetIndexUsage()
RETURNS @udf_IndexUsage TABLE
  
(
      
DatabaseName    SYSNAME NULL,
      
TableName       SYSNAME NULL,
      
IndexName       SYSNAME NULL,
      
IndexType       VARCHAR(50) NULL,
      
Index_MB_Used   INT NULL,
      
Index_Hits      INT NULL,
      
Index_Updates   INT NULL
   )
AS
/***************************************************************
Purpose:   Lists the index usage for all indexes in a DB (including
           those not used).
           Also includes a rough index size (to help in determining
           cosy/benefit of the index)

           The following DMVs are used:
               sys.indexes                 - to retrieve the full list of indexes for the database
               sys.dm_db_index_usage_stats - to examine the usage stats for indexes that
                                             have been used/updated
                                              
          
Author:        ChillyDBA
History:   28 Jun 2012

****************************************************************/

BEGIN
   INSERT
@udf_IndexUsage
      
(
          
DatabaseName,
          
TableName,
          
IndexName,
          
IndexType,
          
Index_MB_Used,
          
Index_Hits,
          
Index_Updates
      
)
      
  
SELECT
      
DB_NAME()                                           AS DatabaseName,
      
o.name                                              AS TableName,
      
si.name                                             AS IndexName,
      
CASE si.index_id
          
WHEN 0 THEN 'Heap - No index on table'
          
WHEN 1 THEN 'Clustered Index'
          
ELSE 'Non-Clustered Index'
      
END                                                 AS IndexType,
       (
          
SELECT used/128
          
FROM sysindexes b
          
WHERE b.name=si.name
          
AND si.index_id = b.indid
      
)                                                   AS Index_MB_Used,
       (
a.user_seeks + a.user_scans + a.user_lookups)      AS Index_Hits,
       (
a.user_updates)                                    AS Index_Updates
  
FROM sys.indexes as si
  
INNER JOIN sysobjects as o on(si.object_id = o.id)
  
LEFT OUTER JOIN sys.dm_db_index_usage_stats a
      
ON (a.object_id = si.object_id and a.index_id = si.index_id)
  
WHERE o.type = 'U'-- exclude system tables
  
ORDER BY
      
o.name,
      
si.name
      
  
RETURN
END


Stored Procedure:


DROP PROCEDURE dbo.usp_GetIndexUsage
GO

CREATE PROCEDURE dbo.usp_GetIndexUsage @DatabaseName  SYSNAME
AS
/***************************************************************
Purpose:   Lists the index usage for all indexes in a DB (including
           those not used).
           Also includes a rough index size (to help in determining
           cosy/benefit of the index)

           The following DMVs are used:
               sys.indexes                 - to retrieve the full list of indexes for the database
               sys.dm_db_index_usage_stats - to examine the usage stats for indexes that
                                             have been used/updated
                                              
          
Author:        ChillyDBA
History:   28 Jun 2012
           24 Jun 2013 - ChillyDBA
               Converted to an SP from a table valued function that takes DatabaseName as a parameter so the SP
               can be created centrally and called for any DB

****************************************************************/

BEGIN
   DECLARE
@Cmd VARCHAR(MAX)
  
SELECT @Cmd = ''
  
  
SELECT @Cmd = @Cmd
  
+ CHAR(13) + CHAR(10) +'USE ' + @DatabaseName
  
+ CHAR(13) + CHAR(10) +''
  
+ CHAR(13) + CHAR(10) +'SELECT'
  
+ CHAR(13) + CHAR(10) +' DB_NAME() AS DatabaseName,'
  
+ CHAR(13) + CHAR(10) +' o.name AS TableName,'
  
+ CHAR(13) + CHAR(10) +' si.name AS IndexName,'
  
+ CHAR(13) + CHAR(10) +' CASE si.index_id'
  
+ CHAR(13) + CHAR(10) +' WHEN 0 THEN ''Heap - No index on table'''
  
+ CHAR(13) + CHAR(10) +' WHEN 1 THEN ''Clustered Index'''
  
+ CHAR(13) + CHAR(10) +' ELSE ''Non-Clustered Index'''
  
+ CHAR(13) + CHAR(10) +' END AS IndexType,'
  
+ CHAR(13) + CHAR(10) +' ('
  
+ CHAR(13) + CHAR(10) +' SELECT used/128'
  
+ CHAR(13) + CHAR(10) +' FROM sysindexes b '
  
+ CHAR(13) + CHAR(10) +' WHERE b.name=si.name'
  
+ CHAR(13) + CHAR(10) +' AND si.index_id = b.indid'
  
+ CHAR(13) + CHAR(10) +' ) AS Index_MB_Used,'
  
+ CHAR(13) + CHAR(10) +' (a.user_seeks + a.user_scans + a.user_lookups) AS Index_Hits,'
  
+ CHAR(13) + CHAR(10) +' (a.user_updates) AS Index_Updates'
  
+ CHAR(13) + CHAR(10) +'FROM sys.indexes as si'
  
+ CHAR(13) + CHAR(10) +'INNER JOIN sysobjects as o on(si.object_id = o.id)'
  
+ CHAR(13) + CHAR(10) +'LEFT OUTER JOIN sys.dm_db_index_usage_stats a '
  
+ CHAR(13) + CHAR(10) +' ON (a.object_id= si.object_id and a.index_id = si.index_id)'
  
+ CHAR(13) + CHAR(10) +'WHERE o.type = ''U''-- exclude system tables'
  
+ CHAR(13) + CHAR(10) +'ORDER BY '
  
+ CHAR(13) + CHAR(10) +' o.name,'
  
+ CHAR(13) + CHAR(10) +' si.name'


  
EXEC (@Cmd)
      
END


Test Code:

SELECT FROM dbo.udf_GetIndexUsage()

EXEC dbo.usp_GetIndexUsage 'master'



 
ċ
udf_GetIndexUsage.sql
(2k)
Andy Hughes,
Jun 28, 2012, 12:11 PM
ċ
usp_GetIndexUsage.sql
(3k)
Andy Hughes,
Jun 24, 2013, 6:56 PM
Comments