DBCC SHOWCONTIG TABLE AND LEAF

Last Post 11 Dec 2006 08:32 AM by TRACEYSQL. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
11 Dec 2006 07:40 AM
hi im trying to understand DBCC SHOWCONTIG

The index id 1 is a clustered index and index id 2 is non clustered both on same column
ACCT_ID


DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 72
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 90.00% [9:10]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 794.1
- Avg. Page Density (full).....................: 90.19%
DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.0
- 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.....................: 1460.5
- Avg. Page Density (full).....................: 81.96%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

on index id i was wondering why the scan density is on 25.00%

and the pages scanned is only 8

Is it because on non clustered it looks at page level so 8 * 8 is 64 nearly 72...(pages)


I have the breakdown of each printed out i guess i do not understand it...
Logical scan has no out of order pages.
Scan density should be close to 100%
Thank you
TRACEYSQL
New Member
New Member

--
11 Dec 2006 08:32 AM
I know it stores just the column and in clustered stores entire row.

But i still not understanding why its
Pages Scanned 8
and Scan density of 25%

Can you help elaborate a little more....
I can't find any blogs out there..............i seen index id = 2 on some threads but the scan density is 100%
JHunter
New Member
New Member

--
11 Dec 2006 09:30 AM
Tracey,

The index is still quite small so allocation over mixed extents could skew what would seem like an optimal solution.

If a table/or index in this case, is less than 8 pages it will be allocated space in a mixed extent (ie a chunk of 8 pages shared by other objects).

Jamie
TRACEYSQL
New Member
New Member

--
11 Dec 2006 11:36 AM
Stays more or less the same the

DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 72
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 90.00% [9:10]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 794.1
- Avg. Page Density (full).....................: 90.19%
DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.0
- 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.....................: 1460.5
- Avg. Page Density (full).....................: 81.96%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So leaf only reports how many pages there are so 8 * 8 = 64 pages
Clustered reports each row?

You are not authorized to post a reply.

Acceptable Use Policy