OPTION OPTIMIZE FOR – Parameter sniffing in SQL Server

OPTIMIZE FOR is a query hint introduced in SQL Server 2005 version and supported in all editions of SQL Server. This query hint can be applied to all DML operations like SELECT, INSERT, UPDATE, DELETE AND MERGE.

The OPTIMIZE FOR query hint allows us to override the incorrect SQL parameter sniffing happens during plan optimization. Parameter sniffing is the default behavior of SQL Server, which determines the query execution plan at compile time and judges the selectivity of our data based on the parameter values. If the data are not pretty selective in nature then the query optimizer may surprise us by choosing a scan on the table or clustered index, even though there is an appropriate index on it. Specifically this hint works with the optimizer to decide the index utilization over huge statistical data.

Let’s create the environment and populate some dummy data into a table called SQLIndia, on which we are going to do some workaround for this demo.

We have inserted some 100000 dummy records into our SQLIndia table.

OPTION_OPTIMIZE_01

We could see the above query just did a scan on the table and showing us a missing index, even though we have already an index key created on the “state_code” column. Below is the missing index detail, which is the same index we have already on the table.

Note: We do not have a clustered index on the table neither a primary key so the query just did a table scan otherwise it would have scanned the clustered index.

Now let’s re-execute the same query with OPTIMIZE FOR query hint.

OPTION_OPTIMIZE_02

Now the same query is doing an index seek instead of table scan. Remember we did not create the missing index, it used the old index we have already on the table.

OPTION (OPTIMIZE FOR UKNOWN)

This hint was added later in SQL Server 2008 version. It directs the query optimizer to use the standard algorithms. In this case the optimizer will look in generating the query plan, rather looking at specific parameter values. This is essentially the same as using WITH RECOMPILE in the stored procedure declaration, except it only applies to a specific statement in the procedure or batch. Parameterized query technique is a well known SQL Server Best Practice and considered as performance tuning step. This technique ensures caching and reuse of existing query execution plans instead of constantly compiling new plan.

Note: If UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “OPTION OPTIMIZE FOR – Parameter sniffing in SQL Server

Leave a Reply

%d bloggers like this: