September 22, 2003 at 3:33 pm
Here is an output from a DBCC ShowContig for one of my database servers before and after defragmenting.
What is of interest to me are the extent switches. They went down by more than half from 3727189 to 1685214.
My table size has remained the same. If the extents were cut by half shouldn't my table size, as shown
by sp_spaceused also be cut by half? BTW, my database is on a single file. At the end of the defrag command
it showed it returned just ~2000 pages. I would think it would retun all the extents that had been freed.
TABLE level scan performed.
- Pages Scanned................................: 13268057
- Extent Switches..............................: 3727189
- Scan Density [Best Count:Actual Count].......: 44.50% [1658508:3727190]
- Logical Scan Fragmentation ..................: 28.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TABLE level scan performed.
- Pages Scanned................................: 13346408
- Extent Switches..............................: 1685214
- Scan Density [Best Count:Actual Count].......: 99.00% [1668301:1685215]
- Logical Scan Fragmentation ..................: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
September 23, 2003 at 1:17 am
That figure is extent SWITCHES, not number of extents.
It shows the number of times SQL Server had to switch from one extent to another when reading through the table, and therefore the smaller this number the better. In a non-fragmented table, this number will be one less than the total number of extents.
September 23, 2003 at 11:16 am
That sounds reasonable. So, it looks like the pages within the extent were not contiguous and the defrag fixed it to a large extent. Is that true?
September 24, 2003 at 1:18 am
You're right.
If you look at your scan density figures:
- Scan Density [Best Count:Actual Count].......: 99.00% [1668301:1685215]
The best Extent Switches figure you could get would be 1668300 (1668301 - 1), so defrag has managed to do a decent job.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply