June 27, 2003 at 3:25 am
Well, im confused (have been for year if you ask the wife)
I run this:
DBCC TAB(6,1765581328)
tells me that index 2 for object 1765581328 resides in pages:
142
143
145
ok, so divide it by 8 and I get the extent numbers:
142 = 17, page type 10
143 = 17, page type 2
145 = 18, page type 2
BUT, i run dbcc showcontig('orders','CustomerID') and tells me this:
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8094.0
- Avg. Page Density (full).....................: 0.00%
hmm.. no extent switch? what the? and only 1 page scan?
The page type on dbcc tab is also confusing, what is type 10? as page 142 is 10, and the other two are page type 2. The commands sp_indexes, sp_statistics over the index are close to useless, returning a single row. I am tending to think that "10" is the statistics for the table as returned by the sp_statistics routine.
Refreshing statistics gives me the same old results in all commands run. The sp_ routines seem to be summarising the facts, but for the life of me cant understand the discrepency with show contig.
All confusing I know, any thoughts etc most appreciated..
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 27, 2003 at 3:56 am
well blow me down..
the table is empty, the space usage reported by dbcc tab and dbcc page was still marking the pages as being used by the object. This explains the figure differences between system functions.
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply