Optimize index structure: Online nonclustered index defragmentation

In SQL 2005 a lot of statistical information is stored about the indexes in your database. This is the third post in a serie about optimizing your index structure.

Previous posts in this serie:

1. Which indexes are not used in my database? Index usage statistics and disk space usage.
2. Defragmentation clustered indexes.

 
Tables in a database can contain a lot of nonclustered indexes per table. These indexes can get fragmented after a while. This will occur when a lot of records are inserted and deleted. Fragmented indexes will have a negative impact on the performance when these indexes are read.
 
Online index defragmentation can be done in 2 different ways:
1) Alter index statement. Can only be performed in Enterprise edition of SQL Server. 
2) DBCC INDEXDEFRAG. Not supported as of SQL 2008 but it executed on SQL 2005 Standard or Enterprise edition.

Next script is an on line operation which can be executed in SQL 2005 Standard and Enterprise edition. It will have impact on the performance of the database. There for do not schedule this script on peak usage of the system.
 
SET NOCOUNT ON

DECLARE @Table NVARCHAR(512)
DECLARE @Index NVARCHAR(512)
DECLARE @Rebuild NVARCHAR(4000)
DECLARE DB CURSOR FOR

SELECT SO.Name, SI.Name
FROM SYS.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
INNER JOIN SYS.objects SO ON SO.object_id=SYS.dm_db_index_physical_stats.object_id
INNER JOIN SYS.indexes SI ON SI.index_id=SYS.dm_db_index_physical_stats.index_id AND
SI.object_id=sys.dm_db_index_physical_stats.object_id
-- Begin select all nonclustered indexes Index_id = 1
AND SI.Index_id <> 1
-- End select all nonclustered indexes Index_id = 1
WHERE avg_fragmentation_in_percent > 10.0 AND SYS.dm_db_index_physical_stats.index_id > 0
ORDER BY SO.Name

OPEN DB
FETCH NEXT FROM DB INTO @Table, @Index
WHILE @@FETCH_STATUS = 0

BEGIN
SET @Rebuild = 'DBCC INDEXDEFRAG (0,' + @Table + ',' + @Index + ')'

PRINT @Rebuild
EXEC SP_EXECUTESQL @Rebuild
FETCH NEXT FROM DB INTO @Table, @Index

END
CLOSE DB
DEALLOCATE DB
SET NOCOUNT OFF 
 

To run this script on SQL Server 2008 change the syntax of @Rebuild to:
SET @Rebuild = 'ALTER INDEX ' + @Index + ' ON ' + @Table + ' REBUILD WITH (ONLINE = ON)'

On big tables this script can take a while to execute. In the software solutions of my company Exact Software we have Exact Globe and Exact Synergy Enterprise. In these solutions next tables can takes a while to defrag: GBKMUT, BacoAppLog, Absences, ItemNumbers, EBCDataLog, WordReferences, BacoSettings. AMUTAK, AMUTAS, ORSRG, ORKRG, FRSRG, FRKRG, ORHSRG, ORHKRG, FRHSRG, FRHKRG.