Tag Archives: index fragmentation

How to: Get All Databases Index Fragmentation Details

This SQL script will provide you the Index fragmentation details for all databases. It would be helpful while scheduling or determining maintenance task of re-indexing.

This SQL script will provides you all the databases names, object names, index id, index name, average fragmentation percent, index type & action required.

The “Action required column” on the basis of fragmentation value using sys.dm_db_index_physical_stats DMV.

If average fragmentation value is >5 & 30 index should rebuild, those value is less than 5 then no result is require.

IF EXISTS (	SELECT * 
	FROM [tempdb].[dbo].[sysobjects] 
	WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
	DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details] 
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
	[DatabaseName] 					[nvarchar] (100) NULL,
	[ObjectName]					[nvarchar] (100) NULL,
	[Index_id] 						INT,
	[indexName] 					[nvarchar] (100) NULL,
	[avg_fragmentation_percent]		float NULL,
	[IndexType] 					[nvarchar] (100) NULL,
	[Action_Required] 				[nvarchar] (100) default 'NA' 
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlQuery = '
	USE [' + @dbname + '];

	IF EXISTS
	(
		SELECT compatibility_level 
		FROM sys.databases 
		WHERE 
			name  = N'''+ @dbname +'''
			AND compatibility_level >= 90
	)
	BEGIN
		INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details] 
		(
			DatabaseName
			, ObjectName
			, Index_id
			, indexName
			, avg_fragmentation_percent
			, IndexType
		) 
		SELECT 
			db_name() as DatabaseName
			, OBJECT_NAME (a.object_id) as ObjectName
			, a.index_id, b.name as IndexName
			, avg_fragmentation_in_percent
			, index_type_desc 
		FROM 
			sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
			JOIN sys.indexes AS b
		ON 
			a.object_id = b.object_id 
			AND a.index_id = b.index_id 
		WHERE
			b.index_id <> 0 
			AND avg_fragmentation_in_percent <> 0
	END;'
	
	EXEC sp_executesql @sqlQuery
	
FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details] 
SET Action_Required = 'Rebuild' 
WHERE avg_fragmentation_percent >30  
GO 

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details] 
SET Action_Required = 'Reorganize' 
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5 
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details] 
ORDER BY databasename

NOTE: This SQL script will work on both SQL Server 2005, 2008 & 2008R2. It won’t work for SQL 2000 as DMV’s are involved in this script.

Original scripts adopted from : http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Index-a68ed3c5