OPTIMIZE FOR

In this article I am trying to explain about how the parameter sniffing is solved by OPTIMIZE FOR options and also give u a glance of new SQL server 2008 OPTIMIZE FOR UNKNOWN query hint.

SQL Server always select the best execution plan for us, and we have different hints that can be forced SQL Server to using one execution plan over another.

Parameter sniffing

Think about the situation when hardcoded value in WAHRE clause, sometimes the query is running so grate and when u changed the hardcoded value in the WHERE clauses it takes Long time to executes. This type of situation is referred to as parameter sniffing.

Here the SQL Server stores the value of parameter as execution plan and for that the query with different value acts as totally different. In my case the query takes a longer time to execute.

So what we do

The SQL Server 2005 provides a query hint called OPTIMIZE FOR options, this allow us to specify what parameter values we want SQL Server to use when creating execution plan.

Process-1

The first example is a simple query without OPTINIZE hint mentioned bellow.

DECLARE @Country VARCHAR(20)

SET @Country = 'IND'    

SELECT *

FROM   Sales.SalesOrderHeader h

       INNER JOIN Sales.Customer c ON h.CustomerID = c.CustomerID

       INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID

WHERE  CountryRegionCode = @Country

If we analyse the execution plan we found that:

SELECT

Cached plan size                              112B

Degree of parallelism                       0

Memory Grant                                   395

Estimated Operation Cost               0(0%)

Estimated Subtree Cost                   1.31871

Estimated Numbers of Rows          5362.94          

The overall cost of this query is 1.31871

Process-2

In this example we are using OPTIMIZE FOR hint.

DECLARE @Country VARCHAR(20)

SET @Country = 'IND'    

SELECT *

FROM   Sales.SalesOrderHeader h

       INNER JOIN Sales.Customer c ON h.CustomerID = c.CustomerID

       INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID

WHERE  CountryRegionCode = @Country

 OPTION (OPTIMIZE FOR (@Country = 'BNG'))

The first part is identical to the first example. In this example tells the SQL server to optimize the query using “BNG” as the parameter values.

SELECT

Cached plan size                              112B

Degree of parallelism                       0

Memory Grant                                   294

Estimated Operation Cost               0(0%)

Estimated Subtree Cost                   1.1805

Estimated Numbers of Rows          3236.19          

The overall costs of this query are: 1.1805 which is better then the first example.

Process-4

In this example we have changed the OPTIMIZE FOR value to be “IND” instead of “BNG”, everything else is same.

DECLARE @Country VARCHAR(20)

SET @Country = 'IND'    

SELECT *

FROM   Sales.SalesOrderHeader h

       INNER JOIN Sales.Customer c ON h.CustomerID = c.CustomerID

       INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID

WHERE  CountryRegionCode = @Country

OPTION (OPTIMIZE FOR (@Country = 'IND'))

SELECT

Cached plan size                              112B

Degree of parallelism                       0

Memory Grant                                   582

Estimated Operation Cost               0(0%)

Estimated Subtree Cost                   1.160652

Estimated Numbers of Rows          15996.7          

The overall cost for this query is 1.160652 which is not as good as the first two examples.

New feature in SQL Server 2008

OPTIMIZE FOR UNKNOWN which was introduced in SQL server 2008 does not require for us to specify a value for parameters.

Optimizer for unknown instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

Basically the query optimizer will ignore the parameter value during query optimization and will use the same query execution plan for  each values.

Example:

SELECT *

FROM   Sales.SalesOrderHeader h

       INNER JOIN Sales.Customer c ON h.CustomerID = c.CustomerID

       INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID

WHERE  CountryRegionCode = @Country

 OPTION (OPTIMIZE FOR UNKNOWN)

Summary

Using OPTIMIZE FOR hint can change the query plan and this may have positive or negative impact, but it gives you an option to adjust the queries executions when using parameters in your query.

Posted by: MR.JOYDEEP DAS