Fails to mention the fact that INDEXDEFRAG causes fragmentation. INDEXDEFRAG does this by consolidating extents and freeing them in the middle of contiguous extents. In tests I performed, by having an index on a dedicated filegroup I was able to have, at a minimum, 15% fragmentation. Keep this in mind when trying to determine whether your logical extent fragmentation is bad or not. The lowest I could get mine was 15%.

Jeff Fischer 4/14/2004 3:40:29 PM

Hai

In SQL server 2000, for some tables the reorganization % is less than 50 % (ie) scan density is less
(ie) the defragment % is more
Please see the dbcc showcontig for a silimar table below.
I ran the dbcc dbreindex (ValSpecReportsReq,'',0 ) and also with a fill factor of 100
then also the scandensity remains the same. That table is very simple. Just two fields and 1000 rows.
What should I do to make the scandensity increase and to reduce the fragmentation.


DBCC SHOWCONTIG scanning 'ValSpecReportsReq' table...
Table: 'ValSpecReportsReq' (116963543); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.7
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 855.2
- Avg. Page Density (full).....................: 89.43%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks in advance
Soorya

anand3/10/2004 8:40:21 PM

I would like to ask that is there any way by which i can find out table fragmentation.

Mahesh B.Paranjpe 3/3/2004 9:11:38 PM

Useful.

john4/24/2001 2:55:37 PM