Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table/view are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance.
In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.
To REBUILD all the indexes, the following syntax is sufficient –
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = Any number between 0 to 100);
Before attempting to REBUILD, we should first find the level of fragmentation using the below tsql –
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), NULL, NULL , 'DETAILED')
REBUILD IF > 30% framgmented
REORGANIZE IF >5 % and < 30% framgmented
I have prepared the following TSQL to rebuild all the indexes on the all the tables and all the indexed views of the database in use.
DECLARE @tsql NVARCHAR(MAX)
DECLARE @fillfactor INT
SET @fillfactor = 90
SELECT @tsql =
STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
INNER JOIN sysindexes i
ON o.id = i.id
o.xtype IN ('U','V')
AND i.name IS NOT NULL
FOR XML PATH('')), 1,1,'')
--PRINT @tsqlEXEC sp_executesql @tsql