Tuesday 27 November 2012

Unused Indexes


A sound indexing strategy is paramount to performance in an OLTP system.  Not having the correct indexes in place can cause unnecessary reads placing additional overhead on, among other things the IO subsystem.  Having said that indexes that are in place but never used cause unnecessary writes during insert and update operations, as well as having to be maintained by expensive REORGANIZATION and REBUILD operations.  This is a lot of unnecessary work SQL Server has to do for little to no additional benefit.  The key with indexing is to cover as many queries in your workload with as few small indexes as possible, which is by no means an easy task.

Below is a script I use to report on unused indexes, the definition of unused indexes in this case uses the sys.dm_db_index_usage_stats DMV where the user_seeks + user_scans + user_lookups = 0.

I exclude Index ID’s 0 and 1 (Heaps and Clustered Indexes respectively) I also exclude Primary Keys.  Now as with any production environment I would never just blindly drop the indexes without investigation always test the effects in a development, QA environment first preferably with a full regression test to get a better picture of the impact and make a more educated decision.

There are a few things that will need changing;

·         @notify_email_operator_name=N'Chris'
·         SET @EmailProfile = ''DBA''
·         SET @EmailRecipient = ''Chris@SQLServer365.co.uk''

You can also obviously change the schedule accordingly to suit your needs.


/*
      -----------------------------------------------------------------
      Get unused indexes and the SQL Statement to drop them
      -----------------------------------------------------------------
    
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------
*/
USE [msdb]
GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Alert - UnusedIndexes')
EXEC msdb.dbo.sp_delete_job @job_name = N'Alert - UnusedIndexes', @delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert - UnusedIndexes',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'This job runs every Monday morning at 07:33 and will email Chris@SQLServer365.co.uk if there are any indexes that are unused (0 reads) in any user database.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'sa',
            @notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Get Unused Indexes]    Script Date: 11/27/2012 14:42:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get Unused Indexes',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'-- Set database context
USE master;
GO

-- Declare variables
DECLARE @EmailProfile VARCHAR(255)
DECLARE @EmailRecipient VARCHAR(255)
DECLARE @EmailSubject VARCHAR(255)
DECLARE @IndexUsageStats INT
DECLARE @IndexCount INT

-- Get Index Usage History Stats Age
SET @IndexUsageStats = (SELECT  DATEDIFF(DAY, sd.crdate, GETDATE()) AS days_history
FROM    sys.sysdatabases sd
WHERE   sd.[name] = ''tempdb'');

-- Set variables
SET @EmailProfile = ''DBA''
SET @EmailRecipient = ''Chris@SQLServer365.co.uk''

-- Drop temporary table if exists
IF OBJECT_ID(''tempDB.dbo.#UnusedIndexes'') IS NOT NULL
    DROP TABLE #UnusedIndexes;
    
-- Create Temporary Table
CREATE TABLE #UnusedIndexes
    (
      DatabaseName VARCHAR(255) ,
      TableName VARCHAR(1000) ,
      IndexName VARCHAR(1000) ,
      IndexID INT ,
      TotalWrites BIGINT ,
      TotalReads BIGINT ,
      [Difference] BIGINT ,
      DropScript VARCHAR(4000)
    );

INSERT INTO #UnusedIndexes
EXEC sp_msforeachdb ''USE [?];
IF ''''?'''' NOT IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''', ''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')
BEGIN
SELECT  DB_NAME(DB_ID()) ,
        OBJECT_NAME(ddius.[object_id]) ,
        i.name AS [IndexName] ,
        i.index_id ,
        user_updates AS [TotalWrites] ,
        user_seeks + user_scans + user_lookups AS [TotalReads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] ,
       ''''USE '''' + ''''['''' + DB_NAME(DB_ID()) + ''''];'''' + '''' IF  EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = '''' + ''''object_id('''' + + '''''''''''''''' + ''''['''' + SCHEMA_NAME(o.[schema_id]) + ''''].'''' + ''''['''' +  OBJECT_NAME(ddius.[object_id]) + '''']'''' + '''''''''''''''' + '''')'''' + '''' AND name = '''' + '''''''''''''''' + i.NAME + '''''''''''''''' + '''')''''      
       + '''' DROP INDEX '''' + ''''['''' + i.name + '''']'''' + '''' ON '''' + ''''['''' + SCHEMA_NAME(o.[schema_id]) + ''''].'''' + ''''['''' + OBJECT_NAME(ddius.[object_id]) + ''''];''''
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
        INNER JOIN sys.objects AS o WITH ( NOLOCK ) ON i.[object_id] = o.[object_id]                                                                                                         
WHERE   OBJECTPROPERTY(ddius.[object_id], ''''IsUserTable'''') = 1 -- Only user tables
        AND ddius.database_id = DB_ID() -- Current Database
        AND ( user_seeks + user_scans + user_lookups ) = 0 -- 0 Reads
        AND i.index_id > 1 -- Exclude clustered indexes and heaps
        AND i.is_primary_key = 0 -- Exclude primary keys
          AND i.is_unique = 0 -- Exclude unique indexes
ORDER BY [TotalReads] ASC;
END
''

-- Check for unused indexes
IF EXISTS ( SELECT  1
            FROM #UnusedIndexes)
    BEGIN
        DECLARE @tableHTML NVARCHAR(MAX);
        SET @tableHTML = N''<style type="text/css">''
            + N''.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} ''
            + N''.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} ''
            + N''body {font-family: Arial, verdana;} ''
            + N''table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} ''
            + N''td{background-color:#F1F1F1; border:1px solid black; padding:3px;} ''
            + N''th{background-color:#99CCFF; border:1px solid black; padding:3px;}''
            + N''</style>'' + N''<table border="1">'' + N''<tr>''
            + N''<th>DatabaseName</th>''
            + N''<th>TableName</th>''
            + N''<th>IndexName</th>''
            + N''<th>IndexID</th>''
            + N''<th>TotalWrites</th>''
            + N''<th>TotalReads</th>''
            + N''<th>Difference</th>''
            + N''<th>DropScript</th>''
            + N''</tr>''
            + CAST(( SELECT td = DatabaseName,
                            '''',
                            td = TableName,
                            '''',
                            td = IndexName,
                            '''',
                            td = IndexID,
                            '''',
                            td = TotalWrites,
                            '''',
                            td = TotalReads,
                            '''',
                            td = [Difference],
                            '''',
                            td = DropScript,
                            ''''                        
                     FROM   #UnusedIndexes
                   FOR
                     XML PATH(''tr'') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N''</table>'';
    
            -- Count Indexes
            SELECT @IndexCount = COUNT(1) FROM #UnusedIndexes;
    
            -- Set subject
            SET @EmailSubject = ''ALERT - '' + CAST(@IndexCount AS VARCHAR(100)) +  '' Unused Indexes found on '' + @@SERVERNAME + '' - have not been used for '' + CAST(@IndexUsageStats AS VARCHAR(100)) + '' days''
          
            -- Email results
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body = @tableHTML, @body_format = ''HTML'';
    END
    GO',
            @database_name=N'master',
            @flags=12
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monday at 07:33',
            @enabled=1,
            @freq_type=8,
            @freq_interval=2,
            @freq_subday_type=1,
            @freq_subday_interval=0,
            @freq_relative_interval=0,
            @freq_recurrence_factor=1,
            @active_start_date=20121123,
            @active_end_date=99991231,
            @active_start_time=73300,
            @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


Enjoy!

Chris

3 comments:

  1. This is handy, ive added it as a metric to red gate monitor. They do have a custom metric on the site but it includes all indexes primary keys etc.

    ReplyDelete
    Replies
    1. Glad you like it Andy! Check out the tables without a Clustered Index and Tables Without a Primary Key posts. They might give you equally as much value :)

      Delete
  2. I've updated this script to exclude unused unique indexes and also improved the drop script so that it includes the USE DatabaseName; for each index making it not only re-runable but instantly usable without having to manually change or add the database context for each database.

    ReplyDelete