October 17, 2005 at 10:59 pm
Hello friends!!
I got some observation regarding DBCC SHOWCONTIG...
I have table (TABLE2) of near @ 20 lack records.....there i have PK....now i copied 100000 records from that table to another table by......
Select * INTO TABLE1 FROM (SELECT TOP 100000 * FROM TABLE2) P
now here in table1 there is no PK...i run DBCC SHOWCONTIG command and got following result....here one thing i observed as i made table table1, there is fillfactor is 0....
TABLE level scan performed.
- Pages Scanned................................: 1979
- Extents Scanned..............................: 249
- Extent Switches..............................: 248
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.60% [248:249]
- Extent Scan Fragmentation ...................: 7.23%
- Avg. Bytes Free per Page.....................: 76.1
- Avg. Page Density (full).....................: 99.06%
Now what i did is, i put PK in table1 and then run command..i got result like..
Again here FILLFACTOR is 0....
- Pages Scanned................................: 1978
- Extents Scanned..............................: 249
- Extent Switches..............................: 248
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.60% [248:249]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.40%
- Avg. Bytes Free per Page.....................: 72.0
- Avg. Page Density (full).....................: 99.11%
Now i again i run command with...
DBCC DBREINDEX ('TABLE1','PK_TABLE1',70)
DBCC SHOWCONTIG ('TABLE1')
And i got....
- Pages Scanned................................: 2778
- Extents Scanned..............................: 350
- Extent Switches..............................: 349
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.43% [348:350]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 2382.7
- Avg. Page Density (full).....................: 70.56%
Actually what i surprised is that values of..
Avg. Bytes Free per Page and Avg. Page Density (full).....what factors make such difference.....even though i run DBCC DBREINDEX.......
Regards,
Papillon
October 18, 2005 at 6:03 am
You ran this:
DBCC DBREINDEX ('TABLE1','PK_TABLE1',70)
Note that last number '70'. That's percent full.
Now look at this:
Avg. Page Density (full).....................: 70.56%
Note that number '70.56%'.
They are the same, each page 70% full.
What did you expect?
-SQLBill
October 18, 2005 at 6:16 am
Hi!!.......
Thanks for your reply...but can u explain it little bit more cause in first two cases fillfactor is 0 but still values are........
Avg. Page Density (full).....................: 99.06%
Avg. Page Density (full).....................: 99.11%
what it means exactly.......
Regards,
Papillon
October 18, 2005 at 7:14 am
I'd expect this. What are you asking?
This is an average figure, over 249 extents or between 1985 and 1992 pages.
An average of only 4.1 bytes difference free per page. 1992 * 4.1 is 8167 (roughly)...
Adding the PK has merely sorted the data according the key. The index is the data (simply)
Before the PK, it would have been a heap created by the insert statements.
This sorting will have changed the data distribution fractionally resulting in one page less being used to store the data, but the same number of extents...
October 18, 2005 at 8:53 am
Fill factor 0 means to fill the pages all the way. You can never get 100% full, so that's why you are seeing a bit over 99% full.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply