|
Home > Archive > MS SQL Server > November 2006 > Large Index
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Is it normal for Index size to be three times the size of its corresponding
table in SQL 2005
| |
| Greg Linwood 2006-11-16, 12:16 am |
| 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@sdlgweg90d
flg.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@sdlgweg90d
flg.com> wrote in message
> news:%23au0S5PCHHA.5012@TK2MSFTNGP06.phx.gbl...
>
>
| |
| Greg Linwood 2006-11-16, 7:12 pm |
| 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@sdlgweg90d
flg.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: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...
>
>
| |
|
| 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@sdlgweg90d
flg.com> wrote in message
> news:%2397basaCHHA.204@TK2MSFTNGP04.phx.gbl...
>
>
| |
| Greg Linwood 2006-11-16, 7:12 pm |
| 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@sdlgweg90d
flg.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...
>
>
|
|
|
|
|