Automated and Formatted Index Maintenance Reports for SQL Server

Problem

I want to create T-SQL scripts to fix index fragmentation and to send me an email notification after the index rebuilds or reorgs. I know I can create maintenance plans, jobs and use database mail to do this, but how can I customize this process to only return indexes that need maintenance as well as get a report that shows me what index maintenance occurred?

Solution

To be able to create an automated process to fix index fragmentation and to alert us of what has been fixed we have to follow these steps:

Enable Database Mail
Create a Database Mail Account and Profile and associate them with each other
Create a stored procedure to reorganize or to rebuild fragmented indexes
Create SQL Agent jobs to schedule the rebuilds or reorgs

Enabling Database Mail

The commands below enable Database Mail to be used on the server. The commands take effect immediately without a server restart. This will only enable the Database Mail function on the server. The next configuration step needs Database Mail turned on for it to take effect.

USE MASTER
GO
-- Enable Database Mail --
EXEC SP_CONFIGURE 'ADVANCED OPTIONS', 1
RECONFIGURE
GO
SP_CONFIGURE 'DATABASE MAIL XPS', 1;
GO
EXEC SP_CONFIGURE 'ADVANCED OPTIONS', 0
RECONFIGURE
GO

Configuring Database Mail to Send the Reports

If you have any questions about how the parameters needed to setup Database Mail, you can read this article Database Mail Configuration Stored Procedures for some help.

The below code will create an account with the SMTP credentials for the email server, create a profile and then associate the profile with the account. All the parameters below can be filled with your information, they are not fixed values.

You will need to replace these tags in the script below with values for your SMTP server:

SMTP SERVER
SMTP PORT
SMTP USER
SMTP PASSWORD

-- Create a database mail account --
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name    = 'Local Account',
    @description     = 'Local mail account for administrative e-mail.',
    @email_address   = 'SQL Server Account',
    @display_name    = 'SQL Server Maintenance Plan Reports',
    @mailserver_name = <SMTP SERVER>,
    @port            = <SMTP PORT>,
    @username        = <SMTP USER NAME>,
    @password        = <SMTP PASSWORD>
-- Create a database mail profile --
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Local Account Admin',
    @description  = 'Profile used for administrative mail.';
-- Associate a database mail profile with an account --
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name    = 'Local Account Admin',
    @account_name    = 'Local Account',
    @sequence_number = 1;

You can send a test e-mail with the command below to check if this is working. This is a simple example on how to send e-mail using Database Mail. You can explore more options here sp_send_dbmail.

Note the values for @profile_name should match the value used above when creating the profile. Also, you need to replace the <E-MAIL ADDRESS> with the appropriate email address(es). Also, the parameter @copy_recipients is optional and was left commented.

-- Send a test e-mail --
EXEC msdb.dbo.sp_send_dbmail   
    @profile_name = 'Local Account Admin',  
    @recipients   = <E-MAIL ADDRESS>,
    --@copy_recipients = <E-MAIL ADDRESS>  
    @body         = 'Local Account Test',  
    @body_format  = 'HTML',  
    @importance   = 'High',  
    @subject      = 'Database Mail Test'

If you want to remove the Database Mail account and profile you can run these commands.

-- Delete an account from its profile association --
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
    @profile_name = 'Local Account Admin',
    @account_name = 'Local Account';
-- Delete database mail profile --
EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Local Account Admin';
-- Delete database mail account --
EXECUTE msdb.dbo.sysmail_delete_account_sp
    @account_name = 'Local Account';

Creating the Stored Procedures

We are using DMV sys.dm_db_index_physical_stats to obtain the logical fragmentation instead of DBCC SHOWCONTIG. The DMV’s are excellent to collect a lot of information and can be more efficient in some scenarios.

These stored procedure use some HTML tags to format the report that will be sent by e-mail. They’ll execute only for one database specified. If you want to run for all databases, you have to change all the input parameters of DMV sys.dm_db_index_physical_stats to NULL, like this: sys.dm_db_index_physical_stats(null, null, null, null, null)

Reorganize Fragmented Indexes

This stored procedure will collect information about indexes with logical fragmentation percent values between 5 and 30 and page counts above 1000 and reorganize those indexes. Reorganization does not cause performance impacts as much as rebuilds and can be executed every day, but it is recommended that you run this during non-peak hours. The reorganize option does not have any options and will always run online. It will physically reorganize the leaf nodes of the index.

The syntax of this command is: ALTER INDEX ‘INDEX_NAME’ ON ‘OBJECT_NAME’ REORGANIZE

A sample database called DB_Test was created to be used in this script, change this for your use. You could also create this in the master database if you want.

