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.

   1: SELECT

   2:       db_name(ps.database_id) AS DBName,

   3: AS Schemaname,

   4:       object_name(ps.OBJECT_ID) AS Tablename,

   5:       Index_Description = CASE

   6:                         WHEN ps.index_id = 1 THEN 'Clustered Index'

   7:                         WHEN ps.index_id <> 1 THEN 'Non-Clustered Index'

   8:                           END,

   9: AS Indexname,

  10:       ROUND(ps.avg_fragmentation_in_percent,0,1) AS 'Fragmentation%',

  11:       SUM(page_count*8) AS 'IndexSizeKB',

  12:       ps.page_count

  13: FROM sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS ps

  14: 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

  15: 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

  16: INNER JOIN sys.schemas AS S ON S.schema_Id=O.schema_id

  17: WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent > 60 -- Indexes having more than 60% fragmentation

  18: GROUP BY db_name(ps.database_id),,object_name(ps.OBJECT_ID),CASE WHEN ps.index_id = 1 THEN 'Clustered Index' WHEN ps.index_id <> 1 THEN 'Non-Clustered Index' END,,ROUND(ps.avg_fragmentation_in_percent,0,1),ps.avg_fragmentation_in_percent,ps.page_count

  19: 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:

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

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

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

   1: EXEC msdb.dbo.sp_send_dbmail  

   2: @profile_name = 'DBA',  

   3: @recipients = '',  

   4: @copy_recipients = '',  

   5: @file_attachments = 'C:tempIndex_Report.xls',  

   6: @body = 'Please find the Index Fragmentation Report in Server name',  

   7: @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' 

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *