mssqltips logo

Automated and Formatted Index Maintenance Reports for SQL Server

By:   |   Updated: 2013-06-03   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Fragmentation and Index Maintenance

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>,
    [email protected]_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,  
    [email protected]_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,  
    [email protected]_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)]', 
    @[email protected], @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)]', 
    @[email protected], 
    @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)]', 
    @[email protected], 
    @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)]', 
    @[email protected], 
    @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:

Fixed Fragmented Index Report


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


Last Updated: 2013-06-03


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, December 19, 2018 - 4:37:53 AM - Klo Back To Top

 Hello,

How can we modifie this in order to be included all the databases of the instance and not only 1 database?

Thank you


Thursday, August 11, 2016 - 10:44:42 AM - James Back To Top

 this tip was awesome - thank you so much!

 


Thursday, August 22, 2013 - 8:20:35 AM - ananda Back To Top

Thanks for sharing these script index optimization. Is it possible Re-organize & Rebuild for all database?

 


Monday, June 03, 2013 - 1:49:01 PM - Josh Back To Top

Everything in this is great. Thanks so much. I only made one change for my needs that might be helpful to others.

 

Your Code:

FROM sys.dm_db_index_physical_stats(db_id(), 0, -1, null, null) dm

My Code:

FROM sys.dm_db_index_physical_stats(db_id(), 0, -1, null, 'detailed') dm

 

Takes longer to figure which indexes are fragmented but forces SQL to take a closer look at them.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools