index bloat?

[sql_server_2000_user]

Hi,

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Thanks,
Seth

[Erland Sommarskog]

sql_server_2000 _user (se*********@co mcast.net) writes:

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Is that a clustered index or a non-clustered iodex?

A clustered index has the data pages in the leafs of the index node, so
size of index is basically size of data.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Tony Sebion]

Just a guess, but is your primary key a clustered index? The clustered
index represents the actual contents of each row in your database, so it
would make sense that it would be larger than only the indexed fields.

If this is the case, you might want to change that index to not be
clustered and choose a more appropriate field to make a clustered index.

Thanks,
Tony

"sql_server_200 0_user" <se*********@co mcast.net> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com:

Hi,

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Thanks,
Seth

[sql_server_2000_user]

Yes! It is intentionally a clustered index. But if the db includes the
row data pages when it reports the size of a clustered index, why is
the size of the index not just equal to the size of the table (105GB?)

Thanks,
Seth

[Tony Sebion]

I think I would take a look at the FILL FACTOR of the index (the % of
the index that is created empty), and possibly do a DBCC SHOWCONTIG to
show the current fragmentation and DBCC REINDEX on the table during a
time of little or no activity if I thought that the tables contents
might be fragmented.

You can look up any of the all-caps terms in Books Online if you want
more info about the commands.

Good luck,
Tony Sebion

"sql_server_200 0_user" <se*********@co mcast.net> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com:

Yes! It is intentionally a clustered index. But if the db includes the
row data pages when it reports the size of a clustered index, why is
the size of the index not just equal to the size of the table (105GB?)

Thanks,
Seth

[sql_server_2000_user]

thanks for the tips. the fill factor is zero on the index; i'll
definitely look for fragmentation. what i don't understand is, the
query was fast yesterday (4 secs), and today it's slow (10 mins), and
the only change i made to the table was that i added an index on an
unrelated field. (the new index is 12GB)

[Tony Sebion]

Now that you mention it, you will want to make sure you REINDEX all the
other indexes for that table after you're done reindexing the clustered
one. I'd start there and see what things look like when that is
complete.

Tony

"sql_server_200 0_user" <se*********@co mcast.net> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com:

thanks for the tips. the fill factor is zero on the index; i'll
definitely look for fragmentation. what i don't understand is, the
query was fast yesterday (4 secs), and today it's slow (10 mins), and
the only change i made to the table was that i added an index on an
unrelated field. (the new index is 12GB)

[Erland Sommarskog]

sql_server_2000 _user (se*********@co mcast.net) writes:

Yes! It is intentionally a clustered index. But if the db includes the
row data pages when it reports the size of a clustered index, why is
the size of the index not just equal to the size of the table (105GB?)
From where did you get that? Enterprise Manager? EM is not known to report
sizes very well. I tried to have a look at it, but was not able to dig
out any sizes at all from EM. (Yeah, that's right, I don't use EM that
often.)

Anyway, your real problem appears to be:
what i don't understand is, the query was fast yesterday (4 secs), and
today it's slow (10 mins), and the only change i made to the table was
that i added an index on an unrelated field. (the new index is 12GB)

It appears that some how this cause a shake-up for the query plan. When
you create a new index, statistics are updated with fullscan. Since the PK
is the clustered index, the PK columns are included in the non-clustered
index as well, as the index keys for the clustered index work as row
locator. I don't know if this causes statistics on the PK to be updated
as well. It could also be that SQL Server makes an incorrect estimate
and thinks that the new index is good for the query.

It would help if you posted the query, the CREATE TABLE statement for
the table, as well as the CREATE INDEX statements.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[sql_server_2000_user]

Wow, you guys are great. Yeah, I know EM returns wack object sizes, but
I thought they were kind of close; maybe not. Anyway, here's a test I
find interesting:

query 1:

select * from loan_history where time_period=196 and exloan_id in (
select top 3 exloan_id from loan where deal_no='ML4W1' )

query 2:

select * from loan_history where time_period=196 and exloan_id in
('CTSMYX0010515 778', 'CTSMYX00105257 10', 'CTSMYX00105274 75')

