Assessing Internal & External Fragmentation

• DBADave

  SSChampion

  Points: 10962

  More actions

  August 24, 2006 at 8:46 am

  #62032

  I'm writing a script to automate index defragmentation and trend fragmentation growth.  Does my WHERE clause, checking SHOWCONTIG results, look correct.

  -- External Fragmentation - Candidates for INDEXDEFRAG and UPDATE STATISTICS

  FROM DBCC_SHOWCONTIG

  WHERE indexid Not In (0,255)  -- Not a Heap and does not contain Text data

  AND ((LogicalFrag > 10 and LogicalFrag < 15)

        OR (Scandensity < 75 and Scandensity > 60))

  AND CountPages > 500 

  -- External Fragmentation - Candidates for DBREINDEX

  FROM DBCC_SHOWCONTIG

  WHERE indexid Not In (0,255)  -- Not a Heap and does not contain Text data

  AND (LogicalFrag > 14 OR Scandensity < 60)

  AND CountPages > 500 

  -- Internal Fragmentation

  FROM DBCC_SHOWCONTIG

  WHERE indexid > 0 -- Not a Heap

  AND indexid < 255 -- Does not contain Text data

  AND AvgPageDensity < 76

  AND CountPages > 100 -- More then 100 Pages

  Thanks,  Dave

• August 28, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736588

  More actions

  August 28, 2006 at 8:42 am

  #657105

  Not sure about the internal fragmentation. I think that depends on a per-table basis since your index keys will be of widely varying widths.

  For external fragmentation, I think your script is ok. Might check out how Kimberly Tripp does it (http://www.sqlskills.com/resources/conferences/200608_TuningImmersionScripts.zip), she's done a lot of work in this area.

• DBADave

  SSChampion

  Points: 10962

  More actions

  August 28, 2006 at 8:56 am

  #657111

  Thanks Steve.  This helps.

  Dave