DBCC showcontig - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC showcontig

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-11 : 11:10:09

I have the following:

DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 66
- Extents Scanned..............................: 9
- Extent Switches..............................: 8
- Avg. Pages per Extent........................: 7.3
- Scan Density [Best Count:Actual Count].......: 100.00% [9:9]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 130.1
- Avg. Page Density (full).....................: 98.39%
DBCC SHOWCONTIG scanning 'ACCT' table...
Table: 'ACCT' (1048220716); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 2.3
- 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.....................: 512.6
- Avg. Page Density (full).....................: 93.67%

Why would the 2nd one be only 33.33% should this not be close to 10)%

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 11:19:21

What made you think that second output should be similar to first one?

It is scanning two different indexes not the ACCT table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-11 : 11:38:05

Its on one table only ACCT

It has one clustered index and one non clustered index both on ACCT
column

So i was trying to understand why the 2nd one only reads 8 pages and the first 72 and the differences

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-12 : 15:43:52

quote:

So i was trying to understand why the 2nd one only reads 8 pages and the first 72 and the differences

Perhaps because the first one had a TABLE level scan performed, while the second had a LEAF level scan performed?

Ken

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-13 : 09:25:46

What does that mean is it because of LEAF and when you do showcontig
if is the number of blocks...66 / 8 = 8.25 ? but its 7 ?

Trying to understand output

Thanks

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-13 : 14:40:56

I'd suspect that a LEAF scan only examines the index, while a TABLE scan examines the table.

Don't know the answer to why it's 7, though.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources