Index Fragmentation Report

It’s very easy to automate a report to check your index fragmentation levels on your databases. Here is a query that I use to check the fragmentation level before deciding whether or not to act on it.

General recommendation is to REORGANIZE if fragmentation is less than 30% and REBUILD if more than 30%. There is a “suggested action” column on this query that will give you the suggested action and appropriate script. Use it as you see fit. Remember though that you could alter this to generate the code to do the index rebuilds on line if you’re running Enterprise Edition.

Hope it’s useful for someone.

 /**********************************************
 A query to show index fragmentation levels on
 the selected database.
 ***********************************************/
 DECLARE @DatabaseName VARCHAR(8000) = 'DBA'
 DECLARE @Mode VARCHAR(8000) = 'LIMITED' -- Can be LIMITED or DETAILED
 /**********************************************
 Execute some dynamic SQL to fetch the database
 object info. Helps to avoid nasty cursors!
 ***********************************************/
 DECLARE @sql VARCHAR(8000)

IF OBJECT_ID('tempdb..#databaseObjects') IS NOT NULL DROP TABLE #databaseObjects
 CREATE TABLE #databaseObjects
 (
 object_id INT,
 schema_id INT,
 name VARCHAR(8000)
 )
 SET @sql = 'SELECT object_id, schema_id, name FROM ' + @DatabaseName + '.sys.objects WHERE type = ''U'''
 INSERT INTO #databaseObjects
 EXEC(@sql)

IF OBJECT_ID('tempdb..#databaseSchemas') IS NOT NULL DROP TABLE #databaseSchemas
 CREATE TABLE #databaseSchemas
 (
 schema_id INT,
 name VARCHAR(8000)
 )
 SET @sql = 'SELECT schema_id, name FROM ' + @DatabaseName + '.sys.schemas'
 INSERT INTO #databaseSchemas
 EXEC(@sql)

IF OBJECT_ID('tempdb..#databaseIndexes') IS NOT NULL DROP TABLE #databaseIndexes
 CREATE TABLE #databaseIndexes
 (
 object_id INT,
 index_id INT,
 name VARCHAR(8000),
 fill_factor INT
 )
 SET @sql = 'SELECT object_id, index_id, name, fill_factor FROM ' + @DatabaseName + '.sys.indexes'
 INSERT INTO #databaseIndexes
 EXEC(@sql)
 /**********************************************
 Get the fragmentation details based on the user
 selection above.
 ***********************************************/
 SELECT
 DB_NAME(database_id) AS [Database],
 ds.name + '.' + do.name AS [Table],
 di.name AS [IndexName],
 CASE
 WHEN index_type_desc = 'CLUSTERED INDEX' THEN 'Clustered Index'
 WHEN index_type_desc = 'HEAP' THEN 'Heap'
 WHEN index_type_desc = 'NONCLUSTERED INDEX' THEN 'Nonclustered Index'
 END AS [Index Type],
 page_count AS [Page Count],
 avg_fragmentation_in_percent AS [Fragmentation (%)],
 CAST(page_count * 8 /1024.0 as DECIMAL(28,2)) AS [IndexSize (MB)],
 di.fill_factor AS [Fill Factor],
 CASE
 WHEN avg_fragmentation_in_percent < 5 THEN 'No action required'
 WHEN avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REORGANIZE;'
 WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + di.name + ' ON ' + @DatabaseName + '.' + ds.name + '.' + do.name + ' REBUILD;'
 END AS [Suggested Action]
 FROM
 sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),NULL ,NULL ,NULL ,@Mode) i
 JOIN #databaseObjects do ON do.object_id = i.object_id
 JOIN #databaseSchemas ds ON ds.schema_id = do.schema_id
 JOIN #databaseIndexes di ON di.object_id = i.object_id and di.index_id = i.index_id
 ORDER BY
 avg_fragmentation_in_percent DESC
 

Thanks,

Simon

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s