Index maintenance is one of the best “bang for the buck” performance tuning activities a DBA can perform. Due to factors such as page splits and updates to index and branch leaf pages, index become fragmented, resulting the in index data no longer being physically contiguous. Looking at our available options as far as index maintenance goes, there may be some confusion as whether we should be rebuilding our indexes, or performing an index reorganization. It really depends on their degree of fragmentation.
Microsoft recommends rebuilding if fragmentation is over 30%, reorganizing if it’s between 5% and 30% and leaving it alone if fragmentation is below 5%. In SQL Server 2005, you can use the sys.dm_db_index_physical_stats function to view the fragmentation of an index.
This will get detail for all tables in your DB, returning the three key fields first and ordering by most fragmented:
—————————————————————————————-
SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
dm.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
AND i.index_id = dm.index_id
order by avg_fragmentation_in_percent desc
—————————————————————————————-
To reorganize: ALTER INDEX REORGANIZE –reorgs and repacks the leaf pages of the index; this makes DBCC INDEX DEFRAG obsolete.
To rebuild: ALTER INDEX REBUILD WITH (ONLINE = ON) –drops the index and recreates it; this makes
DBCC DBREINDEX and DBCC INDEX REBUILD obsolete.
Rebuild syntax: ALTER INDEX PKtTable1
ON dbo.tTable1
REBUILD
GO
The (ONLINE = ON) operator allows the index to be rebuilt while the underlying data is accessed by other users (new to SQL 2005).
The following script will rebuild indexes for all tables in your database:
—————————————————————————————-
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name = ‘YourDatabaseName’
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
– create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
– SQL 2000 command
–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)
– SQL 2005 command
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
—————————————————————————————-
Now, you may notice that even after rebuilding your indexes, many still show a large percentage of fragmentation. Hint: check out the page_count column of sys.dm_db_index_physical_stats. If it is a small number, SQL Server may have just skipped over this index. This is fine, as fragmentation in small tables/indexes has a minimal hindrance on performance.
