Index fragmentation Report

Written by Deepak. Posted in Scripts

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:       S.name 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:       b.name 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),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

  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:

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

   1: EXEC msdb.dbo.sp_send_dbmail  

   2: @profile_name = 'DBA',  

   3: @recipients = 'DBA@abc.com',  

   4: @copy_recipients = 'xyz@abc.com',  

   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'

VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Tags: , ,

Trackback from your site.

Leave a comment

CAPTCHA Image
*

Recent Comments

DotNetShoutout

|

SQL-Articles ยป Script to get data file usage and autogrowth details…

Thank you for submitting this cool story – Trackback from DotNetShoutout…

VidhyaSagar

|

Saeed,
I don’t think so you can stop it in SMO, instead you need to turn off password policy for that login

VidhyaSagar

|

Thanks Ashish, I’ve updated the script.

Ashish

|

I was just searching some trace related articles and gone through your article. Found one incorrect information,
to disable the trace, the command should be
dbcc traceoff (….)
you might have by mistake mentioned it as traceon for disabling as well.

Rating