Index fragmentation Report

I had to prepare the list of tables having more than 60% fragmentation. I wrote this query to get those information. The below query will give the list of all the fragmented tables in a particular database having more than 60% fragmentation. This will work for SQL 2005 and beyond.

SELECT
      db_name(ps.database_id) AS DBName,
      S.name AS Schemaname,
      object_name(ps.OBJECT_ID) AS Tablename,
      Index_Description = CASE
                        WHEN ps.index_id = 1 THEN 'Clustered Index'
                        WHEN ps.index_id <> 1 THEN 'Non-Clustered Index'
                          END,
      b.name AS Indexname,
      ROUND(ps.avg_fragmentation_in_percent,0,1) AS 'Fragmentation%',
      SUM(page_count*8) AS 'IndexSizeKB',
      ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.object_id = b.object_id AND ps.index_id = b.index_id AND b.index_id <> 0 -- heap not required
INNER JOIN sys.objects AS O ON O.object_id=b.object_id AND O.type='U' AND O.is_ms_shipped=0 -- only user tables
INNER JOIN sys.schemas AS S ON S.schema_Id=O.schema_id
WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent > 60 -- Indexes having more than 60% fragmentation
GROUP BY db_name(ps.database_id),S.name,object_name(ps.OBJECT_ID),CASE WHEN ps.index_id = 1 THEN 'Clustered Index' WHEN ps.index_id <> 1 THEN 'Non-Clustered Index' END,b.name,ROUND(ps.avg_fragmentation_in_percent,0,1),ps.avg_fragmentation_in_percent,ps.page_count
ORDER BY ps.avg_fragmentation_in_percent DESC

I decided to automate this and put it as a job across all the servers. The following needs to be done to schedule it as a job:

Right click on the db you wish to know the fragmentation status – Task – Export data
Choose the source server and dbname and click next
Choose the destination as Excel and specify the path where you want the o/p excel file to be placed
Choose the option ‘Write a query to specify the data to transfer’
Click Next, Next and finally in the Save and execute package page please choose the option ‘Save SSIS package’ instead of execute immediately
Choose file system and mention the path where you want the dtsx file to be saved.
Click finish to save the SSIS package

Now follow the below steps to configure the package as a job:

Step1:
In the 1st step of the job select the type as Operating system (cmdexec) and copy paste the below query: Please change the path of the dtsx file to the path you have saved. In my case i have saved it to C:temp location.

C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /f “C:TempIndex_Fragmentation Report.dtsx” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E

Step2: Use the following command to send an email of the excel sheet containing the fragmentation details of tables

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'DBA',  
@recipients = 'DBA@abc.com',  
@copy_recipients = 'xyz@abc.com',  
@file_attachments = 'C:tempIndex_Report.xls',  
@body = 'Please find the Index Fragmentation Report in Server name',  
@subject = 'Index Fragmentation Report'

Step3: Use the below command as step 3 in the job to delete the excel file once its mailed to the intended recipients.

   1: EXEC master..xp_cmdshell 'del C:tempIndex_Report.xls' 

Index fragmentation Report

Comments

Leave a Reply Cancel reply