Good query goes bad

Hello,
We have a 3rd party app that uses "sp_executesql" to run a stored procedure.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or two
given the same parameters. It seems to be doing a ton of I/O's when it's slow
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictable
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- MartinWe've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James