• Madeira Team

Pro and Cons of Parameter Sniffing

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