|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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? Aaron 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. 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. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials <aa***@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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ <aa***@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. >  |
|||||||||||||||||||||||
Other interesting topics