Dealing with fragmentation

Question

Dealing with fragmentation

SeeCoolGuy

SSCrazy

Points: 2902
More actions
April 26, 2007 at 2:38 pm

#173108

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 More actions · Answer 2

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

-- Francisco

John Rowan SSC Guru Points: 56440 More actions · Answer 3

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 More actions · Answer 4

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

-- Francisco