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'