February 15, 2006 at 1:37 am
I have a table that after running a DBCC DBREINDEX whos the following results when a DBCC SHOWCONTIG ( SECRETTABLE) WITH TABLERESULTS,ALL_INDEXES
| ObjectName | ObjectId | IndexName | IndexId | Level | Pages | Rows | MinimumRecordSize | MaximumRecordSize | AverageRecordSize | ForwardedRecords | Extents | ExtentSwitches | AverageFreeBytes | AveragePageDensity | ScanDensity | BestCount | ActualCount | LogicalFragmentation | ExtentFragmentation |
| SecretTable | 549576996 | 0 | 0 | 253 | 15172 | 26 | 220 | 121.35300000000001 | 0 | 46 | 45 | 698.69097900390625 | 91.367790222167969 | 69.565217391304344 | 32 | 46 | 99.604743957519531 | 80.434783935546875 |
I don't understand why the extent and logical fragmentation remain so high after a reindex.
This is a 3rd party app and SecretTable is a pseudonym but is it because there are no indexes (clustered or otherwise) on the table?
I have seen something similar with tables where there is a clustered index but I am guessing that the remaining high fragmentation in those cases is something to do with the number of records and the size of those records?
February 15, 2006 at 5:49 am
Any fragmentation within a table itself cannot be removed without a clustered index. If you don't want a clustered index on your table just add one and remove it, your fragmentation will be removed.
Heaps are a bit of a pain this way, they should only be used for pure inserts, otherwise they always fragment eventually.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply