SP timing out, can't find the cause

We are using SQL Server 2005. The SP in question is a fairly simple
select statement. When the developers run their unit tests it is
timing out (15 second timeout is set from their end). When I run the
SP in a query window using the exact same parameters it takes less
than a second to run. I've run profiler and turned up nothing there,
including deadlocks. The SP does show as having a duration of 15
seconds though. I've also run the blocking monitor as described on
Microsoft's site (http://support.microsoft.com/kb/271509) and I don't
see any blocking issues. I've also monitored CPU and IO access times
through the performance monitor on the server and everything looked
clear.

Is there something obvious that I should be checking? I'm at a loss
(and a bit rusty in my performance monitoring).

Thanks!
-Tom.

re: SP timing out, can't find the cause

On Nov 17, 3:43*pm, "Thomas R. Hummel" <tom_hum...@hotmail.comwrote:Looks like I've found the answer to my own question, at least
partially. SET arithabort OFF is coming from the application
connection and when I set that in my Management Studio I see the same
slowness. Now to find out why that's the case...

-Tom.

re: SP timing out, can't find the cause

This is due most likely to having two query plans for the SP because SET
ARITHABORT is one of the options that affects plan reuse. One of the
plans is not efficient for the selected parameters. Read the following
article for more details:

http://www.microsoft.com/technet/pro...05/recomp.mspx

You can Google "parameter sniffing" for more examples. There are
different methods to solve that. You can just execute the SP using WITH
RECOMPILE to get a fresh plan. Other alternatives are to include query
hint OPTION (RECOMPILE) on the query that uses the parameters, OPTION
(OPTIMIZE FOR ...) to optimize for selected parameter value, use local
variables to assign the parameters and then use the local variables in
the query (as local variables are not sniffed). You can also use a plan
guide to apply a query hint without changing the SP.

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

re: SP timing out, can't find the cause

Thomas R. Hummel (tom_hummel@hotmail.com) writes:ARITHABORT explains why you get different plans in Mgmt Studio and the
application, but it's highly unlikely that it as such has any impact on
the query plan. It's more likely to be an issue with parameter sniffing,
that is, which value the parameters of the SP when the procedure was
called the first time. If you view the XML document of the query plan,
I think the sniffed values are present there somewhere.

ARITHABORT is on my default when you connect from Mgmt Studio, but off
by default from all client APIs.


--
Erland Sommarskog, SQL Server MVP, esquel@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/prodinf...ons/books.mspx

re: SP timing out, can't find the cause

See if developers use "SELECT * FROM..." in their query-s that they pass.
Make them use named fields "SELECT ID,Name, [blabla all fields just like
with * ] FROM..." and see what happens with and without "SET arithabort OFF
". Name resolving can be an issue.
Anyway arithabort handleing is good thing to do.

"Thomas R. Hummel" <tom_hummel@hotmail.comwrote in message
news:7dd3f6ce-ea03-46ad-a7e8-dce2f4e46a43@e1g2000pra.googlegroups.com...
On Nov 17, 3:43 pm, "Thomas R. Hummel" <tom_hum...@hotmail.comwrote:Looks like I've found the answer to my own question, at least
partially. SET arithabort OFF is coming from the application
connection and when I set that in my Management Studio I see the same
slowness. Now to find out why that's the case...

-Tom.