Rebuild and Reorganizing the index on SQL Server

To improve the performance of SQL Server, we can rebuild and reorganise the indexes based on fragementation.

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time, these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

There are three main steps as given below.

Detecting Fragmentation

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

Reorganizing an Index

To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement. To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.

Rebuilding an Index

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

USE "Databasename"
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'index create memory', 1024
GO
DECLARE @STABLE_NAME VARCHAR(100)
DECLARE @SINDEX_NAME VARCHAR(100)
DECLARE @SOPTION_NAME VARCHAR(100)
DECLARE @Pages Int
DECLARE @DATABASE_NAME VARCHAR(100)

DECLARE Index_List CURSOR FORWARD_ONLY
FOR 
		SELECT 
		OBJECT_NAME(B.OBJECT_ID) as TableName, b.name as Index_Name, 
		CASE 
		WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (FILLFACTOR =80,PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,ONLINE=ON,SORT_IN_TEMPDB = ON,MAXDOP=2)' --FOR ONLINE ADD USE THIS 'REBUILD WITH(ONLINE = ON)'
		ELSE 'REORGANIZE'  
		END AS INDEX_OPTION,
		ps.Page_count as TotalPage
		FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
		INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
		AND ps.index_id = b.index_id
		WHERE ps.database_id = DB_ID() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10
		ORDER BY TotalPage desc 

SELECT @DATABASE_NAME=DB_NAME()
PRINT @DATABASE_NAME

OPEN Index_List

FETCH NEXT FROM Index_List
INTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@Pages
insert_block:
BEGIN TRY
WHILE @@FETCH_STATUS = 0
BEGIN

	EXECUTE MASTER.DBO.Re_Index_Table 	@Db_Name=@DATABASE_NAME  ,	@Table_Name=@STABLE_NAME,	@Index_Name=@SINDEX_NAME,	@Option_Name= @SOPTION_NAME  	

	PRINT @STABLE_NAME +' , ' + @SINDEX_NAME +' , '+ @SOPTION_NAME + ' INDEX COMPLETED'
	FETCH NEXT FROM Index_List
	INTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@Pages
END
END TRY
BEGIN CATCH
    FETCH NEXT FROM Index_List
	INTO @STABLE_NAME, @SINDEX_NAME, @SOPTION_NAME,@Pages
END CATCH
CLOSE Index_List

DEALLOCATE Index_List

For more details:
http://technet.microsoft.com/en-us/library/ms189858.aspx[^]