Query 1 takes a long time - i don't know how long because i cancel it
after a few minutes. Query 2 is <1 sec. the odd thing is, the subquery
of query 1 is also <1 sec! ???

The query that used to be fast and is now slow, requiring a huge amount
of reads, is simply:

select * from loan
inner join loan_history lh
on loan.exloan_id = lh.exloan_id
where loan.deal_no='M L4W1'
and time_period='19 6'

Here's the output of DBCC SHOWCONTIG on loan_history (305mm rows):

DBCC SHOWCONTIG scanning 'loan_history' table...
Table: 'loan_history' (949578421); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 7497085
- Extents Scanned........ ............... .......: 941533
- Extent Switches....... ............... ........: 1489125
- Avg. Pages per Extent......... ............... : 8.0
- Scan Density [Best Count:Actual Count].......: 62.93%
[937136:1489126]
- Logical Scan Fragmentation ............... ...: 93.40%
- Extent Scan Fragmentation ............... ....: 2.39%
- Avg. Bytes Free per Page........... ..........: 535.3
- Avg. Page Density (full)......... ............: 93.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
And for loan (9.8mm rows):

DBCC SHOWCONTIG scanning 'loan' table...
Table: 'loan' (1413580074); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 462476
- Extents Scanned........ ............... .......: 58046
- Extent Switches....... ............... ........: 77279
- Avg. Pages per Extent......... ............... : 8.0
- Scan Density [Best Count:Actual Count].......: 74.81% [57810:77280]
- Logical Scan Fragmentation ............... ...: 96.45%
- Extent Scan Fragmentation ............... ....: 0.54%
- Avg. Bytes Free per Page........... ..........: 329.5
- Avg. Page Density (full)......... ............: 95.93%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Sorry for the long post, but here's the create table statement for
loan:

