Post reply

Need help related to indexes

  • July 8, 2010 at 2:49 pm

    #1190508

    You find the exact fragmentation in sys.dm_db_index_physical_stats, in the avg_fragmentation_in_precent column. Check it before and after rebuild to see the change.

    If the fragmentation's not decreasing, there's a common reason: the index is too small. Due to the way SQL assigns the first few pages of a table, rebuilding an index under 20 pages often has no effect. It is commonly recommended not to bother rebuilding indexes under 1000 pages

    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

  • July 9, 2010 at 6:43 am

    #1190763

    Check the page count , if it is near about 1000 as gail suggest, there would be no impact of defragmentation.

    select object_name(object_id),page_count,* from sys.dm_db_index_physical_stats (22,null,null,null,null)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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