Prev: read cobol comp-3 field
Next: sp default permissions
From: sp on 23 Dec 2009 11:38
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!!
From: TheSQLGuru on 23 Dec 2009 14:23
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" <sp(a)discussions.microsoft.com> wrote in message
news:989CA74C-4868-4CFC-BF37-4F6CE1D34F2F(a)microsoft.com...
> 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!!


From: Plamen Ratchev on 23 Dec 2009 17:33
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

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 23 Dec 2009 18:08
sp (sp(a)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, esquel(a)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

 | 
Pages: 1
Prev: read cobol comp-3 field
Next: sp default permissions