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:
- 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
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'
Leave a Reply