CREATE TABLE [loan] (
[SERIES_NO] [varchar] (12) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[POOL_ID] [char] (3) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[DEAL_NO] [char] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[GROUP_NO] [char] (3) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[LOAN_ID] [char] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[EXLOAN_ID] [varchar] (18) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[LOAN_NO] [varchar] (12) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[ADD_DATE] [int] NULL ,
[PROP_ZIP] [char] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[STATE] [char] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PROP_TYPE] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[UNITS_NO] [int] NULL ,
[OCCUPANCY] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ORIG_DATE] [int] NULL ,
[MATURITY] [int] NULL ,
[FIRST_PMT] [int] NULL ,
[ORIG_AMT] [money] NULL ,
[CLOSE_BAL] [money] NULL ,
[CLOSE_INT] [float] NULL ,
[SALE_PRICE] [money] NULL ,
[APP_VALUE] [money] NULL ,
[PROD_TYPE] [char] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[TERM] [int] NULL ,
[INIT_RATE] [float] NULL ,
[UNDER_RAT1] [float] NULL ,
[UNDER_RAT2] [float] NULL ,
[LOAN_TYPE] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PURPOSE] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PMT_FREQ] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[LOAN_SRC] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[BUYDOWN] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DOCUMENT] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PMI_CODE] [char] (3) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CONVERT] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[POOL_INS] [bit] NULL ,
[RECOURSE] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[LTV] [float] NULL ,
[SERV_FEE_R] [float] NULL ,
[NEGAM] [bit] NULL ,
[NEG_LIMIT] [float] NULL ,
[INDEX_ID] [char] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[MARGIN] [float] NULL ,
[P_RATE_CAP] [float] NULL ,
[P_RATE_FLO] [float] NULL ,
[P_PAY_CAP] [float] NULL ,
[P_PAY_FLO] [float] NULL ,
[L_RATE_CAP] [float] NULL ,
[L_RATE_FLO] [float] NULL ,
[RATE_RESET] [int] NULL ,
[PAY_RESET] [int] NULL ,
[FIRST_RATE] [int] NULL ,
[FIRST_PAY] [int] NULL ,
[AMORT_TERM] [int] NULL ,
[DOC_RAW] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[FICO] [int] NULL ,
[LIEN] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[GRADE_RAW] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[GRADE_MIC] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PP_PEN] [bit] NULL ,
[PP_TERM] [int] NULL ,
[F_RATE_CAP] [float] NULL ,
[PMI_LEVEL] [float] NULL ,
[PLEDGE_AMT] [money] NULL ,
[EFF_LTV] [float] NULL ,
[FIRST_LTV] [float] NULL ,
[SECOND_LTV] [float] NULL ,
[COMB_LTV] [float] NULL ,
[SERVICER] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ORIGINATOR] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[SILENT_SECOND] [bit] NOT NULL ,
CONSTRAINT [PK_loan] PRIMARY KEY CLUSTERED
(
[EXLOAN_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

And for loan_history:

CREATE TABLE [loan_history] (
[TIME_PERIOD] [int] NOT NULL ,
[POOL_ID] [char] (3) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[DEAL_NO] [char] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[GROUP_NO] [char] (3) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[SERVICER] [char] (4) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[LOAN_ID] [char] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[EXLOAN_ID] [varchar] (18) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[LAST_INT_P] [int] NULL ,
[BALANCE] [money] NULL ,
[INT_RATE] [float] NULL ,
[TOTPMT_DUE] [money] NULL ,
[SCH_PRINC] [money] NULL ,
[SCH_MNTH_P] [money] NULL ,
[MBA_STAT] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OTS_STAT] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DELIQ_HIST] [char] (12) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[EXCEPTION] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[FC_START_D] [int] NULL ,
[FC_END_D] [int] NULL ,
[FC_END_TYP] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PAYOFF_D] [int] NULL ,
[PAYOFF_R] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[REO_DATE] [int] NULL ,
[INV_BAL] [money] NULL ,
[NEXT_INT_R] [float] NULL ,
[LOSS_AMT] [money] NULL ,
[INET_RATE] [float] NULL ,
[YYYYMM] AS ((floor(([TIME_PERIOD] / 12)) + 1989) * 100 +
[TIME_PERIOD] % 12 + 1) ,
CONSTRAINT [PK_loan_history] PRIMARY KEY CLUSTERED
(
[TIME_PERIOD],
[EXLOAN_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks for all the help, I really appreciate it.
Seth

[Erland Sommarskog]

sql_server_2000 _user (se*********@co mcast.net) writes:

query 1:

select * from loan_history where time_period=196 and exloan_id in (
select top 3 exloan_id from loan where deal_no='ML4W1' )

query 2:

select * from loan_history where time_period=196 and exloan_id in
('CTSMYX0010515 778', 'CTSMYX00105257 10', 'CTSMYX00105274 75')

Query 1 takes a long time - i don't know how long because i cancel it
after a few minutes. Query 2 is <1 sec. the odd thing is, the subquery
of query 1 is also <1 sec! ???
Well, it's not the case that SQL Server first runs the subquery and given
that result optimizes the outer query. It optimizes everything in one
go.

Then again, it's difficult to say why it would fail here, since it knows
that it will get (at most) three rows from loan, and thus only have to
read three rows from loan_history.

You forgot to include indexes for the tables, but I assume that here is
a non-clustered index on loan.deal_no?

What query plan do you have for the slow query here? Run the query
embedded in SET SHOWPLAN_TEXT ON /OFF. (In separate batches.)
The query that used to be fast and is now slow, requiring a huge amount
of reads, is simply:

select * from loan
inner join loan_history lh
on loan.exloan_id = lh.exloan_id
where loan.deal_no='M L4W1'
and time_period='19 6'
For this query it would probably be better if the index on
loan.deal_no was clustered, possibly then it should be (deal_no, exloan_id).
But this could have consequences for other queries.

Again, it would be interesting to see the query plan.

By the way, using SELECT * in production code is not good practice.
Better to list all columns you reallyneed.
Here's the output of DBCC SHOWCONTIG on loan_history (305mm rows):

Thanks. The tables appears to be in decent shape. Not perfect, but
certainly not alarming.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp