Click here to Skip to main content
12,622,741 members (27,634 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

11K views
4 bookmarked
Posted

Rebuild and Reorganizing the index on SQL Server

, 4 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Improve SQL server performance by rebuild and reorganizing the indexes
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[^]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ram Balak Sharma
Architect Q3 technology
India India
Ram is .Net Architect by profession and passion having 8 year experience. He has extensive experience on Microsoft Development Platform and is also Microsoft Certified Application Developer (MCAD) for Web.
Reach me at rsharma@stackdotnet.com

http://www.stackdotnet.com/
6 Freely avaliable E-Books/

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161128.1 | Last Updated 4 Oct 2011
Article Copyright 2011 by Ram Balak Sharma
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid