Execution plan whackyness

Ok, I have this stored proc that does a lot of things and as of recently
it runs really really slow, like 2 minutes (versus 7-8 seconds before).
In an effort isolate the problem, I cut and pasted the stored proc
source code into Query Analyzer. I then replaced the sproc parameters
with declarations and assigned them values that I pass into the stored
proc. Then I ran it. The source code itself runs fast (7-8 seconds as
the sproc did before). In addition, the query plan is different between
the 2 scenarios.
What could be causing anything like this?You might want to check to ensure the stats are updated and check
fragmentation.
--
Thomas
"Frank Rizzo" wrote:
> Ok, I have this stored proc that does a lot of things and as of recently
> it runs really really slow, like 2 minutes (versus 7-8 seconds before).
> In an effort isolate the problem, I cut and pasted the stored proc
> source code into Query Analyzer. I then replaced the sproc parameters
> with declarations and assigned them values that I pass into the stored
> proc. Then I ran it. The source code itself runs fast (7-8 seconds as
> the sproc did before). In addition, the query plan is different between
> the 2 scenarios.
> What could be causing anything like this?
>|||Hi there,
fragmenatation is not an issue because it would hurt both the SP and the
Ad-hoc query and it doesn't seem like the ad-hoc query has problems. Have
you tried to recompile your SP ?
Try
"EXEC Your_SP With Recompile".
If it generates the same execution plan as the Ad-hoc query then run
"Exec SP_Recompile Your_SP". This will "mark" the SP for a recompilation
at next execute and generate and cache the new executionplan. The problem in
SP's sometimes is that the exeplan is static. It is generated and cached at
first execute and then reused. Eventually check if your SP is recompiling
while executing. You can trace that with your Profiler.
Regards :)
Bobby Henningsen
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:93A1541E-2C06-4670-BC54-0747A865BBFB@.microsoft.com...
> You might want to check to ensure the stats are updated and check
> fragmentation.
> --
> Thomas
>
> "Frank Rizzo" wrote:
>> Ok, I have this stored proc that does a lot of things and as of recently
>> it runs really really slow, like 2 minutes (versus 7-8 seconds before).
>> In an effort isolate the problem, I cut and pasted the stored proc
>> source code into Query Analyzer. I then replaced the sproc parameters
>> with declarations and assigned them values that I pass into the stored
>> proc. Then I ran it. The source code itself runs fast (7-8 seconds as
>> the sproc did before). In addition, the query plan is different between
>> the 2 scenarios.
>> What could be causing anything like this?|||There is a big difference between a parameter, variable and constant. In short:
WHERE col = @.parm
The optimizer will sniff the parm value from the call if the proc and determine selectivity based on
that.
WHERE col = @.var
The optimizer has no idea about the value of the variable and have to guess. In some case it can use
density, but it still doesn't know the value of the variable.
WHERE col = 23
Optimizer does know the value and determine selectivity based on that.
I suggest you google on "parameter sniffing" for more info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Rizzo" <none@.none.com> wrote in message news:OtyVwOk5FHA.620@.TK2MSFTNGP12.phx.gbl...
> Ok, I have this stored proc that does a lot of things and as of recently it runs really really
> slow, like 2 minutes (versus 7-8 seconds before). In an effort isolate the problem, I cut and
> pasted the stored proc source code into Query Analyzer. I then replaced the sproc parameters with
> declarations and assigned them values that I pass into the stored proc. Then I ran it. The
> source code itself runs fast (7-8 seconds as the sproc did before). In addition, the query plan
> is different between the 2 scenarios.
> What could be causing anything like this?|||I am sorry, I neglected to mention in the original post that I tried
this approach as well as creating the sproc with the recompile option to
begin with.
Bobby Henningsen wrote:
> Hi there,
> fragmenatation is not an issue because it would hurt both the SP and the
> Ad-hoc query and it doesn't seem like the ad-hoc query has problems. Have
> you tried to recompile your SP ?
> Try
> "EXEC Your_SP With Recompile".
> If it generates the same execution plan as the Ad-hoc query then run
> "Exec SP_Recompile Your_SP". This will "mark" the SP for a recompilation
> at next execute and generate and cache the new executionplan. The problem in
> SP's sometimes is that the exeplan is static. It is generated and cached at
> first execute and then reused. Eventually check if your SP is recompiling
> while executing. You can trace that with your Profiler.
> Regards :)
> Bobby Henningsen
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:93A1541E-2C06-4670-BC54-0747A865BBFB@.microsoft.com...
>>You might want to check to ensure the stats are updated and check
>>fragmentation.
>>--
>>Thomas
>>
>>"Frank Rizzo" wrote:
>>
>>Ok, I have this stored proc that does a lot of things and as of recently
>>it runs really really slow, like 2 minutes (versus 7-8 seconds before).
>> In an effort isolate the problem, I cut and pasted the stored proc
>>source code into Query Analyzer. I then replaced the sproc parameters
>>with declarations and assigned them values that I pass into the stored
>>proc. Then I ran it. The source code itself runs fast (7-8 seconds as
>>the sproc did before). In addition, the query plan is different between
>>the 2 scenarios.
>>What could be causing anything like this?
>
>|||On Thu, 10 Nov 2005 14:11:21 -0800, Frank Rizzo <none@.none.com> wrote:
>Ok, I have this stored proc that does a lot of things and as of recently
>it runs really really slow, like 2 minutes (versus 7-8 seconds before).
> In an effort isolate the problem, I cut and pasted the stored proc
>source code into Query Analyzer. I then replaced the sproc parameters
>with declarations and assigned them values that I pass into the stored
>proc. Then I ran it. The source code itself runs fast (7-8 seconds as
>the sproc did before). In addition, the query plan is different between
>the 2 scenarios.
>What could be causing anything like this?
Does the SP run fast or slow when called from QA?
I've been plagued recently with SPs that run fine when called
individually but pathologically slowly when called in production under
load, even with the same parameters.
My only "fix" is recoding with ever-simpler intermediate steps until
it behaves better even under load. I found one where it was the
unusual data configuration only under load (that was actually on a QA
box, not production), on the others, I just don't know.
This problem may be worse under sp4. That is, we made some database
changes about the same time as installing sp4, and have noticed the
problems more recently. However, I do believe they also occurred on
sp3a. I have not yet been able to run the production system with the
profiler capturing query plans, though I guess I'd better do so, if I
can find an appropriate filter so the trace doesn't slow production
and generate gigabytes of trace per minute.
J.