How to Identify and Fix Index Fragmentation in SQL Server with T-SQL

				
					---------------------------------------------------------------------------------
--Index fragmentation information/status script - with alter index script
---------------------------------------------------------------------------------
SELECT
	s.[name] AS SchemaName
	, OBJECT_NAME(ix.OBJECT_ID) AS Table_Name
	, ix.[name] AS Index_Name
	, ix.index_id AS Index_ID
	, ixps.index_type_desc AS Index_Type_Desc
	, ixps.avg_fragmentation_in_percent  AS Fragmentation_Percentage
	, ixps.avg_page_space_used_in_percent AS Page_Space_Used_Percentage
	, ixps.page_count AS Page_Count

	/* Conditionally generate ALTER INDEX command to rebuild or reorganize based on fragmentation level*/
	/* IF avg_fragmentation_in_percent above 35 then REBUILD */
	/* IF avg_fragmentation_in_percent between 15 and 35 then REORGANIZE */
	/* ELSE do nothing */

	, CASE
		WHEN ixps.avg_fragmentation_in_percent > 35 THEN  'ALTER INDEX [' + ix.[name] + '] ON [' + s.[name] + '].[' + OBJECT_NAME(ix.OBJECT_ID) + '] REBUILD'
		WHEN ixps.avg_fragmentation_in_percent BETWEEN 15 AND 35 THEN  'ALTER INDEX [' + ix.[name] + '] ON [' + s.[name] + '].[' + OBJECT_NAME(ix.OBJECT_ID) + '] REORGANIZE'
	  END AS Alter_Index_Script
FROM
	sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ixps
	INNER JOIN sys.indexes ix
		ON ix.object_id = ixps.object_id
			AND ix.index_id = ixps.index_id
	INNER JOIN sys.tables t
		ON ix.object_id = t.object_id
	INNER JOIN sys.schemas s
		ON t.schema_id = s.schema_id
WHERE
	ixps.avg_fragmentation_in_percent > 0
	--AND s.name in('SchemaNameHere')
ORDER BY ixps.avg_fragmentation_in_percent DESC

/* The script above retrieves information on index fragmentation for all tables in the current database,
and generates an ALTER INDEX command to rebuild or reorganize the index based on the level of fragmentation.
Indexes with fragmentation above 35% will be rebuilt, while indexes with fragmentation between 15% and 35%
will be reorganized. */

/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio,
then execute the script to view the fragmentation details and ALTER INDEX commands. Once you have reviewed
the information, you can copy and paste the ALTER INDEX commands to rebuild or reorganize the indexes as
needed to reduce fragmentation and improve database performance. */