Wierd Performance Question- Please help!!

[sp]

Hi,

I have a stored procedure and in that one update statement is taking 36
minutes when ran thought the client application. But, when i profiled and run
those statements in the batch in management studio it gives me results in
less than 5 minutes.

Please let me know your tips to trouble shoot this.

thanks in advance!!

[Plamen Ratchev]

If you eliminate the possibilities of blocking, then look at the executions plans. If different execution plans are used
that will explain it. Read the following for details explanation on plan caching and potential performance problems with
parameter sniffing, as well as some methods to solve it:

http://technet.microsoft.com/en-us/library/cc966425.aspx
http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

--
Plamen Ratchev
http://www.SQLStudio.com

[TheSQLGuru]

Parameter sniffing for sure. Search the web - there are many ways to
'defeat' this very common issue.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"sp" <s...@discussions.microsoft.com> wrote in message
news:989CA74C-4868-4CFC...@microsoft.com...

[sp]

hi Plamen,
Thanks for your post. I am able to see my plan when i run it my mgmt.
studio. but how to fetch the plan that was executed by the client
application. is there any way i can see the "actual plan" of a statement
executed by client application.

thanks,

"Plamen Ratchev" wrote:

> .
>

[Plamen Ratchev]

You can use SQL Profiler to capture the execution plan:
http://www.mssqltips.com/tip.asp?tip=1264
http://msdn.microsoft.com/en-us/library/ms190233.aspx
http://blogs.techrepublic.com.com/datacenter/?p=269

[Erland Sommarskog]

sp (s...@discussions.microsoft.com) writes:
> Thanks for your post. I am able to see my plan when i run it my mgmt.
> studio. but how to fetch the plan that was executed by the client
> application. is there any way i can see the "actual plan" of a statement
> executed by client application.

You can catch that in Profiler with the event Performance:Showplan
XML For Statistics Profile. However, be aware of that tracing this
event on a production server can have a significant impact on the
overall performance. And this applies, even if you filter for the spid
of the client.

You can also try running the procedure in Mgmt Studio, after first
having run this command:

SET ARITHABORT OFF

the query will now probably take 36 minutes as it does in the client.
ARITHABORT is one of these settings that are key attributes of a plan,
and this is why you get different execution plans. ARITHABORT is OFF by
default in client code, but ON by default in SSMS. It is very unlikely,
however, that ARITHABORT itself affects the execution time, but that
is likely to be a matter of parameter sniffing.

If you look in the XML document, you can see the sniffed parameters
values at the bottom of the document.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx