Dealing with fragmentation

  • SeeCoolGuy

    SSCrazy

    Points: 2902

    So I have the following table, and I run it via DBCC DBREINDEX and also via INDEXDEFRAG, but I noticed that the results do not change

    This table has a PK (clustered) and no other separate clustered indexes.

    ------before

    DBCC SHOWCONTIG scanning 'StatHst' table...

    Table: 'StatHst' (992722589); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 5

    - Extents Scanned..............................: 4

    - Extent Switches..............................: 3

    - Avg. Pages per Extent........................: 1.3

    - Scan Density [Best Count:Actual Count].......: 25.00% [1:4]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 75.00%

    - Avg. Bytes Free per Page.....................: 1748.0

    - Avg. Page Density (full).....................: 78.40%

    --------after

    DBCC SHOWCONTIG scanning 'StatHst' table...

    Table: 'StatHst' (992722589); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 5

    - Extents Scanned..............................: 4

    - Extent Switches..............................: 3

    - Avg. Pages per Extent........................: 1.3

    - Scan Density [Best Count:Actual Count].......: 25.00% [1:4]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 75.00%

    - Avg. Bytes Free per Page.....................: 1748.0

    - Avg. Page Density (full).....................: 78.40%

    -- Francisco

  • Lynn Pettis

    SSC Guru

    Points: 442337

    I have seen this on tables with small row counts.  What you don't tell us is how many records in the table, how big is each record, and what is the fill factor?  With that, we could tell you more.

  • SeeCoolGuy

    SSCrazy

    Points: 2902

    ah, ok. 60 rows, and the fill factor for the PK is 90%

    -- Francisco

  • John Rowan

    SSC Guru

    Points: 56440

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

     

    According to Microsoft, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • SeeCoolGuy

    SSCrazy

    Points: 2902

    Hey this is an excellent article. Thanks for providing the link

    -- Francisco

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

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