Index size question

I have a SQL 2000 table with 16 million rows. I made a copy of it. The
two tables have the same number of rows, 16,152,139 to be exact.
The old table had a clustered, composite primary key across the first four
columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
(varchar(20)), and transaction date (smalldatetime). The database size was
1,708,032 KB and the index was 8,520 KB.
To the new table, I added an ID field of type Int, and made it the primary
key nonclustered, also an identity field. The only other index is a
different date field in the table that's a clustered index (smalldatetime).
I also set the index fill factor to 80% from 90% in the old one.
The new table takes 2,406,592 KB; it's bigger because of the extra field.
BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
two single-column indexes take 19 times the storage space as one 4-column
composite index?* I have run dbcc dbreindex on the table.
I also ran dbcc updateusage on the new table and got trivial differences.
Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
DBCC SHOWCONTIG scanning 'Transactions-Old' table...
Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 212443
- Extents Scanned.......................: 26688
- Extent Switches.......................: 26687
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.50%
- Avg. Bytes Free per Page................: 766.4
- Avg. Page Density (full)................: 90.53%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 280366
- Extents Scanned.......................: 35103
- Extent Switches.......................: 35102
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.11%
- Avg. Bytes Free per Page................: 1562.7
- Avg. Page Density (full)................: 80.69%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 19966
- Extents Scanned.......................: 2500
- Extent Switches.......................: 2499
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.60%
- Avg. Bytes Free per Page................: 6.2
- Avg. Page Density (full)................: 99.92%
I would post the DDL but I can't find the link to get the format...
Thanks.
David WalkerDavid,
The table holds the leaf level pages of the clustered index. This means
that adding a clustered index to a heap (a table without clustered
index) will add only a few percent to the total size. A nonclustered
index is stored entirely separate from the table.
Maybe you hadn't noticed, but it is impossible to index 16 million rows
in 8 million bytes. (16,152,139 rows in 8,520 KB).
When you create a nonclustered index, it needs space for the indexed
column(s) plus space for the clustered key of each row. So the wider the
clustered index, the bigger any nonclustered index will be. When adding
a nonclustered index, all of a sudden the size of the clustered index
shows...
Hope this helps,
Gert-Jan
DW wrote:
> I have a SQL 2000 table with 16 million rows. I made a copy of it. The
> two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first four
> columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
> (varchar(20)), and transaction date (smalldatetime). The database size wa
s
> 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the primary
> key nonclustered, also an identity field. The only other index is a
> different date field in the table that's a clustered index (smalldatetime)
.
> I also set the index fill factor to 80% from 90% in the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra field.
> BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How coul
d
> two single-column indexes take 19 times the storage space as one 4-column
> composite index?* I have run dbcc dbreindex on the table.
> I also ran dbcc updateusage on the new table and got trivial differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can look at
?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:266
88]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:351
03]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500
]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
(Please reply only to the newsgroup)