Different Execution Plan using COM+ of Query Analyzer.

Hi,
I have the following problem : one of our statements gives a different
execution plan if it is executed from a component in COM+, of directly
in Query Analyzer.
I checked this using the following scenario :
* Execute the business code and profile using SQL Profiler.
* I included the query plan in the trace.
* Copy/Paste the statement to Query Analyzer, to ensure that I have
exactly the same statement. Execute it, and compare the result from
SQL Profiler.
If I compare the two, he does a table scan coming from COM+, and an
Index S coming from Query Analyzer. The COM+ version takes a
minute, the Query Analyzer doesn't even last a second.
I have tried this scenario staring from a restart of the SQL Server
service, so I am sure that caching of the statement is not the cause.
If I restart and first execute from Query Analyzer, the COM+ version
still takes just as long.
Note that this is on a database where no one else is working on.
After adding an INDEX hint to the statement, both the query plans are
the same, and both execute quickly.
Of course I want to avoid having to add INDEX hints to statements, and
I want to be sure that if I request a Query Plan in Query Analyzer, I
get something that I can trust.
Any suggestions what might be the cause of this ?
JDAre you sending the statement from the client app or are you executing a
stored procedure?
AMB
"JanM" wrote:

> Hi,
> I have the following problem : one of our statements gives a different
> execution plan if it is executed from a component in COM+, of directly
> in Query Analyzer.
> I checked this using the following scenario :
> * Execute the business code and profile using SQL Profiler.
> * I included the query plan in the trace.
> * Copy/Paste the statement to Query Analyzer, to ensure that I have
> exactly the same statement. Execute it, and compare the result from
> SQL Profiler.
> If I compare the two, he does a table scan coming from COM+, and an
> Index S coming from Query Analyzer. The COM+ version takes a
> minute, the Query Analyzer doesn't even last a second.
> I have tried this scenario staring from a restart of the SQL Server
> service, so I am sure that caching of the statement is not the cause.
> If I restart and first execute from Query Analyzer, the COM+ version
> still takes just as long.
> Note that this is on a database where no one else is working on.
> After adding an INDEX hint to the statement, both the query plans are
> the same, and both execute quickly.
> Of course I want to avoid having to add INDEX hints to statements, and
> I want to be sure that if I request a Query Plan in Query Analyzer, I
> get something that I can trust.
> Any suggestions what might be the cause of this ?
> JD
>|||No stored procedure is used. All statements are executed from within
COM + transactions.
JanM
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<AA5F00BB-E11F-496F-8D69-DBE0BB3DAB5A@.microsoft.com>...
> Are you sending the statement from the client app or are you executing a
> stored procedure?
>
> AMB
>
> "JanM" wrote:
>