Calling Stored Procedure from COM+

Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level. COM+ defaults to
serializable, where others defaults to READ COMMITTED.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>