Share via

SQL Server Index Maintenance

  • Article

After working on a problem where the transaction logs for a large database were filling to sizes larger then the actual database every night it turned out to be a server maintenance plan that rebuilt and reorganized indexes every night.  While working on designing a custom plan that would only rebuild or reorganize when actually necessary I found many references out on the internet.  None of the scripts I found out there really accomplished what I was after.

Starting with finding the indexes that need maintenance done on them I eventually came up with this SQL:

 select 

     sch.name [schema_name]

     ,obj.name [table_name]

     ,idx.name [index_name]

     ,idx.type_desc [index_type]

     ,part.used_page_count*8 [used_kb] --each page is 8k

     ,part.row_count

     ,CONVERT(DECIMAL(9,2),frag.avg_fragmentation_in_percent) [fragmentation]

     from sys.dm_db_partition_stats part

         join sys.objects obj on part.object_id = obj.object_id

         join sys.schemas sch on obj.schema_id = sch.schema_id

         left join sys.indexes idx on part.object_id = idx.object_id and part.index_id = idx.index_id

         left join sys.dm_db_index_physical_stats(db_id(),null,null,null,null) frag on part.object_id = frag.object_id

             and part.index_id = frag.index_id

     where 

         obj.is_ms_shipped = 0

         and frag.page_count > 100

     order by [schema_name],[table_name],[index_name];

 .csharpcode, .csharpcode pre
{
  font-size: small;
   color: black;
   font-family: consolas, "Courier New", courier, monospace;
   background-color: #ffffff;
  /*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt 
{
   background-color: #f4f4f4;
  width: 100%;
    margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This gave me a nice view to see how big the tables were, and where the fragmentation problems were.  We decided that if it's less800KB (based on frag.page_count) we didn't care about the fragmentation.

Armed with this info it was a matter of basically executing the rebuild or reorganize depending on your preference.  I decided to use a in memory table and just did a insert-select into it.  My temp table looked like:

 declare @IndexStatus table(

     schema_name varchar(250)

     ,table_name varchar(250)

     ,index_name varchar(250)

     ,index_type varchar(250)

     ,used_kb int

     ,row_count int

     ,fragmentation decimal(9,2));

From there I just took the results into a cursor and executed dynamic sql.  My logic was <10% fragmentation I didn't care, greater then 30 gets a rebuild, 10-30 gets a reorganize

 declare maintCursor cursor for

     select 'alter index ['+[index_name]+'] on ['+[schema_name]+'].['+[table_name]+']', fragmentation 

         from @IndexStatus 

         where fragmentation > 10;

  

 declare @stmt varchar(max);

 declare @frag float;

  

 open maintCursor

 fetch next from maintCursor into @stmt, @frag

 while @@fetch_status = 0

 begin

     if @frag > 30

         set @stmt = @stmt+' rebuild'; --if this is enterprise you can add 'with (online=on)'

     else

         set @stmt = @stmt+' reorganize';

     

     print (@stmt)

     exec (@stmt)

     fetch next from maintCursor into @stmt, @frag

 end

 close maintCursor

 deallocate maintCursor

Comments