Get Poorly Utilized NonClustered Indexes
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: Unknown/ChillyDBA
Date: 7 May 2012/1 Jul 2012
Description
Returns a list of non-clustered indexes where the cost of updating the index is greater than the benefit delivered ie #writes > #reads
If a Database Name is supplied then the scope will be restricted to the database, otherwise all locks on the server will be returned.
Code
Function:
DROP FUNCTION dbo.udf_GetPoorNCIndexes
GO
CREATE FUNCTION dbo.udf_GetPoorNCIndexes (@DatabaseName SYSNAME)
RETURNS @PoorNCIndexes TABLE
(
DatabaseName SYSNAME NULL,
TableName SYSNAME NULL,
IndexName SYSNAME NULL,
IndexID INT,
TotalIndexWrites INT,
TotalIndexReads INT,
UsageDifference INT
)
AS
/***************************************************************
Purpose: To retrieve details of all nonclustered indexes where the number of
writes is greater than the number of reads.
These will be underutilised and potentially a performance
problem.
Scope of the results is for the server unless a DatabaseName is provided
The following DMVs are used:
sys.dm_db_index_usage_stats - contains a record of all index usage on the server
Author: Unknown
History: 7 May 2012 - Initial Issue
1 Jul 2012 - ChillyDBA - Converted to a table valued function
****************************************************************/
BEGIN
INSERT @PoorNCIndexes
(
DatabaseName,
TableName,
IndexName,
IndexID,
TotalIndexWrites,
TotalIndexReads,
UsageDifference
)
-- Possible Bad NC Indexes (writes > reads)
SELECT
DB_NAME(s.database_id) AS DatabaseName,
OBJECT_NAME(s.[object_id]) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
user_updates AS TotalIndexWrites,
user_seeks + user_scans + user_lookups AS TotalIndexReads,
user_updates - ( user_seeks + user_scans + user_lookups )
AS UsageDifference
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = ISNULL(DB_ID(@DatabaseName), s.Database_ID)
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY
UsageDifference DESC ,
TotalIndexWrites DESC ,
TotalIndexReads ASC ;
RETURN
END
Test Code:
SELECT * FROM dbo.udf_GetPoorNCIndexes(NULL)
SELECT * FROM dbo.udf_GetPoorNCIndexes ('AdventureworksDW2008R2')