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 WalkerThis should be the DDL:
CREATE TABLE [dbo].[Transactions] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] [int] NULL ,
[Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Transactions-Old] (
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
(month,[Trade_Date])) ,
[Acct_Pre] AS (left([Acct_Number],3))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[SSN_TIN],
[ACCT_NUMBER],
[Settle_Date],
[SEQ_NUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
> 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 Walker
>|||Oops -- the new Transactions table actually has the ID (int) field
indexed twice, both clustered and non-clustered, with 2 different
indexes. Now how did that happen? :-)
I'll fix the indexes and check again.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> 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 Walker
>|||Um, I fixed the incorrect index to be a clustered index on the
Trade_Date column like it should have been, and the results are
essentially the same. I'm still confused.
Thanks for any insights.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> 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 Walker
>|||David,
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 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 Walker
--
(Please reply only to the newsgroup)|||Hi David,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you informaiton provided, you generate the SQL script from one table.
You noticed that the ID column in the following part:
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
There is an clustered index and nonclustered index bulid on the table. You
wonder how it come, right?
To get the information on the table, you could run this statement in you
Query Analyzer:
exec sp_help transactions
OR
sp_helpindex transactions
From my experience, in the Enterprise Manger you have first create a
clustered index 'IX_CL_Transactions_TradeDate' on the column ID, then you
create a PRIMARY KEY constraint on this same column. So, finaly, you will
found that the 'sp_help transactions' or 'sp_helpindex transactions' will
show that the index on the column ID is non-clustered.
For maintenance purpose, you could run the DBCC INDEXDEFRAG
Hope this helps and if you still have questions, please feel free to post
your message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql