2
About the Speaker Benjamin Nevarez Author of “Inside the SQL Server Query Optimizer” and “SQL Server 2014 Query Tuning & Optimization” Working with SQL Server for 15 years 2

3
Query Optimizer - purpose It analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans, and selects the plan with the lowest cost of the choices considered. Requires a lot of resources (mostly CPU, optimization time) 3

5
Parameterization Parameterized queries: query plan can be reused many times even if the parameter value changes Query not explicitly parameterized: in most cases plan can only be reused with the exact parameter value 5

10
Parameter Sniffing 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. However, some performance problems can occasionally appear 10

11
Parameter Sniffing 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 11

12
Parameter Sniffing Demo Using the statistics histogram Producing two distinct plans for the same query 12

13
Optimize for a typical parameter Most of the executions of a query use the same plan Avoid an ongoing optimization cost ALTER PROCEDURE test int) AS SELECT * FROM Sales.SalesOrderDetail WHERE ProductID OPTION (OPTIMIZE FOR = 897)) 13

14
Optimize for a typical parameter Demo 14

15
Optimize on every execution Best execution plan for every query You end up paying for the optimization cost ALTER PROCEDURE test int) AS SELECT * FROM Sales.SalesOrderDetail WHERE ProductID OPTION (RECOMPILE) 15

16
Optimize on every execution Demo 16

17
OPTIMIZE FOR UNKNOWN and Local Variables Disables parameter sniffing Query Optimizer uses the density information of the statistics object (instead of the histogram) Ignore parameters, uses the same plan 17

18
OPTIMIZE FOR UNKNOWN and Local Variables ALTER PROCEDURE test int) AS SELECT * FROM Sales.SalesOrderDetail WHERE ProductID OPTION (OPTIMIZE FOR UNKNOWN) 18

19
OPTIMIZE FOR UNKNOWN and Local Variables Demo OPTIMIZE FOR UNKNOWN Local Variables Using the statistics density 19