Post reply

Finding index Fragment details in SQL2000

  • November 2, 2011 at 2:43 am

    #146616

    hi,

    Please advice me the query to find the index fragment details in SQLServer 2000.

    Thanks and Regards,

    Ravi.

  • November 2, 2011 at 2:46 am

    #1402013

    DBCC ShowContig

    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

  • November 2, 2011 at 9:11 am

    #1402209

    Thanks Gail.

    In SQLServer 2005, we will take the avg_fragmentation_in_percent to check whether index needs to be reorganize ore rebuild.

    Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.

    1) In SQLServer 2000,when I run DBCC SHOWCONTIG to check the index fragmentation,

    Based on which value we will consider the index fragmentation in the below

    Logical Scan Fragmentation or Extent Scan Fragmentation or Scan Density ?

    2)What is the recommendation to do index reorganize or index rebuild in SQL2000?

    3)What is the percentage 85.55% indicates, what is the value represents in bracket [] in the below.

    - Scan Density [Best Count:Actual Count].......: 85.55% [5214:6095]

    Please advise me.

    Thanks and Regards,

    Ravi.

  • November 2, 2011 at 9:24 am

    #1402219

    http://msdn.microsoft.com/en-us/library/aa258803%28v=sql.80%29.aspx

    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 4 posts - 1 through 3 (of 3 total)

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