dbcc showcontig

[KingSexy182]

Hi,
Ive just created a table, clustered index with fillfactor 70% (as it will be doing thousands of inserts and deletions daily).
After a few thousand inserts, i run dbcc showcontig on the table.

Result:
---------
Table: 'MyTable' (1650104919); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 5460
- Extents Scanned..............................: 686
- Extent Switches..............................: 1885
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 36.21% [683:1886]
- Logical Scan Fragmentation ..................: 13.19%
- Extent Scan Fragmentation ...................: 0.29%
- Avg. Bytes Free per Page.....................: 2083.2
- Avg. Page Density (full).....................: 74.26%

***the Extent switches just balloons out, and scan density drops badly. is this terrible?

Also - What is the main difference between "Logical Scan Fragmentation" & "Extent Scan Fragmentation". Every time, its either one of these values is much higher than the other. What is the best practice, which of the 2 should I be trying to keep fragmentation low?

[skhanal]

Logical scan fragmentation relevant to index pages only and is percentage of out of order pages to total number of pages in index. A page is out of order i the next leaf page pointed by IAM is not same as next page pointed by the page.


Extent scan fragmentation measures fragmentation at extent level.

Both these numbers should be below 10%, so the numbers in your case are not bad.

But I'm not sure why your scan density is so low even with so low extent fragmentation.

[rmiao]

Low scan density is caused by page split, rebuilding clustered index will defrag the table.

[skhanal]

That should have increased extent fragmentation as well but it does not look proportional.

[KingSexy182]

Yes, it is very weird indeed.
After rebuilding indexes, and more inserts i get the same statistics, scan density the main offender.
However I cannot afford to rebuild the indexes daily. so my only other option would be to run index defrag on the actual table.

Or maybe - could it be that my actual harddisk needs defragmenting? after all, i am running this test on my local machine which only has less than 2 GB (6GB Total) of available space, and has never been fragmented for years.
I think, this might explain it.

[skhanal]

That could be it, the datafile itself may be fragmented.

Try stopping the sql server and run disk defrag.

[KingSexy182]

i will most certainly defrag my hd at end close of business. it'll be interesting to find out, if there were any differences.

[KingSexy182]

Is update statistics automatically executed after DBCC INDEXDEFRAG?

[skhanal]

I don't think it does, because INDEXDEFRAG does not reduce the number of pages or extents it only reorders pages.

[KingSexy182]

After Defragmenting my harddisk, and re-doiing the test; the results were noticeable.

DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (1650104919); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 5861
- Extents Scanned..............................: 739
- Extent Switches..............................: 1593
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 45.98% [733:1594]
- Logical Scan Fragmentation ..................: 9.28%
- Extent Scan Fragmentation ...................: 0.81%
- Avg. Bytes Free per Page.....................: 2170.9
- Avg. Page Density (full).....................: 73.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


It improved from 1:3 to 1:2 ratio in scan density after a few thousand density.

[rmiao]

Should rebuild clustered index to bring Scan Density close to 100%.

[KingSexy182]

thanks all.

[KingSexy182]

stumbled across this in technet:

Physical Disk Fragmentation
Disk fragmentation can contribute to poor performance on some systems. To determine whether disk fragmentation exists, use system tools provided in Microsoft Windows or from third parties to analyze drives on which SQL Server databases reside.

On small-scale environments with more conventional I/O subsystems, it is recommended that you correct disk fragmentation before running index defragmentation tools.

On large-scale environments that benefit from more intelligent disk subsystems, such as SAN (storage area networks) environments, correcting disk fragmentation is not necessary