Optimize index structure: Defragmentation clustered indexes

In SQL 2005 a lot of statistical information is stored about the indexes in your database. This is the second post in a serie about optimizing your index structure.
A clustered indexes determines the physical order of data in a table. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. Fragmentation affects disk performance and the effectiveness of the SQL Server read-ahead manager. The SQL Server read-ahead manager can read less records in a fragmented clustered index. A non fragmented clustered index will maximize the number of records the SQL Server read-ahead manager can retrieve in one disk IO.
With next script you can rebuild your clustered indexes which have a fragmentation of more than 10 %.

Be aware that a rebuild of a clustered indexes will results in a table lock. It is a off line operation. Execute this script when you know that the database is not used be other applications. Depending on the size of your database it can take a while to execute.

To generate the SQL script without rebuilding the clustered index directly, remark:
-- EXEC SP_EXECUTESQL @Rebuild
in the script.

The script can also be used for all non clustered indexes, however rebuilding of non clustered indexes can be done in an online operations. In another post I will make a script to rebuild non clustered indexes online

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 only clustered indexes Index_id = 1
AND SI.Index_id = 1
-- End select only clustered 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 = 'ALTER INDEX ' + @Index + ' ON ' + @Table + ' REBUILD'

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

END
CLOSE DB
DEALLOCATE DB
SET NOCOUNT OFF


Previous post in this serie:
Which indexes are not used in my database? SQL 2005 Index usage statistics and disk space usage.