A stored-procedure becomes slow and needs re-creation

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow until I drop the SP and re-create it.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds (before it is re-created).
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsSounds like the execution plan deviates significantly from the actual
data over time. You may want to try using the WITH RECOMPILE option to
force the SP to recompile every time it runs.
Stu

A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds
I've tried recompiling the procedure, as well as drop and create it again,
but it doesn't help.
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsFirst thing: Google for "Parameter sniffing", make sure you understand that
concept.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message news:OerL4M8ZGHA.3880@.TK2MSFTNGP04.phx
.gbl...
> Hi all
> I have a SP that beahves strange. Originally it takes about 20 millisecond
s
> to complete, but sometimes it starts going slow and take about 5-7 seconds
.
> When this happens, it keeps going slow.
> I tried to run the SQL body of the SP in the Query analyzer, and it runs
> fast (20 ms), while the SP takes 5-7 seconds
> I've tried recompiling the procedure, as well as drop and create it again,
> but it doesn't help.
> Does anyone knows what can cause this and what is the soloution ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>|||Could be parameter sniffing, can you show the code?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi Denis
Disabling parameter sniffing seems to work here. If it is still too slow
I'll send the code (which a bit messy). If not, I wouldn't waist your time.
Thanks
Boaz Be-Porat
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145899699.966141.109220@.y43g2000cwc.googlegroups.com...
> Could be parameter sniffing, can you show the code?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>