Re: Index chosen is wrong index

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 18:45:47 +0200

Jean,

as other have suggested, you have not provided enough information to
support any conclusion. For example, do you have any unique
constraints/indexes, do you have a clustered index (if so, what is its
definition), what is the distribution of the data, etcetera.

In addition, since you are joining two resultsets, there is likely to be
a second index seek/scan, and the union can affect the query plan.

If you post more information, maybe someone can say something with more
confidence.

BTW: I assume obfuscated the query on purpose, it is really
unreadable...

Gert-Jan

Jean Bertrand wrote:
>
> I'm on SQL Server 2000 SP3.
> The execution plan for the query below selects the wrong
> index. It selects the index that has the leading column
> as SDITIM, which is not even in the where clause nor the
> order by clause. The index that it chose is not even the
> clustered index. Any ideas on WHY? Is this a BUG?
>
> Index Chosen in Execution Plan
> ====================================
> CREATE INDEX [F4211_9] ON [PRODDTA].[F4211]([SDITM],
> [SDMCU], [SDDRQJ]) WITH FILLFACTOR = 90 ON [SECONDARY]
> GO
>
> SQL Statement
> ==============
> SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
> SDOKCO, SDOORN, SDOCTO, SDRKCO,
> SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8,
> SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ,
> SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01,
> SDITM, SDLITM, SDAITM, SDLOCN,
> SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU,
> SDRLIT, SDRKIT, SDSRP1, SDSRP2,
> SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK,
> SDSOCN, SDUPRC, SDAEXP, SDUNCS,
> SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCDCD,
> SDCARS, SDMOT, SDZON, SDFRTH,
> SDUOM4, SDSO15, SDSLSM, SDSLM2, SDSWMS, SDCRCD, SDCRR,
> SDFUP, SDFEA, SDFUC, SDTORG
> FROM PRODDTA.F4211 (NOLOCK) WHERE ( SDLTTR >= '520' AND
> SDDCTO = 'S2' AND
> SDLTTR <= '999'
> AND SDMCU = ' 9500' )
> UNION SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO,
> SDMCU, SDOKCO,
> SDOORN, SDOCTO, SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT,
> SDAN8, SDSHAN, SDPA8, SDDRQJ,
> SDTRDJ, SDPDDJ, SDOPDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL,
> SDRSDJ, SDPEFJ, SDVR01, SDITM,
> SDLITM, SDAITM, SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY,
> SDNXTR, SDLTTR, SDEMCU, SDRLIT,
> SDRKIT, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM,
> SDUORG, SDSOQS, SDSOBK, SDSOCN,
> SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT,
> SDPSN, SDDELN, SDCDCD, SDCARS,
> SDMOT, SDZON, SDFRTH, SDUOM4, SDSO15, SDSLSM, SDSLM2,
> SDSWMS, SDCRCD, SDCRR, SDFUP,
> SDFEA, SDFUC, SDTORG FROM PRODDTA.F42119 (NOLOCK)
> WHERE ( SDLTTR >= '520' AND SDDCTO = 'S2' AND SDLTTR
> <= '999' AND
> SDMCU = ' 9500') 

-- 
(Please reply only to the newsgroup)

Relevant Pages

  • Re: sql server issue.
    ... I tried by keeping the Clustered index on RecipientId,MessageId. ... Before having the clustered index on recipientid the execution plan is ... > query better. ... >>INSERT INTO MessageRecipient ...
    (microsoft.public.sqlserver.programming)
  • Re: Adhoc Search...indexing optimization help please.
    ... I have checked the article and modified my query to look like ... a clustered index scan is still performed. ... >> actual columns in the table Clients) ... >> When I check up the execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: sp_executesql uses wrong index over time
    ... > SQL Server MVP ... The delete query looks typically like this when doing ... >> the query analyzer we found out thru the execution plan that it had ... >> while the other one does a clustered index seek. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance Issue
    ... >and from the Execution Plan both uses the Clustered Index ... the best query would be just the following: ...
    (microsoft.public.sqlserver.programming)
  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)