Index Fragmentation -Notification Email

Index Fragmentation Notification Script -

As a DBA, you are required to do a frequent maintenance on a database, index re-building is one such task which can hurt the database performance if index fragmentation is not caught on time. Therefore, you are required to check the index fragmentation on each databases, and rebuild or re-organize as needed. The big questions is when do are you required to rebuild or reorganize the indexes?
As per Microsoft recommendation,
avg_fragmentation_in_percent value     Corrective Statement
>5% and <=30%                                     alter index reorganize
>30 and page count > 100                      alter index rebuild

Today, I am going to demo you how you can automate this task so that you can focus on something productive not running a index fragmentation script all the time.

This demo is has multiple steps. We will create a table, stored procedures, and a job to automate this notification script. You are required to know that you know how to set up a job and this demo does not teach you how to set up a job.

Let's Begin!!

Let's create a table to store a result.

USE [Your DB_NAME]
GO
/****** Object:  Table [dbo].[index_fragmentaton]    Script Date: 09/15/2014 03:52:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[index_fragmentaton](
 [DB_Name] [varchar](30) NOT NULL,
 [Table_Name] [varchar](128) NULL,
 [Index_name] [varchar](128) NULL,
 [Fragmentation_PCt] [int] NULL,
 [Script] [varchar](350) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

2.  Stored Procedures to query the result and store it into a table that we just created.

USE [Your DB_NAME]
GO
Create Procedure Index_Maintenance_Script
as
Begin
insert into [Your DB_NAME].[dbo].[index_fragmentaton] ( DB_Name, Table_Name, Index_name, Fragmentation_PCt, Script)
SELECT 'DB_NAME'                  AS Datadase, 
       t.name                          AS Table_Name, 
    ix.name         AS Index_Name,
       ABS(ps.avg_fragmentation_in_percent) AS Fragment_Pct, 
       'ALTER INDEX [' + ix.name + '] ON [' + s.name 
       + '].[' + t.name + '] ' + CASE WHEN ps.avg_fragmentation_in_percent > 40  and ps.page_count >= 100
       THEN 
       'REBUILD' ELSE 'REORGANIZE' END + CASE WHEN pc.partition_count > 1 THEN 
       ' PARTITION = ' + Cast(ps.partition_number AS NVARCHAR(max)) ELSE '' END 
FROM   sys.indexes AS ix 
       INNER JOIN sys.tables t 
               ON t.object_id = ix.object_id 
       INNER JOIN sys.schemas s 
               ON t.schema_id = s.schema_id 
       INNER JOIN (SELECT object_id, 
                          index_id, 
                          avg_fragmentation_in_percent, 
                          partition_number ,
                          page_count
                   FROM   sys.Dm_db_index_physical_stats (Db_id(1), NULL, NULL, 
                          NULL, 
                          NULL)) ps 
               ON t.object_id = ps.object_id 
                  AND ix.index_id = ps.index_id 
       INNER JOIN (SELECT object_id, 
                          index_id, 
                          Count(DISTINCT partition_number) AS partition_count 
                   FROM   sys.partitions 
                   GROUP  BY object_id, 
                             index_id) pc 
               ON t.object_id = pc.object_id 
                  AND ix.index_id = pc.index_id 
WHERE  ps.avg_fragmentation_in_percent > 10 
       AND ix.name IS NOT NULL 
end;

3. Create a job with two steps job.

Step 1: Set up a job to truncate a table and execute a procedure to load the result

 --Running a Procedures

USE [Your DB_NAME]
GO
Truncate Table [dbo].[index_fragmentaton] 
DECLARE @RC int
-- TODO: Set parameter values here.
EXECUTE @RC = [dbo].[Index_Maintenance_Script]

Step 2: Emailing  the Query Result in a Tabular Format

DECLARE @xml NVARCHAR(max) 
DECLARE @body NVARCHAR(max) 

SET @xml = Cast((SELECT [db_name]         AS 'td', 
                        '', 
                        [table_name]      AS 'td', 
                        '', 
                        [index_name]      AS 'td', 
                        '', 
                        fragmentation_pct AS 'td', 
                        '', 
                        [script]          AS 'td' 
                 FROM   [YOUR DB_NAME].[dbo].[index_fragmentaton] 
                 WHERE  fragmentation_pct > 40 
                        AND script LIKE '%REBUILD' 
                 FOR xml path('tr'), elements) AS NVARCHAR(max)) 
SET @body = 
'

Index Fragmentation by Database. DBA needs to Review each index before Rebuilding!

' 
SET @body = @body + @xml + '

 
 Database Name 
 
 Table Name 
 
 Index Name 
 
 Fragmentation Percentage 
 
 Script
 
 EmaiL generated from: Index_Fragmentation_Noficication_Script Job ' 

IF (SELECT Count(*) 
    FROM   [YOUR  DB_NAME].[dbo].[index_fragmentaton] 
    WHERE  script LIKE '%REBUILD') > 0 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @profile_name = 'YOU MAIL PROFILE', 
        -- replace with your SQL Database Mail Profile  
        @body = @body, 
        @body_format ='HTML', 
        @recipients = 'Pbaniya04@gmail.com',-- replace with your email address 
        @subject = 'Index Fragmentation List -DB Instance'; 
  END; 
ELSE 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @profile_name = 'YOUR MAIL PROFILE', 
        -- replace with your SQL Database Mail Profile  
        @body = 'No Index Rebuild Required!!', 
        @body_format ='HTML', 
        @recipients = 'Pbaniya@gmail.com',-- replace with your email address 
        @subject = 'Index Fragmentation List -DB Instance'; 
  END; 

Note: How to Find the Profile Name on your database?

SELECT [profile_id], 
       [name], 
       [description], 
       [last_mod_datetime], 
       [last_mod_user], 
       'EXEC msdb.dbo.sp_send_dbmail  @profile_name = ''' + name 
       + ''',  @recipients = ''pani.gorthi@abc.com'',   @subject = ''Test'',  @body  = ''Message'',    @body_format = ''HTML'';' AS TestSQL 
FROM   [msdb].[dbo].[sysmail_profile] 

Follow the instruction and send your result to me. I hope you find this useful.