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.
--Table created CREATE TABLE SQLIndia (id INT IDENTITY(1,1) , state_code INT NOT NULL) GO --Index created CREATE INDEX idx_SQLIndia_state_code ON SQLIndia (state_code) GO --Populating dummy data into table DECLARE @i INT SET @i = 1 WHILE (@i <= 100000) BEGIN IF @i <=10 INSERT INTO SQLIndia (state_code) VALUES (3) IF (@i > 10 AND @i <= 10000) INSERT INTO SQLIndia (state_code) VALUES (19) IF (@i > 10000 AND @i <= 100000) INSERT INTO SQLIndia (state_code) VALUES (1989) SET @i = @i + 1 END GO --Updated the statistics after data insert UPDATE STATISTICS SQLIndia WITH SAMPLE 100 PERCENT, ALL
We have inserted some 100000 dummy records into our SQLIndia table.
--Turn on the actual execution plan (CTRL + M) DECLARE @state_code INT SET @state_code = 3 SELECT * FROM SQLIndia WHERE state_code = @state_code
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.
/* USE [dbTemp] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[SQLIndia] ([state_code]) INCLUDE ([id]) GO */
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.
--Turn on the actual execution plan (CTRL + M) DECLARE @state_code INT SET @state_code = 3 SELECT * FROM SQLIndia WHERE state_code = @state_code OPTION (OPTIMIZE FOR (@state_code = 3))
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