indexes are slowing down my query

sql2k sp3
Ive got a query that takes 30 seconds to run:
select top 50 customerkey as customerkey from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey
order by CustomerKey,min(TransDate)
Index Tuning Wizard wants to create an Composite Index on
MerchName, CustomerKey, TranCode, and TransDate. There is
already an Composite Index on TransDate, TranCode(in this
order) but for kicks I do the Wizards deal. Pretty cool in
that my query speeds up to only 1 second. Any yes, I
cleared the cache. Ive always followed the advice from
this article:
http://www.sql-server-performance.com/composite_indexes.asp
and for the most part used single column Indexes instead
of Composites. So, I drop the newly created Composite
Indexes, create 4 single coulmn Indexes, and expect it to
be about the same. Not only was it slower, but it was
slower now than when there was no Indexes on these
columns. 50 seconds. Ive tested this alot over the last
couple of days and its very consistant. Im getting a table
scan on the TranCode coulmn I noticed. I used an Index
Hint and have the same thing. Why is the Composite soooooo
much faster than the individuals? Why Is a Table Scan
occuring? Why is it slower than before? All ideas are
appreciated.
*** note: someone mentioned yesterday (thank you) that the
results are due to the ITW's index being a covered index.
That makes sense. But I am still not getting why my query
takes 20 seconds longer after adding individual indexes?
***
TIA, ChrisRHi,
Here is a bit of information I found in BOL:
"
Covered queries can improve performance. Covered queries
are queries where all the columns specified in the query
are contained within the same index. For example, a query
retrieving columns a and b from a table that has a
composite index created on columns a, b, and c is
considered covered. Creating indexes that cover a query
can improve performance because all the data for the query
is contained within the index itself; only the index
pages, not the data pages, of the table must be referenced
to retrieve the data, thereby reducing overall I/O.
Although adding columns to an index to cover queries can
improve performance, maintaining the extra columns in the
index incurs update and storage costs.
"
It seems that your individual indexes DO NOT cover your
query. Each time you run your query, the optimizer is
using 4 different index (one at a time) and it has
increased disk i/o. Increased disk i/o means slow query
response.
hth
DeeJay
>--Original Message--
>sql2k sp3
>Ive got a query that takes 30 seconds to run:
>
> select top 50 customerkey as customerkey from
transdtl
> where MerchName in ('DTV*DIRECTV
SERVICE','DTV*DIRECTV
>SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey
> order by CustomerKey,min(TransDate)
>
>Index Tuning Wizard wants to create an Composite Index on
>MerchName, CustomerKey, TranCode, and TransDate. There is
>already an Composite Index on TransDate, TranCode(in this
>order) but for kicks I do the Wizards deal. Pretty cool
in
>that my query speeds up to only 1 second. Any yes, I
>cleared the cache. Ive always followed the advice from
>this article:
>http://www.sql-server-
performance.com/composite_indexes.asp
>and for the most part used single column Indexes instead
>of Composites. So, I drop the newly created Composite
>Indexes, create 4 single coulmn Indexes, and expect it to
>be about the same. Not only was it slower, but it was
>slower now than when there was no Indexes on these
>columns. 50 seconds. Ive tested this alot over the last
>couple of days and its very consistant. Im getting a
table
>scan on the TranCode coulmn I noticed. I used an Index
>Hint and have the same thing. Why is the Composite
soooooo
>much faster than the individuals? Why Is a Table Scan
>occuring? Why is it slower than before? All ideas are
>appreciated.
>*** note: someone mentioned yesterday (thank you) that
the
>results are due to the ITW's index being a covered index.
>That makes sense. But I am still not getting why my query
>takes 20 seconds longer after adding individual indexes?
>***
>TIA, ChrisR
>.
>|||>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?
>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>>--Original Message--
>>sql2k sp3
>>Ive got a query that takes 30 seconds to run:
>>
>> select top 50 customerkey as customerkey from
>transdtl
>> where MerchName in ('DTV*DIRECTV
>SERVICE','DTV*DIRECTV
>>SERVICER39')
>> and TranCode not in (7008,7023)
>> Group By CustomerKey
>> order by CustomerKey,min(TransDate)
>>
>>Index Tuning Wizard wants to create an Composite Index
on
>>MerchName, CustomerKey, TranCode, and TransDate. There
is
>>already an Composite Index on TransDate, TranCode(in
this
>>order) but for kicks I do the Wizards deal. Pretty cool
>in
>>that my query speeds up to only 1 second. Any yes, I
>>cleared the cache. Ive always followed the advice from
>>this article:
>>http://www.sql-server-
>performance.com/composite_indexes.asp
>>and for the most part used single column Indexes instead
>>of Composites. So, I drop the newly created Composite
>>Indexes, create 4 single coulmn Indexes, and expect it
to
>>be about the same. Not only was it slower, but it was
>>slower now than when there was no Indexes on these
>>columns. 50 seconds. Ive tested this alot over the last
>>couple of days and its very consistant. Im getting a
>table
>>scan on the TranCode coulmn I noticed. I used an Index
>>Hint and have the same thing. Why is the Composite
>soooooo
>>much faster than the individuals? Why Is a Table Scan
>>occuring? Why is it slower than before? All ideas are
>>appreciated.
>>*** note: someone mentioned yesterday (thank you) that
>the
>>results are due to the ITW's index being a covered
index.
>>That makes sense. But I am still not getting why my
query
>>takes 20 seconds longer after adding individual indexes?
>>***
>>TIA, ChrisR
>>.
>.
>|||On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:
>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)