Circles.png

  until the 2018 eoStar User Forum and Technology Conference (August 13-16)
Click here for details

Rebuilding Indexes

From Wiki-eostar.com
Jump to: navigation, search

How to Rebuild fragmented SQL indexes - Do not run during business hours

When transactions are entered into the eoStar database, index fragmentation can occur causing the information stored in the index to become scattered in the database. Fragmented indexes can have an adverse effect on eoStar's performance. This article will discuss:

  • Identify heavily fragmented indexes
  • Rebuilding a single index
  • Rebuilding a range of indexes


Identifying fragmented indexes

Querying the system view sys.dm_db_Index_Physical_Stats (Not available in SQL 2000) will return a list of all database indexes and the fragmentation (as a %) of each. The example below returns the index name, the table of which the index belongs, index type, and the average fragmentation.


 Select sys.name 'Index Name'
   ,obj.name 'Belongs to table'
   ,stat.Index_Type_Desc
   ,stat.Avg_Fragmentation_In_Percent
 From sys.dm_db_Index_Physical_Stats (db_id(db_name()),null,null,null,null)
   as stat join sys.indexes as sys on stat.Index_Id = sys.Index_Id and stat.Object_id = sys.Object_id
   join sys.objects as obj on obj.Object_id = sys.Object_id
 Where Avg_Fragmentation_In_Percent > 30.00
   and sys.Name is not null
 Order by stat.Avg_Fragmentation_In_Percent Desc


Results

FragmentedIndexes.JPG

Considering that Microsoft recommends rebuilding indexes that are greater than 30 percent fragmented, the indexes in this DB need attention! Which brings us to...


Rebuilding a single index

The syntax for rebuilding an index is simple:


 ALTER INDEX {index_name} ON {Table Name} REBUILD   


Taking from our fragmentation results, this is how we would rebuild the non-clustered DriverNid index on the Reconciliations table.


Alter Index IX_UNCLUSTERED_Reconciliations_DriverNid on Reconciliations Rebuild


Rebuilding a range of indexes

Rather than having to rebuild several fragmented indexes one by one, a cursor can be used to gather and rebuild several indexes at once. The script below first gathers indexes that are greater than 30% fragmented. Next it rebuilds all indexes on participating tables (in other words, if a table containing 5 indexes has at least 1 index fragmented greater than 30%, all 5 indexes will be rebuilt).


SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE TableCursor CURSOR FOR 
	Select 
		sch.name + '.' + obj.name as 'name'
	From 
		sys.dm_db_Index_Physical_Stats (db_id(db_name()),null,null,null,null) as stat 
		join sys.indexes as sys on stat.Index_Id = sys.Index_Id and stat.Object_id = sys.Object_id
		join sys.objects as obj on obj.Object_id = sys.Object_id
		join sys.schemas as sch on obj.schema_id = sch.schema_id
	Where 
		Avg_Fragmentation_In_Percent > 30.00
		and sys.Name is not null
	group by 
		obj.name
		,sch.name
OPEN TableCursor   
	FETCH NEXT FROM TableCursor INTO @Table   
	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'  
		EXEC (@cmd)  
		FETCH NEXT FROM TableCursor INTO @Table   
	END
CLOSE TableCursor   
DEALLOCATE TableCursor  

Consider using a script such as this in a scheduled maintenance plan to keep eoStar running smoothly.