Large Index

Is it normal for Index size to be three times the size of its corresponding
table in SQL 2005It is possible to mis-use the index configuration options to force this to
happen - eg, set fillfactor 33, include all columns in the index & it could
easily be ~ 3 times the size of the table..
What's your fill-factor for this index?
Can you post DBCC SHOWCONTIG output for this index?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
> Is it normal for Index size to be three times the size of its
> corresponding table in SQL 2005
>|||DBCC SHOWCONTIG scanning 'DSECURITY' table...
Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7950.0
- Avg. Page Density (full)................: 1.78%
DBCC SHOWCONTIG scanning 'MHINFO' table...
Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'VERINFO' table...
Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
TABLE level scan performed.
- 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]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7389.0
- Avg. Page Density (full)................: 8.71%
DBCC SHOWCONTIG scanning 'ID_TABLE' table...
Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
TABLE level scan performed.
- 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]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7656.0
- Avg. Page Density (full)................: 5.41%
DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7604.0
- Avg. Page Density (full)................: 6.05%
DBCC SHOWCONTIG scanning 'PALETTE' table...
Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 136
- Extents Scanned.......................: 17
- Extent Switches.......................: 16
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7241.5
- Avg. Page Density (full)................: 10.53%
DBCC SHOWCONTIG scanning 'CUSTOM1' table...
Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2385
- Extents Scanned.......................: 299
- Extent Switches.......................: 298
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.33%
- Avg. Bytes Free per Page................: 7265.2
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7574.0
- Avg. Page Density (full)................: 6.42%
DBCC SHOWCONTIG scanning 'CUSTOM2' table...
Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 7745
- Extents Scanned.......................: 969
- Extent Switches.......................: 968
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.31%
- Avg. Bytes Free per Page................: 7259.7
- Avg. Page Density (full)................: 10.31%
DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 27
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.93%
- Extent Scan Fragmentation ...............: 60.00%
- Avg. Bytes Free per Page................: 7291.8
- Avg. Page Density (full)................: 9.91%
DBCC SHOWCONTIG scanning 'CUSTOM3' table...
Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORMS' table...
Table: 'FORMS' (226099846); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7499.5
- Avg. Page Density (full)................: 7.34%
DBCC SHOWCONTIG scanning 'CUSTOM4' table...
Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM5' table...
Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 52
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7247.9
- Avg. Page Density (full)................: 10.45%
DBCC SHOWCONTIG scanning 'CUSTOM6' table...
Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 355
- Extents Scanned.......................: 49
- Extent Switches.......................: 52
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
- Logical Scan Fragmentation ..............: 6.48%
- Extent Scan Fragmentation ...............: 6.12%
- Avg. Bytes Free per Page................: 6637.2
- Avg. Page Density (full)................: 18.00%
DBCC SHOWCONTIG scanning 'CUSTOM7' table...
Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ROLES' table...
Table: 'ROLES' (354100302); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7815.0
- Avg. Page Density (full)................: 3.45%
DBCC SHOWCONTIG scanning 'CUSTOM8' table...
Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USERROLES' table...
Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7646.0
- Avg. Page Density (full)................: 5.53%
DBCC SHOWCONTIG scanning 'CUSTOM9' table...
Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 28967
- Extents Scanned.......................: 3621
- Extent Switches.......................: 3620
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 3.01%
- Avg. Bytes Free per Page................: 7211.5
- Avg. Page Density (full)................: 10.90%
DBCC SHOWCONTIG scanning 'CUSTOM10' table...
Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 46
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.8
- Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
- Logical Scan Fragmentation ..............: 8.70%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 7281.7
- Avg. Page Density (full)................: 10.04%
DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
- Logical Scan Fragmentation ..............: 87.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 7335.6
- Avg. Page Density (full)................: 9.37%
DBCC SHOWCONTIG scanning 'CUSTOM11' table...
Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7641.0
- Avg. Page Density (full)................: 5.60%
DBCC SHOWCONTIG scanning 'CUSTOM12' table...
Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 8040.0
- Avg. Page Density (full)................: 0.67%
DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM29' table...
Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM30' table...
Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM31' table...
Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'RESTORETBL' table...
Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'DOCMASTER' table...
Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 454519
- Extents Scanned.......................: 56834
- Extent Switches.......................: 57417
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418
]
- Logical Scan Fragmentation ..............: 1.10%
- Extent Scan Fragmentation ...............: 2.94%
- Avg. Bytes Free per Page................: 6930.2
- Avg. Page Density (full)................: 14.38%
DBCC SHOWCONTIG scanning 'USERACTIONS' table...
Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
TABLE level scan performed.
- 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]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7941.0
- Avg. Page Density (full)................: 1.89%
DBCC SHOWCONTIG scanning 'USERHISTORY' table...
Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 114
- Extents Scanned.......................: 24
- Extent Switches.......................: 23
- Avg. Pages per Extent..................: 4.8
- Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
- Extent Scan Fragmentation ...............: 91.67%
- Avg. Bytes Free per Page................: 1233.4
- Avg. Page Density (full)................: 84.76%
DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
TABLE level scan performed.
- 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]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8063.0
- Avg. Page Density (full)................: 0.38%
DBCC SHOWCONTIG scanning 'SID_TABLE' table...
Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
TABLE level scan performed.
- 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................: 7846.0
- Avg. Page Density (full)................: 3.06%
DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
TABLE level scan performed.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
> It is possible to mis-use the index configuration options to force this to
> happen - eg, set fillfactor 33, include all columns in the index & it
> could easily be ~ 3 times the size of the table..
> What's your fill-factor for this index?
> Can you post DBCC SHOWCONTIG output for this index?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>|||Which table has the index which is 3x the size of the table?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
> DBCC SHOWCONTIG scanning 'DSECURITY' table...
> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7950.0
> - Avg. Page Density (full)................: 1.78%
> DBCC SHOWCONTIG scanning 'MHINFO' table...
> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'VERINFO' table...
> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
> TABLE level scan performed.
> - 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]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7389.0
> - Avg. Page Density (full)................: 8.71%
> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
> TABLE level scan performed.
> - 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]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7656.0
> - Avg. Page Density (full)................: 5.41%
> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7604.0
> - Avg. Page Density (full)................: 6.05%
> DBCC SHOWCONTIG scanning 'PALETTE' table...
> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 136
> - Extents Scanned.......................: 17
> - Extent Switches.......................: 16
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7241.5
> - Avg. Page Density (full)................: 10.53%
> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2385
> - Extents Scanned.......................: 299
> - Extent Switches.......................: 298
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.33%
> - Avg. Bytes Free per Page................: 7265.2
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7574.0
> - Avg. Page Density (full)................: 6.42%
> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 7745
> - Extents Scanned.......................: 969
> - Extent Switches.......................: 968
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.31%
> - Avg. Bytes Free per Page................: 7259.7
> - Avg. Page Density (full)................: 10.31%
> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 27
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.93%
> - Extent Scan Fragmentation ...............: 60.00%
> - Avg. Bytes Free per Page................: 7291.8
> - Avg. Page Density (full)................: 9.91%
> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORMS' table...
> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7499.5
> - Avg. Page Density (full)................: 7.34%
> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 52
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 7.4
> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7247.9
> - Avg. Page Density (full)................: 10.45%
> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 355
> - Extents Scanned.......................: 49
> - Extent Switches.......................: 52
> - Avg. Pages per Extent..................: 7.2
> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
> - Logical Scan Fragmentation ..............: 6.48%
> - Extent Scan Fragmentation ...............: 6.12%
> - Avg. Bytes Free per Page................: 6637.2
> - Avg. Page Density (full)................: 18.00%
> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ROLES' table...
> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7815.0
> - Avg. Page Density (full)................: 3.45%
> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USERROLES' table...
> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7646.0
> - Avg. Page Density (full)................: 5.53%
> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 28967
> - Extents Scanned.......................: 3621
> - Extent Switches.......................: 3620
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:362
1]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 3.01%
> - Avg. Bytes Free per Page................: 7211.5
> - Avg. Page Density (full)................: 10.90%
> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 46
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 5.8
> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
> - Logical Scan Fragmentation ..............: 8.70%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 7281.7
> - Avg. Page Density (full)................: 10.04%
> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 8
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
> - Logical Scan Fragmentation ..............: 87.50%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 7335.6
> - Avg. Page Density (full)................: 9.37%
> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7641.0
> - Avg. Page Density (full)................: 5.60%
> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 8040.0
> - Avg. Page Density (full)................: 0.67%
> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 454519
> - Extents Scanned.......................: 56834
> - Extent Switches.......................: 57417
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:574
18]
> - Logical Scan Fragmentation ..............: 1.10%
> - Extent Scan Fragmentation ...............: 2.94%
> - Avg. Bytes Free per Page................: 6930.2
> - Avg. Page Density (full)................: 14.38%
> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
> TABLE level scan performed.
> - 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]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7941.0
> - Avg. Page Density (full)................: 1.89%
> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 114
> - Extents Scanned.......................: 24
> - Extent Switches.......................: 23
> - Avg. Pages per Extent..................: 4.8
> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
> - Extent Scan Fragmentation ...............: 91.67%
> - Avg. Bytes Free per Page................: 1233.4
> - Avg. Page Density (full)................: 84.76%
> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
> TABLE level scan performed.
> - 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]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8063.0
> - Avg. Page Density (full)................: 0.38%
> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
> TABLE level scan performed.
> - 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................: 7846.0
> - Avg. Page Density (full)................: 3.06%
> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
> TABLE level scan performed.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>|||DOCMASTER
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Which table has the index which is 3x the size of the table?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>|||The pages on that table's clustered index are only 14% full, so there's a
lot of wasted space.
Rebuild it with a higher fillfactr (eg 80% or higher) & its size should be
significantly reduced.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23cY8pAbCHHA.1012@.TK2MSFTNGP04.phx.gbl...
> DOCMASTER
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
>