| Hal Berenson 2002-11-09, 6:23 pm |
| Ad-hoc queries that specify exactly the values to be used (e.g., col1 =
"jones") are optimized according to the value(s) you specify. Assuming the
query isn't so simple that auto-paramterization kicks in, you will get a new
plan optimized for the specific values provided each time you submit the
query.
Stored Procedures are optimized for the values provided as parameters (e.g.,
col1 = @p1) the first time the Stored Procedure is executed. The plan that
results is then re-used for as long as it sits in cache (or until some other
event, such as a major change in statistics) causes a re-optimiation. 99.5%
of the time re-use of the plan proves fine for all reasonable values. 0.5%
of the time the value used in the initial call to the SP is atypical, and/or
data in the table is heavily skewed, and/or the parameter values used differ
dramatically, and/or the nature of the query is such that reusing the same
plan yields poor results. If this is the problem you are having, you can
mark the SP so that it is recompiled on each invocation. See the WITH
RECOMPILE option on CREATE/ALTER PROCEDURE.
A related problem is that the optimizer can only optimize for values that
are actually passed in to the SP (known as paramater sniffing). The
optimizer can't optimize for values calculated in the SP itself. In this
latter case the optimizer uses some magic numbers for calculating plans,
which could themselves turn out to be non-representative of the actual
values your application will use. For example:
SELECT @newid = @p1 * @p2
SELECT * FROM t1 where col1 > @newid
gives the optimizer fits. It has no idea what the value of @newid might be
at actual execution, so it just has to guess what percentage of the values
in col1 will be greater. That guess can lead to really poor results if, for
example, @newid is either close to the beginning or the end of the values in
col1.
WITH RECOMPILE will not help in this situation, so you need to restructure
the SP. Either place the query itself in another SP that this SP calls, or
call sp_executesql from within the SP to execute the query (passing @newid
as its parameter). You can combine the former with WITH RECOMPILE if you
need a new plan for each new value of @newid.
You can play around with this stuff, and if it doesn't solve your problem
post the SP and someone will take a look.
--
Hal Berenson
True Mountain Consulting
"DSEIFRIED" <DON@A-T-G.COM> wrote in message
news:42e601c28771$cc273150$35e
f2ecf@TKMSFTNGXA11...
> I take a stored procedure and an ADHOC query....use the
> same 4 parameters and execute both. The sp takes 7
> seconds. The adhoc query takes 1. The execution plans
> are different also.
>
> I delete and recreated the SP thinking a bad plan would be
> in cache. I verified it wasn't using syscacheobjects and
> then recreated the SP. I get the same results.
>
> I'm struggling to troubleshoot this. In the morning the
> SP and Adhoc are closer and as the day goes on the SP gets
> slower. I have verified that there is still plenty of
> memory so cache isn't overloaded.
>
> Any ideas? This has happened 3 times this week. I would
> greatly appreciate any direction.
>
> Thanks
|