SQL Server Index Maintenance


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];

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 (2)

  1. Tony de Lagarde says:

    Jason,

    Great article thanks for it!

Skip to main content