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
              and sch.name = 'dbo'
       group by
              obj .name
              ,sch. name
OPEN TableCursor  
       FETCH NEXT FROM TableCursor INTO @Table  
       WHILE @@FETCH_STATUS = 0  
       BEGIN try  
              SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (ONLINE = ON)' 
              EXEC (@cmd ) 
              FETCH NEXT FROM TableCursor INTO @Table
       END try
          begin catch
                      SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REORGANIZE' 
            EXEC (@cmd )
                      FETCH NEXT FROM TableCursor INTO @Table
          end catch
CLOSE TableCursor  
DEALLOCATE TableCursor   

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