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