DMV Queries - 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')


ċ
udf_GetPoorNCIndexes.sql
(2k)
Andy Hughes,
Jul 1, 2012, 1:00 PM
Comments