Look at the following 3 scripts:
Transact-SQL
-- 1. Hardcoded SELECT * FROM Sales.Orders WHERE DateAndTime < '19900101' -- 2. Variable DECLARE @FilterDate DATETIME = '19900101' SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate -- 3. Stored Procedure CREATE PROCEDURE Sales.GetOrders @FilterDate DATETIME2(0) AS SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate GO EXECUTE Sales.GetOrders '19900101'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 1. Hardcoded
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < '19900101'
-- 2. Variable
DECLARE @FilterDate DATETIME = '19900101'
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < @FilterDate
-- 3. Stored Procedure
CREATE PROCEDURE Sales.GetOrders
@FilterDate DATETIME2(0)
AS
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders '19900101'
All three run the same query : The first uses a hard coded filter value, the second uses a variable, and the third uses a Stored Procedure.
Same logical process but they are compiled in a very different way leading to huge different performance outcomes.
What is parameter sniffing?
When a query is compiled, the optimizer doesn’t know the value of variables. This is very important, so I’ll add some exclamation points!!!!!!!
For instance, in the following query:
Transact-SQL
-- 2. Variable DECLARE @FilterDate DATETIME = '19900101' SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate
1
2
3
4
5
6
7
8
-- 2. Variable
DECLARE @FilterDate DATETIME = '19900101'
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < @FilterDate
The optimizer doesn’t know the value of @FilterDate . Only after compilation is finished and the execution plan is be executed will SQL Server calculate the value of @FilterDate .
However, there are a few exclusions to the above rule (that the optimizer doesn’t know the value of variables) for instance: Stored Procedures, sp_executesql and a parameterized query.
In these special cases, SQL Server knows (sniffs) the values of the input parameters and uses these values when compiling the statements! This process is called parameter sniffing.
For example, in the following query:
Transact-SQL
-- Stored Procedure CREATE PROCEDURE Sales.GetOrders @FilterDate DATETIME2(0) AS SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate GO EXECUTE Sales.GetOrders '19900101'
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Stored Procedure
CREATE PROCEDURE Sales.GetOrders
@FilterDate DATETIME2(0)
AS
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders '19900101'
The optimizer at compile time knows @FilterDate = '19900101' .
What happens if we run later on the Stored Procedure with a different input? For example:
Transact-SQL
EXECUTE Sales.GetOrders '20000101'
1
EXECUTE Sales.GetOrders '20000101'
This leads us to our second rule:
When parameter sniffing is applied, the same execution plan is used regardless of the input values. Therefore, the first run of a Stored Procedure is critical, the input parameters of the first run will dictate the values used at compile time and hence on the outcome execution plan which stays in cache for future run!! So in our case, SQL Server will use the same execution plan compiled for the first run and won’t create a new plan for the following runs.
Cons and Pros of Parameter sniffing:
Pro #1:
The obvious and most definite pro of parameter sniffing is that only one compilation is needed to serve a whole range of values – we’re saving so many compilations!
Con #1:
That’s the con as well! One compilation means one execution plan for any value passed. So we’re actually reusing the same execution plan without optimizing it for that specific value. Yes, in most cases it’s OK but sometimes that’s really bad!
Look at the following scenario:
Suppose the Orders table contains many orders from 2007 till 2011. The first run of the Stored Procedure (from above): Sales.GetOrders will dictate the execution plan. So let’s first run it with a very selective value (very few rows are returned):
Transact-SQL
EXECUTE Sales.GetOrders '20000101'
1
EXECUTE Sales.GetOrders '20000101'
You can see in the Execution Plan that the optimizer estimated correctly that almost no rows would return.
However, when running the following:
Transact-SQL
EXECUTE Sales.GetOrders '20120101'
1
EXECUTE Sales.GetOrders '20120101'
The optimizer uses the same execution plan and therefore estimates miss correctly that 1 row would return. As you can see, the estimated number of rows is very different than the actual number of rows – causing the optimizer to use Lookup for many rows – a very destructive plan!
Con #2:
When an input parameter value is changed inside the Stored Procedure, the optimizer isn’t aware of the change and refers to the original value prior to the change. Look at the following code:
Transact-SQL
ALTER PROCEDURE Sales.GetOrders @FilterDate DATETIME2(0) AS IF (@FilterDate IS NULL) SET @FilterDate = SYSDATETIME() SELECT * FROM Sales.Orders WHERE DateAndTime < @FilterDate GO EXECUTE Sales.GetOrders NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER PROCEDURE Sales.GetOrders
@FilterDate DATETIME2(0)
AS
IF (@FilterDate IS NULL)
SET @FilterDate = SYSDATETIME()
SELECT
*
FROM
Sales.Orders
WHERE
DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders NULL
At compile time, the optimizer isn’t aware of the change to @FilterDate. Therefore, when running the Stored Procedure with NULL, the optimizer compiles the SELECT statement evaluating @FilterDate to be NULL and not SYSDATETIME(). You can see in the Execution Plan, the optimizer estimates no rows to return although the whole table is returned.
Of course, there are many ways to surmount these cons, I’ll go over them in my next post.
Happy New Year!