Tuesday 27 March 2012

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

2 comments:

  1. SQL Server TRIES TO always select the best execution plan for us. It doesn't always succeed. That's why hints and Optimize for commands exist. Sometimes the SQL designers don't know what the best option is, that's why the default in 2000 was page lock and in 2005 it was row lock and neither is the best idea in all situations.

    ReplyDelete