This script contains a validation to not send an email if indexes were not processed. Before you execute this script change the parameters used for sp_send_dbmail (PROFILE NAME, E-MAIL ADDRESS)

USE DB_Test
GO
CREATE PROC [dbo].[sp_Index_Reorganize]  
AS  
BEGIN  
SET NOCOUNT ON
DECLARE 
    @objectid int,
    @indexid int,
    @objectname varchar(150),
    @indexname varchar(150),  
    @indextype varchar(150),
    @avgfragperc_a decimal,
    @avgfragperc_b decimal,
    @msg varchar(MAX),
    -- Fill this parameters to be used in sp_send_dbmail
    @mail_profile_name varchar(50) = '',
    @mail_recipients varchar(50) = '',
    @mail_copy_recipients varchar(50) = ''
  
CREATE TABLE #fraglist (  
   ObjectId int,
   ObjectName varchar(150),  
   IndexId int,
   IndexName varchar(150),  
   IndexType varchar(150),  
   AvgFragPercent_before decimal,
   AvgFragPercent_after decimal
)  
INSERT INTO #fraglist ( ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId )
SELECT ( SELECT UPPER(name)
           FROM sys.objects
          WHERE object_id = dm.object_id ) ObjectName,
       ( SELECT UPPER(name)
           FROM sys.indexes
          WHERE object_id = dm.object_id
            AND index_id = dm.index_id ) IndexName,
       dm.index_type_desc, avg_fragmentation_in_percent, dm.object_id, dm.index_id
  FROM sys.dm_db_index_physical_stats(db_id(), 0, -1, null, null) dm
 WHERE avg_fragmentation_in_percent BETWEEN 5 AND 30
   AND index_id > 0
   AND page_count >= 1000
IF (@@ROWCOUNT = 0)
   RETURN
-- CURSOR FOR FRAGMENTED OBJECTS REASONABLE --
DECLARE cs CURSOR FORWARD_ONLY LOCAL FOR  
SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId
  FROM #fraglist
 ORDER BY ObjectName, IndexName
OPEN cs  
FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  EXEC ('ALTER INDEX [' + @indexname + '] on ' + @objectname + ' REORGANIZE')
    
  FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
END;  
  
CLOSE cs;  
DEALLOCATE cs;  
UPDATE #fraglist
   SET AvgFragPercent_after = avg_fragmentation_in_percent
  FROM #fraglist
       INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), 0, -1, null, null)
          ON object_id = ObjectId
         AND index_id = IndexId
    
-- CURSOR CS_REPORT  
DECLARE cs_report CURSOR FORWARD_ONLY LOCAL FOR
 SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, AvgFragPercent_after
   FROM #fraglist  
  ORDER BY ObjectName, IndexName
  
OPEN cs_report;  
FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
  
SELECT @msg = '<font size=2 align="right"><H4>Below reorganized indexes:</H4><BR>' +   
              'Labels:<BR>'+  
              'FB: Percent of current logical fragmentation<BR>'+  
              'FA: Percent of fixed logical fragmentation<BR>'+  
              'The best values are between 0% and 5%<BR><BR>'+  
              '<table border="1">'+  
              '<tr>'+  
              '<th>Object</th>'+  
              '<th>Index</th>'+  
              '<th>Index Type</th>'+  
              '<th>FB %</th>'+  
              '<font color=blue>'+  
              '<th>FA %</th>'+  
              '</font>'+  
              '</tr>'  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SELECT @msg = @msg +   
                '<tr>'+  
                '<td>'+ @objectname +'</td>'+  
                '<td>'+ @indexname +'</td>'+  
                '<td>'+ @indextype +'</td>'+  
                '<td>'+ CAST(@avgfragperc_b AS VARCHAR(10)) +'</td>'+  
                '<font color=blue>'+  
                '<td>'+ CAST(@avgfragperc_a AS VARCHAR(10)) +'</td>'+  
                '</font>'+  
                '</tr>'  
  
  FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
END;  
  
SELECT @msg = @msg +   
        '</font>'+  
              '</table>'  
  
CLOSE cs_report;  
DEALLOCATE cs_report;  
  
EXEC msdb.dbo.sp_send_dbmail   
    @profile_name    = @mail_profile_name,  
    @recipients      = @mail_recipients,  
    --@copy_recipients = @mail_copy_recipients, -- This is a optimal parameter    
    @body            = @msg,  
    @body_format     = 'HTML',  
    @importance      = 'High',  
    @subject         = 'Reorganized indexes report'  
  
DROP TABLE #fraglist;  
  
END

Rebuild Fragmented Indexes

This script will collect information about indexes with logical fragmentation percent values above 30 and page counts above 1000 and rebuilds those indexes. You can use either the online or offline option when rebuilding the indexes. We can also define the fill factor and others options. This script is using a default fill factor of 90, but you should monitor page splits to determine the correct value for this option. You can get more information about rebuilding indexes in this tip Rebuilding SQL Server indexes.

