Post reply

Rebuild and organize indexes based on page_count

  • February 19, 2013 at 1:58 am

    #268876

    Hi all,

    I have a job weekly, it runs on Sunday. The purpose is REBUILD/REORGANIZE all indexes in my database.

    If avg_fragmentation_in_percent > 5 and <=30% Then REORGANIZE

    If avg_fragmentation_in_percent > 30% Then REBUILD

    However, I saw the several indexes are not impacted by this job. I know that SQL engine will not do anything in case page_count of an index is small. But does anyone know what threshold of page_count SQL Engine will use to rebuil/reorganize indexes?

    In my case, I have a index

    TableName |IndexName | avg_fragmentation_in_percent |page_count

    ProductPromotion |PK_ProductPromotion | 58.33333333 | 12

    Thanks,

  • February 19, 2013 at 2:12 am

    #1589072

    The SQL engine has no such threshold (well, other than it can't rebuild a 1 page index). Even with 2 it will run the rebuild/reorg. Probably won't have much if any effect, but it will run it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply