Fragmenation Report of all databases in a single report

Here is the Script to generate a report of all fragmenatation indexes in a server and send the report Via mail to your inbox.

You can automate this in SQL job just by adding Reciepients and DB Mail profiles as parameter

SP will send details Report of Fragmented indexes to your Mail, You can Schdeule this job or run on demand to fetch the data.

Based on the report you can analyze the data on weekly basis and set weekly Reindex and Reorganize plans
__________________________________________________________________________

Step1:

Create an SP using Script 1.

Description: SP will fetch the data (Fragmenated Indexes info)from all the databases and send mail to reciepients  

Step2:

Sample Query to get Fragmentation report

exec [USP_Fragmentation_Report] 'hanuman.devineni@gmail.com','DBMailProfile'

--Parameter 1 : Recipient to which you want to send mail

--Parameter 2 : DB Mail Profile Name

Script 1 to create an SP below:


/****** Object:  StoredProcedure [dbo].[USP_Fragmentation_Report]    Script Date: 03/18/2014 03:09:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[USP_Fragmentation_Report] 

 @To varchar(100),
 @profile varchar(50)

 AS
/*************************************************************************** 
-- PROGRAM: Fragmentation Index report 
-- CREATED: 12/18/2013   AUTHOR:  Hanuman D 
-- EXAMPLE: EXEC the script to get the Fragmented indexes {object name} 
-- DESC   : Script will send Defragmented report mail to Reciepients
--                
   
***************************************************************************************************/ 
 
Set Nocount On 
/*-- Query to Check for the temporary table if  exists drops and recreates */ 
 
If exists (select * from tempdb.sys.all_objects where name like '#Temp%' )  
drop table #Temp  
create table #Temp  
(DatabaseName varchar(50),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent
int,IndexType varchar(50),Action_Required varchar(20) default 'NA')  
 
 
/*-- Insert records of fragmented indexes from each user database to temperorary table */ 
insert into #Temp (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)  
 
exec master.sys.sp_MSforeachdb ' USE [?]  
 IF DB_ID(''?'') > 4 
SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,   
  
a.index_id, b.name as IndexName,   
  
round(avg_fragmentation_in_percent,0) asavg_fragmentation_in_percent, index_type_desc  
  
-- , record_count, avg_page_space_used_in_percent --(null in limited)  
  
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a  
  
JOIN sys.indexes AS b   
  
ON a.object_id = b.object_id AND a.index_id = b.index_id  
  
WHERE b.index_id <> 0 and avg_fragmentation_in_percent >50 '  
  
/*  Update the Action column as Reorganize or Reindex based on Fragmentation % */ 
update #Temp  
set Action_Required ='Rebuild'  
where avg_fragmentation_percent >40   
 
 /* Removed as we are getting only for fragmented indexes > 50 
update #Temp  
set Action_Required ='Reorganize'  
where avg_fragmentation_percent <30 and avg_fragmentation_percent >14  
 
*/ 
 
/*Query to exclude the data for readonly databases*/ 
 
delete from #Temp where DatabaseName in (select name from sys.databases where is_read_only <> 0) 
 
/*-- Query to Capture information and send mail in HTML format*/ 
 
 
DECLARE @xml NVARCHAR(MAX) 
DECLARE @body NVARCHAR(MAX) 
DECLARE @Subject NVARCHAR(300)
DECLARE @rowcount int
 
 
SET @Subject='Fragmentation Index Report for Server '+(select @@servername)+'' 
/* Query to check count of temp table and send mail if greater than 0  */
SELECT @rowcount = COUNT(*) FROM #Temp
IF (@rowcount  > 0)
BEGIN
    
SET @xml = CAST(( SELECT [DatabaseName] AS 'td','',[ObjectName] AS 'td','', 
       [Index_id] AS 'td','', [indexName] AS 'td','',[avg_fragmentation_percent] AS 'td','',[IndexType] 'td','',
[Action_Required] AS 'td' 
FROM  #Temp Where databasename not like '%FP%' and databasename not like '%reportserver%' and databasename not like
'%_test%' and databasename <>'TestData' and databasename <>'profiler' and databasename not like '%FYE%'
ORDER BY DatabaseName 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 
 
 
SET @body = 
N'<H4>Fragmented Indexes on MMI server '+ (select td = (select @@servername))+'>50%</H4>'+ 
N'<html><body><table border = 1> <FONT SIZE=2> 
<tr bgcolor=#F8F8FD> 
<th> <FONT SIZE=2>DatabaseName </FONT></th> <th><FONT SIZE=2> ObjectName </FONT></th> <th><FONT SIZE=2>
Index_id</FONT></th> <th><FONT SIZE=2> IndexName</FONT></th><th><FONT SIZE=2>
avg_fragmentation_percent</FONT></th><th><FONT SIZE=2> IndexType </FONT></th>
<th><FONT SIZE=2>Action_Required</FONT></th></tr>'     
 
 
SET @body = @body + @xml +'</FONT></table></body></html>' 
 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = @Profile, -- replaces with your SQL Database Mail Profile  
@body = @body, 
@body_format ='HTML', 
@recipients = @To, -- replaces with your email address 
@subject = @Subject ; 
 
END
ELSE
BEGIN
  
   Set @body='NO Fragmented indexes on '+(select @@servername)+' server'
  
   EXEC msdb.dbo.sp_send_dbmail 
@profile_name = @Profile, -- replaces with your SQL Database Mail Profile  
@body = @body, 
@body_format ='HTML', 
@recipients = @To, -- replaces with your email address 
@subject = @Subject ;
END