|
|
Posted by Neil on 12/27/07 23:36
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9A13EFC4CFA19Yazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>>> You could run DBCC SHOWCONTIG on some big tables to have a look at
>>> fragmentation. But if you reindex every night, and don't shrink
>>> anything,
>>> the situation should improve.
>>
>> I ran it on the five largest tables in the db (based on the bytes used,
>> not the pages). The Extent Scan Fragmentation for those five tables was:
>> 86%, 70%, 97%, 98%, 74%.
>
> That's quite high. What values do you have for Scan Density and Avg. Page
> Density (full)? Or post the full output for one these tables.
Here's the full output. Will reply to the rest later (am out the door...).
Thanks!
DBCC SHOWCONTIG scanning 'CustomerMerges' table...
Table: 'CustomerMerges' (709733731); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 725
- Extents Scanned..............................: 96
- Extent Switches..............................: 95
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 94.79% [91:96]
- Extent Scan Fragmentation ...................: 86.46%
- Avg. Bytes Free per Page.....................: 404.7
- Avg. Page Density (full).....................: 95.00%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'ImageFilesProcessed' table...
Table: 'ImageFilesProcessed' (1992550332); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1695
- Extents Scanned..............................: 214
- Extent Switches..............................: 213
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.07% [212:214]
- Extent Scan Fragmentation ...................: 70.09%
- Avg. Bytes Free per Page.....................: 375.4
- Avg. Page Density (full).....................: 95.36%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'CustActivity' table...
Table: 'CustActivity' (1006730739); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 270
- Extents Scanned..............................: 38
- Extent Switches..............................: 37
- Avg. Pages per Extent........................: 7.1
- Scan Density [Best Count:Actual Count].......: 89.47% [34:38]
- Extent Scan Fragmentation ...................: 97.37%
- Avg. Bytes Free per Page.....................: 392.2
- Avg. Page Density (full).....................: 95.15%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'CustomerEvents' table...
Table: 'CustomerEvents' (1029734871); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 320
- Extents Scanned..............................: 46
- Extent Switches..............................: 45
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 86.96% [40:46]
- Extent Scan Fragmentation ...................: 97.83%
- Avg. Bytes Free per Page.....................: 376.9
- Avg. Page Density (full).....................: 95.34%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'ImageFileErrors' table...
Table: 'ImageFileErrors' (69067482); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 1936
- Extents Scanned..............................: 243
- Extent Switches..............................: 242
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.59% [242:243]
- Extent Scan Fragmentation ...................: 74.49%
- Avg. Bytes Free per Page.....................: 344.1
- Avg. Page Density (full).....................: 95.75%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
[Back to original message]
|