USE DB_Test
GO
CREATE PROC [dbo].[sp_Index_Rebuild]  
AS  
BEGIN  
SET NOCOUNT ON
DECLARE 
    @objectid int,
    @indexid int,
    @objectname varchar(150),
    @indexname varchar(150),  
    @indextype varchar(150),
    @avgfragperc_a decimal,
    @avgfragperc_b decimal,
    @msg varchar(MAX),  
    -- Fill this parameters to be used in sp_send_dbmail
    @mail_profile_name varchar(50) = '',
    @mail_recipients varchar(50) = '',
    @mail_copy_recipients varchar(50) = ''
   
CREATE TABLE #fraglist (  
   ObjectId int,
   ObjectName varchar(150),  
   IndexId int,
   IndexName varchar(150),  
   IndexType varchar(150),  
   AvgFragPercent_before decimal,
   AvgFragPercent_after decimal
)  
INSERT INTO #fraglist ( ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId )
SELECT ( SELECT UPPER(name)
           FROM sys.objects
          WHERE object_id = dm.object_id ) ObjectName,
       ( SELECT UPPER(name)
           FROM sys.indexes
          WHERE object_id = dm.object_id
            AND index_id = dm.index_id ) IndexName,
       dm.index_type_desc, avg_fragmentation_in_percent, dm.object_id, dm.index_id
  FROM sys.dm_db_index_physical_stats(db_id(), 0, -1, null, null) dm
 WHERE avg_fragmentation_in_percent > 30
   AND index_id > 0
   AND page_count >= 1000
IF (@@ROWCOUNT = 0)
 RETURN
-- CURSOR FOR FRAGMENTED OBJECTS REASONABLE --
DECLARE cs CURSOR FORWARD_ONLY LOCAL FOR  
SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, ObjectId, IndexId
  FROM #fraglist
 ORDER BY ObjectName, IndexName
OPEN cs  
FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  EXEC ('ALTER INDEX [' + @indexname + '] on ' + @objectname + ' REBUILD WITH (FILLFACTOR = 90)')
    
  FETCH NEXT FROM cs INTO @objectname, @indexname, @indextype, @avgfragperc_b, @objectid, @indexid
END;  
  
CLOSE cs;  
DEALLOCATE cs;  
UPDATE #fraglist
   SET AvgFragPercent_after = avg_fragmentation_in_percent
  FROM #fraglist
       INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), 0, -1, null, null)
          ON object_id = ObjectId
         AND index_id = IndexId
    
-- CURSOR CS_REPORT  
DECLARE cs_report CURSOR FORWARD_ONLY LOCAL FOR
 SELECT ObjectName, IndexName, IndexType, AvgFragPercent_before, AvgFragPercent_after
   FROM #fraglist  
  ORDER BY ObjectName, IndexName
  
OPEN cs_report;  
FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
  
SELECT @msg = '<font size=2 align="right"><H4>Below rebuilded indexes:</H4><BR>' +   
              'Labels:<BR>'+  
              'FB: Percent of current logical fragmentation<BR>'+  
              'FA: Percent of fixed logical fragmentation<BR>'+  
              'The best values are between 0% and 5%<BR><BR>'+  
              '<table border="1">'+  
              '<tr>'+  
              '<th>Object</th>'+  
              '<th>Index</th>'+  
              '<th>Index Type</th>'+  
              '<th>FB %</th>'+  
              '<font color=blue>'+  
              '<th>FA %</th>'+  
              '</font>'+  
              '</tr>'  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  SELECT @msg = @msg +   
                '<tr>'+  
                '<td>'+ @objectname +'</td>'+  
                '<td>'+ @indexname +'</td>'+  
                '<td>'+ @indextype +'</td>'+  
                '<td>'+ CAST(@avgfragperc_b AS VARCHAR(10)) +'</td>'+  
                '<font color=blue>'+  
                '<td>'+ CAST(@avgfragperc_a AS VARCHAR(10)) +'</td>'+  
                '</font>'+  
                '</tr>'  
  
  FETCH NEXT FROM cs_report INTO @objectname, @indexname, @indextype, @avgfragperc_b, @avgfragperc_a
END;  
  
SELECT @msg = @msg +   
        '</font>'+  
              '</table>'  
  
CLOSE cs_report;  
DEALLOCATE cs_report;  
  
EXEC msdb.dbo.sp_send_dbmail   
    @profile_name    = @mail_profile_name,  
    @recipients      = @mail_recipients,  
    --@copy_recipients = @mail_copy_recipients, -- This is a optimal parameter    
    @body            = @msg,  
    @body_format     = 'HTML',  
    @importance      = 'High',  
    @subject         = 'Rebuilded indexes report'  
  
DROP TABLE #fraglist;  
  
END

Create Jobs to Execute the Stored Procedures

We can create jobs using the SSMS GUI or T-SQL scripts.

Reorganize Indexes Job

This job will be configured to run every week day at 1:00 AM, but this can be changed to whatever schedule you want.

Remember you have to change the two parameters @ownerlogin and @servername, because I left them filled with my values.

USE [msdb]
GO
DECLARE 
    @jobId BINARY(16),
    @ownerlogin nvarchar(30) = N'NBDOUGLAS\Douglas',
    @servername nvarchar(30) = N'NBDOUGLAS\SQL2008R2',
    @schedule_id int
EXEC  msdb.dbo.sp_add_job 
    @job_name=N'Reorganize Indexes', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=@ownerlogin, @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobserver 
    @job_name=N'Reorganize Indexes', 
    @server_name = @servername
EXEC msdb.dbo.sp_add_jobstep 
    @job_name=N'Reorganize Indexes', 
    @step_name=N'Execute', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=N'use db_test
EXEC dbo.sp_Index_Reorganize', 
    @database_name=N'DB_Test', 
    @flags=0
EXEC msdb.dbo.sp_update_job 
    @job_name=N'Reorganize Indexes', 
    @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N'', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=@ownerlogin, 
    @notify_email_operator_name=N'', 
    @notify_netsend_operator_name=N'', 
    @notify_page_operator_name=N''
EXEC msdb.dbo.sp_add_jobschedule 
    @job_name=N'Reorganize Indexes', 
    @name=N'Reorganize Index Schedule', 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=62, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20130406, 
    @active_end_date=99991231, 
    @active_start_time=10000, 
    @active_end_time=235959, 
    @schedule_id = @schedule_id OUTPUT

Rebuild Indexes Job

This job will be configured to run only on Saturday’s at 1:00 AM. This schedule can be changed as needed. It will only run on Saturday because rebuilding some indexes can cause more of a performance hit on your servers.

Again, remember you have to change the two parameters @ownerlogin and @servername.

USE [msdb]
GO
DECLARE 
    @jobId BINARY(16),
    @ownerlogin nvarchar(30) = N'NBDOUGLAS\Douglas',
    @servername nvarchar(30) = N'NBDOUGLAS\SQL2008R2',
    @schedule_id int
EXEC  msdb.dbo.sp_add_job 
    @job_name=N'Rebuild Indexes', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=@ownerlogin, 
    @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobserver 
    @job_name=N'Rebuild Indexes', 
    @server_name = @servername
EXEC msdb.dbo.sp_add_jobstep 
    @job_name=N'Rebuild Indexes', 
    @step_name=N'Execute', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, 
    @subsystem=N'TSQL', 
    @command=N'use db_test
EXEC dbo.sp_Index_Rebuild', 
    @database_name=N'DB_Test', 
    @flags=0
EXEC msdb.dbo.sp_update_job 
    @job_name=N'Rebuild Indexes', 
    @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N'', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=@ownerlogin, 
    @notify_email_operator_name=N'', 
    @notify_netsend_operator_name=N'', 
    @notify_page_operator_name=N''
EXEC msdb.dbo.sp_add_jobschedule 
    @job_name=N'Rebuild Indexes', 
    @name=N'Rebuild Indexes Schedule', 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=64, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20130406, 
    @active_end_date=99991231, 
    @active_start_time=10000, 
    @active_end_time=235959, 
    @schedule_id = @schedule_id OUTPUT

Sample Output

To illustrate the results that will be received by e-mail, the body of message will contains examples like this:

Sometimes the T-SQL scripts give to us more quick steps

Conclusion

Hopefully getting information about what index maintenance occurred on what indexes will help in your decision making process. Feel free to change the scripts as needed.

Next Steps

Explore more about techniques used here.
More detail about some commands used in this tip can be obtained from:

Douglas P. Castilho

Over 12 years working with databases and software industry, started work with SQL Server in version 7.0 and it now examines new techniques with the 2012 version. Specializes in database, focuses on performance tuning, backup/restore, disaster recovery, database mirroring, healthy check, replication, security, query optimization, and code analysis.

With software industry, has experience with ERP, mobile, ETL, Business Intelligence projects, with SQL Server and Oracle databases. Applications developed in Delphi, VB, C#, .NET, C++ and ExtJS Java.

Douglas has some Microsoft certifications, like MCITP in SQL Server DBA-DEV, MCPD in Windows Developer 3.5 and MCTS in .NET Framework 3.5 and Windows Forms Application. On education, has a MBA in Project Management.