Databases Reference
In-Depth Information
Parameter Sniing
As we saw in
Chapter 3, Statistics and Cost Estimation
, SQL Server can use the histograms
of statistics objects to estimate the cardinality of a query, and then use this information to
try to produce an optimal execution plan. The Query Optimizer accomplishes this by first
inspecting the values of the query parameters.
This behavior is called parameter sniffing, and it is a very good thing: getting an execution
plan tailored to the current parameters of a query naturally improves the performance
of your applications. We also know that the plan cache can store these execution plans
so that they can be reused the next time the same query needs to be executed. This saves
optimization time and CPU resources, as the query does not need to be optimized again.
However, although the Query Optimizer and the plan cache work well together most
of the time, some performance problems can occasionally appear. Given that the Query
Optimizer can produce different execution plans for syntactically identical queries,
depending on their parameters, caching and reusing only one of these plans may create
a performance issue for alternative instances of this query which would benefit from a
better plan. This is a known problem with queries using explicit parameterization, such as
stored procedures, for example. Next, I'll show you an example of this problem, together
with a few recommendations on how to fix it.
Let's write a simple stored procedure using the
Sales.SalesOrderDetail
table on the
AdventureWorks
database:
CREATE
PROCEDURE
test
(
@pid
int
)
AS
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@pid
Listing 6-11.
