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.
- Martin
We'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

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 tw
o
given the same parameters. It seems to be doing a ton of I/O's when it's slo
w
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 unpredictabl
e
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

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

GK - Blocking the unauthorized use of the SQL Management Console ( or third party tool

Hi to all,
Whenever SQL server is installed onto a server it can be accessed by the SQL
MC and easily registered for use. Whenever someone not authorized installs
the SQL MC or a third party tool with the same capabilities all database con
tent can be altered !! THIS
IS A VERY SERIOUS THREAD !
Is there a way to prevent this?
GKramer
The NetherlandsCan you please elaborate on your concerns? Only users who have been granted
access to SQL Server can connect and access database objects. When SQL
Server is initially installed, only members of the local Administrators
group can connect. Once you grant an account access to SQL Server, they can
use any tool but are limited by the SQL Server permissions you have granted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:2BDA7DD5-3FDE-45CC-BD95-BDCD8AD01BDE@.microsoft.com...
> Hi to all,
> Whenever SQL server is installed onto a server it can be accessed by the
SQL MC and easily registered for use. Whenever someone not authorized
installs the SQL MC or a third party tool with the same capabilities all
database content can be altered !! THIS IS A VERY SERIOUS THREAD !
> Is there a way to prevent this?
> GKramer
> The Netherlands
>