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'