By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Stuck execution plan?

P: n/a
Using SQL Server 2000 SP4.

There is a relatively complex stored procedure that usually completes in
less than 20 seconds. Occasionally it times out after 180 seconds. The SP
is called via ADO 2.8, using adCmdStoredProc command type. If I use
Profiler to capture the EXEC that ADO sends to run the procedure, and run
that from QA, the procedure completes in less than 20 seconds as it should.

The procedure is created WITH RECOMPILE. One additional twist is that
sp_setapprole is called from the client before running the procedure in
question. This may be irrelevant, because even if I include the same
sp_setapprole call when running the procedure from QA, it still executes
quickly, and even if I comment out the call to sp_setapprole in the client
code, the proc still times out when run from the client.

The only thing that fixes it, at least for a day or two, is DBCC
FREEPROCCACHE. So it appears that a bad plan is somehow stuck in memory and
is only used when the procedure is called from the client app, and is not
flushed even though the procedure was created WITH RECOMPILE.

Other than scheduling the DBCC call to run every night, is there anything
else I could try to get this resolved? Thanks.

--
(remove a 9 to reply by email)
May 2 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Dimitri Furman (df*****@cloud99.net) writes:
There is a relatively complex stored procedure that usually completes in
less than 20 seconds. Occasionally it times out after 180 seconds. The
SP is called via ADO 2.8, using adCmdStoredProc command type. If I use
Profiler to capture the EXEC that ADO sends to run the procedure, and
run that from QA, the procedure completes in less than 20 seconds as it
should.
If you in this situation issue SET ARITHABORT OFF from QA, what happens?

This may seem like a crazy thing, but ADO connects by default with
ARITHABORT OFF, whereas QA swears by a default of ON. This is one of the
options that are saved with the query plan, so different settings, different
query plans. (But this does not mean that any or either setting affects
the performance. It's just that QA gets a fresh deal.)
The procedure is created WITH RECOMPILE.
...
The only thing that fixes it, at least for a day or two, is DBCC
FREEPROCCACHE. So it appears that a bad plan is somehow stuck in memory
Nah, I would rather look for something that is being invoked by the
procedure: a trigger, a used-defined function or an inner stored procedure.
Other than scheduling the DBCC call to run every night, is there anything
else I could try to get this resolved? Thanks.


You need to track down exactly which piece of codes that all of a sudden
takes a long time. Profiler is good for this, although it may be trouble
some if the problem is in a UDF.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '06 #2

P: n/a
On May 02 2006, 11:36 am, Erland Sommarskog <es****@sommarskog.se> wrote
in news:Xn*********************@127.0.0.1:
Dimitri Furman (df*****@cloud99.net) writes:
There is a relatively complex stored procedure that usually completes
in less than 20 seconds. Occasionally it times out after 180 seconds.
The SP is called via ADO 2.8, using adCmdStoredProc command type. If
I use Profiler to capture the EXEC that ADO sends to run the
procedure, and run that from QA, the procedure completes in less than
20 seconds as it should.


If you in this situation issue SET ARITHABORT OFF from QA, what
happens?

This may seem like a crazy thing, but ADO connects by default with
ARITHABORT OFF, whereas QA swears by a default of ON. This is one of
the options that are saved with the query plan, so different settings,
different query plans. (But this does not mean that any or either
setting affects the performance. It's just that QA gets a fresh deal.)


Well, I had to do the DBCC thing so I can't repro this right now, but will
check this the next time it happens. If this turns out to be the cause, I
guess I'll just need to set ARITHABORT to ON from ADO on the same
connection prior to running the proc, right? At least this will make the
behavior consistent between the app and QA, even though it probably won't
fix the issue.
The procedure is created WITH RECOMPILE.
...
The only thing that fixes it, at least for a day or two, is DBCC
FREEPROCCACHE. So it appears that a bad plan is somehow stuck in
memory


Nah, I would rather look for something that is being invoked by the
procedure: a trigger, a used-defined function or an inner stored
procedure.


No triggers or inner procedures, but there are a few UDFs, one of which (a
fairly complex multi-statement table-valued one) is suspicious. So is it
possible that there is a separate plan for that function, which does not
get recompiled even though the calling proc is WITH RECOMPILE? If that's
the case, how can I force recompilation for a UDF? There is no WITH
RECOMPILE for functions, is there?
Other than scheduling the DBCC call to run every night, is there
anything else I could try to get this resolved? Thanks.


You need to track down exactly which piece of codes that all of a
sudden takes a long time. Profiler is good for this, although it may
be trouble some if the problem is in a UDF.


--
(remove a 9 to reply by email)
May 2 '06 #3

P: n/a
Dimitri Furman (df*****@cloud99.net) writes:
Well, I had to do the DBCC thing so I can't repro this right now, but will
check this the next time it happens. If this turns out to be the cause, I
guess I'll just need to set ARITHABORT to ON from ADO on the same
connection prior to running the proc, right? At least this will make the
behavior consistent between the app and QA, even though it probably won't
fix the issue.
Yeah, but it's easier to change the setting from QA.

Then again, it's a good habit to always turn on ARITHABORT from ADO,
as the setting must be on if you work with indexed views and indexed
computed columns.
No triggers or inner procedures, but there are a few UDFs, one of which (a
fairly complex multi-statement table-valued one) is suspicious. So is it
possible that there is a separate plan for that function, which does not
get recompiled even though the calling proc is WITH RECOMPILE? If that's
the case, how can I force recompilation for a UDF? There is no WITH
RECOMPILE for functions, is there?


Correct, that appears to be a missing feature. And neither can you say
"sp_recompile table_udf". But if there is a table that is referred to by
the UDF and not the surrounding SP, you can say "sp_recompile" on that
table next time this happens. If performance then comes back to normal,
then you know that the UDF is the culprit.

A possible trick to force a recompile each time is to add this line
first in the procedure:

SET ANSI_NULLS OFF

Since this setting is saved with the function, it's a dummy operation,
but it nevertheless causes a recompilation of a stored procedure. I
have not verified this with a table function. (You could still run into
trouble with indexed views if you do this.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '06 #4

P: n/a
On May 02 2006, 05:28 pm, Erland Sommarskog <es****@sommarskog.se> wrote
in news:Xn**********************@127.0.0.1:
Dimitri Furman (df*****@cloud99.net) writes:
No triggers or inner procedures, but there are a few UDFs, one of
which (a fairly complex multi-statement table-valued one) is
suspicious. So is it possible that there is a separate plan for that
function, which does not get recompiled even though the calling proc
is WITH RECOMPILE? If that's the case, how can I force recompilation
for a UDF? There is no WITH RECOMPILE for functions, is there?


Correct, that appears to be a missing feature. And neither can you say
"sp_recompile table_udf". But if there is a table that is referred to
by the UDF and not the surrounding SP, you can say "sp_recompile" on
that table next time this happens. If performance then comes back to
normal, then you know that the UDF is the culprit.

A possible trick to force a recompile each time is to add this line
first in the procedure:

SET ANSI_NULLS OFF

Since this setting is saved with the function, it's a dummy operation,
but it nevertheless causes a recompilation of a stored procedure. I
have not verified this with a table function. (You could still run
into trouble with indexed views if you do this.)


Well, of course this function uses not one but two indexed views...

What if I set ANSI_NULL_DFLT_ON to OFF within the function? If I am reading
KB243586 correctly, this should cause a recompile, and I believe it won't
interfere with indexed views.

If that doesn't work, I think I can create a dummy table, reference it in
the function, and call sp_recompile on that table prior to calling the
procedure.

--
remove a 9 to reply by email
May 3 '06 #5

P: n/a
Dimitri Furman (df*****@cloud99.net) writes:
Well, of course this function uses not one but two indexed views...
Ohoh! Then it's essential that ARITHABORT is ON. If it is not, SQL Server
will disregard the view and read from the base tabels.
If that doesn't work, I think I can create a dummy table, reference it in
the function, and call sp_recompile on that table prior to calling the
procedure.


Ouch! That's a kludge!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.