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.co...te_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.
TIA, ChrisR
composite is a covering index so all the data SQL needs is actually IN The
index.
Once SQL reads the index, it HAS the data (Literally)
in piece meal indexes, once the index is read, a table lookup must be
performed. You likely see this in your Query Plan as Bookmark lookups.
basically, way more IO required with the piece meal indexes.
Greg Jackson
PDX, Oregon
|||So then why is the query slower than when I started?
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uUC6enXWEHA.3740@.TK2MSFTNGP12.phx.gbl...
> composite is a covering index so all the data SQL needs is actually IN The
> index.
> Once SQL reads the index, it HAS the data (Literally)
> in piece meal indexes, once the index is read, a table lookup must be
> performed. You likely see this in your Query Plan as Bookmark lookups.
> basically, way more IO required with the piece meal indexes.
> Greg Jackson
> PDX, Oregon
>
|||really hard to say without looking at perfmon data, profiler results, yadda
yadda yadda.
However, I would point to the usual suspects...
1. Could be your indexes are becoming fragmented (Run dbcc showcontig to
find out if scan density has dropped below 80% on any of the indexes\tables
in question)
2. statistics out of date
3. Data has changed - you have more data than before perhaps
4. other schema changes impacting the query now that werent impacting it
before
5. some blocking is occuring that was not occuring before
I dunno.
GAJ