|
|
Posted by Erland Sommarskog on 09/15/06 22:15
cbrichards via SQLMonster.com (u3288@uwe) writes:
> I have a stored procedure that will execute with less than 1,000 reads
> one time (with a specified set of parameters), then with a different set
> of parameters the procedure executes with close to 500,000 reads
> (according to Profiler).
>
> In comparing the execution plans, they are the same, except for the
> actual and estimated number of rows. When the proc runs with parameters
> that produce reads that are less than 1,000 the actual and estimated
> number of rows equal 1. When the proc runs with parameters that produce
> reads are near 500,000 the actual rows are approximately 85,000 and the
> estimated rows equal 1.
>
> Then I run:
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
>
> If I then reverse the order of execution by executing the procedure that
> initially executes with close to 500,000 reads first, the reads drop to
> less than 2,000. The execution plan shows the acutual number of rows
> equal to 1, and the estimated rows equal to 2.27. Then when I run the
> procedure that initially executed with less than 1,000 reads, it
> continues to run at less than 1,000 reads, and the actual number of rows
> is equal to 1 and the estimated rows equal to 2.27. When run in this
> order, there is consistency in the actual and estimated number of rows
> and the reads for both executions with differing parameters are within
> reason.
So this is what is happening: SQL Server engage in something known as
parameter sniffing. When SQL Server runs a stored procedure for which
there is no plan available in the procedure cache, the optimizer builds
a plan for the procedure. It then uses the actual values of the input
parameters as guidance for the plan.
This can sometimes backfire, for instance if the first invocation is
for an atypical value. It appears that in your case, there is little
difference for one of the input cases, but the balance tips over in
favour of a plan that is detrimental for the other input cases.
The situation is not entirely trivial to handle. There are several
options, but all have their drawbacks.
To start with DBCC FREEPROCACHE, since this affects the entire server, this
is a really bad idea.
If you always want the same plan, you can use an index hint, so that
say
dbo.Table_Z z WITH (INDEX = IDX1_pFID)
to force the use of the non-clustred index. This may still not give
the plan you want, though, as the optimizer can choose some other
combination. (On SQL 2005 you can force the exact plan if you wish.) A
more seroius problem with this approach is that in two years when
the data has changed, another plan is much better. So index hints should
only be used when you are convinced that the index in question is
always the best.
Another alternative is to add WITH RECOMPILE to the procedure definition.
In this case the procedure gets recompiled every time it's called, which
means that you can get a new plan each time. The obvious drawback is
that you get a higher overhead. If the procedure is just that statement,
it's not that big deal, but if the procedure is several hundread lines
long, there is reason for concern.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|