Database Reference
In-Depth Information
Chapter 16
Parameter Sniffing
In the previous chapter, I discussed how to get execution plans into cache and how to get them reused. It's a laudable
goal and one of the many ways to improve the overall performance of the system. One of the best mechanisms
for ensuring plan reuse is to parameterize the query, through either stored procedures, prepared statements, or
sp_executesql
. All these mechanisms create a parameter that is used instead of a hard-coded value when creating
the plan. These parameters can be sampled, or sniffed, by the optimizer to use the values contained within when
creating the execution plan. When this works well, as it does most of the time, you benefit from more accurate
plans. But when it goes wrong and becomes bad parameter sniffing, you can see serious performance issues.
In this chapter, I cover the following topics:
•
The helpful mechanisms behind parameter sniffing
•
How parameter sniffing can turn bad
•
Mechanisms for dealing with bad parameter sniffing
Parameter Sniffing
When a parameterized query is sent to the optimizer and there is no existing plan in cache, the optimizer will perform
its function to create an execution plan for manipulating the data as requested by the T-SQL statement. When this
parameterized query is called, the values of the parameters are set, either through your program or through defaults
in the parameter definitions. Either way, there is a value there. The optimizer knows this. So, it takes advantage of that
fact and reads the value of the parameters. This is the “sniffing” aspect of the process known as
parameter sniffing
.
With these values available, the optimizer will then use those specific values to look at the statistics of the data to
which the parameters refer. With specific values and a set of accurate statistics, you'll get a better execution plan. This
beneficial process of parameter sniffing is running all the time, automatically, for all your parameterized queries,
regardless of where they come from.
You can also get sniffing of local variables. Before proceeding with that, though, let's delineate between a local
variable and a parameter since, within a T-SQL statement, they can look the same. This example shows both a local
variable and a parameter:
CREATE PROCEDURE dbo.ProductDetails
(@ProductID INT)
AS
DECLARE @CurrentDate DATETIME = GETDATE();
SELECT p.Name,
p.Color,
p.DaysToManufacture,
pm.CatalogDescription
