Get Poorly Utilized NonClustered Indexes

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