Multiple OPTIMIZE FOR Hints in One Query: Value or UNKNOWN

Multiple OPTIMIZE FOR hints in one query are written as a single list. Each parameter gets its own value or UNKNOWN. The hint tells the optimizer which values to plan for, instead of the ones that happen to arrive first. The list lets you pick a real value for one parameter and leave another open.

Why a Hint Per Parameter

SQL Server builds a plan with the parameter values of the first call and reuses it. That is parameter sniffing. When the data is skewed, the first call decides the plan for everyone. If a rare value arrives first, the plan suits rare values and hurts the common ones.

OPTIMIZE FOR fixes the values used at compile time. With one parameter it is simple. With two, each parameter can need a different answer. One is skewed and worth planning for, and the other is not. For the other ways to deal with sniffing, see Parameter Sniffing Fixes Compared: Which One to Use.

The Demo Table

The database is OptimizeHintDemo. The items table has 100,000 rows. Half of the rows are Toy, 48 percent are Book and 2 percent are Gadget. StockNumber is 1 for 30 percent of the rows and spread out for the rest. Each column has its own index. Run it on a test server.

IF DB_ID(N'OptimizeHintDemo') IS NULL CREATE DATABASE OptimizeHintDemo;
GO
USE OptimizeHintDemo;
GO
DROP TABLE IF EXISTS dbo.Items;
CREATE TABLE dbo.Items (
    ItemID      int NOT NULL CONSTRAINT PK_Items PRIMARY KEY,
    ProductName varchar(20) NOT NULL,
    StockNumber int NOT NULL
);
INSERT INTO dbo.Items (ItemID, ProductName, StockNumber)
SELECT n,
       CASE WHEN n % 100 < 50 THEN 'Toy' WHEN n % 100 < 98 THEN 'Book' ELSE 'Gadget' END,
       CASE WHEN n % 10 < 3 THEN 1 ELSE n % 1000 + 2 END
FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
CREATE INDEX IX_Items_ProductName ON dbo.Items (ProductName);
CREATE INDEX IX_Items_StockNumber ON dbo.Items (StockNumber);

Six Ways to Compile One Query

The script below runs the same query six times through sp_executesql. It tries multiple OPTIMIZE FOR hints of different kinds. Every call passes the rare values Gadget and 7. A short comment names each variant. The first call has no hint, so SQL Server plans for Gadget and 7. The others add an OPTIMIZE FOR hint of a different kind. Clearing the procedure cache of this database first keeps old plans out of the way.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @p nvarchar(max) = N'@ProductName varchar(20), @StockNumber int, @id int OUTPUT';
EXEC sp_executesql N'/*QA*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber;',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;
EXEC sp_executesql N'/*QB*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber
     OPTION (OPTIMIZE FOR UNKNOWN);',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;
EXEC sp_executesql N'/*QC*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber
     OPTION (OPTIMIZE FOR (@ProductName = ''Toy'', @StockNumber UNKNOWN));',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;
EXEC sp_executesql N'/*QD*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber
     OPTION (OPTIMIZE FOR (@ProductName = ''Toy'', @StockNumber = 1));',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;
EXEC sp_executesql N'/*QE*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber
     OPTION (OPTIMIZE FOR UNKNOWN, OPTIMIZE FOR (@ProductName = ''Toy''));',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;
EXEC sp_executesql N'/*QF*/ SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName AND StockNumber = @StockNumber
     OPTION (OPTIMIZE FOR (@ProductName = ''Toy''), MAXDOP 1);',
     @p, @ProductName = 'Gadget', @StockNumber = 7, @id = NULL;

Read the Compile Values From the Plan

The cached plan records the values it was compiled for and the rows it expected. This query reads both for the six statements. It filters on the comment tags, and the pattern with a letter range cannot match its own text.

SELECT SUBSTRING(st.text, CHARINDEX(N'/*Q', st.text) + 3, 1) AS Variant,
       CAST(qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:RelOp)[1]/@EstimateRows', 'float') AS decimal(12,1)) AS EstimatedRows,
       qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:ParameterList/p:ColumnReference[@Column="@ProductName"]/@ParameterCompiledValue)[1]', 'varchar(30)') AS NameCompiledAs,
       qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:ParameterList/p:ColumnReference[@Column="@StockNumber"]/@ParameterCompiledValue)[1]', 'varchar(30)') AS StockCompiledAs
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE st.text LIKE N'%/*Q[A-F]*/ SELECT @id = ItemID FROM dbo.Items%'
ORDER BY Variant;

If your server has optimize for ad hoc workloads on, run the six statements twice before you read the plans.

How to Read the Table

Variant A shows sniffing. The plan was built for Gadget and 7, and it expects 2 rows. Every other call that reuses it is judged by that guess.

Variant B uses UNKNOWN for both parameters, so no value is compiled and the estimate comes from average density. Variant C is the answer to the original question. It plans for the common value Toy and leaves StockNumber open, which gives 71.3. Variant D names a value for both parameters and expects 15,000 rows, because StockNumber 1 is the skewed value. That plan is built for a large result. A call for Gadget and 7 then receives that plan.

Variant E combines a plain OPTIMIZE FOR UNKNOWN with a list that names one parameter. It produced the same plan values as C. So the plain form covers every parameter that the list leaves out. Multiple OPTIMIZE FOR hints in one OPTION clause are accepted, and the named value Toy won over the general UNKNOWN. Variant F names only ProductName and adds MAXDOP 1 to the same OPTION clause. StockNumber keeps its first value, 7, because nothing says otherwise.

What SQL Server Refuses

A hint can name a variable once. A second value for the same variable fails with Msg 4131. A variable that the query does not declare fails with Msg 137. Both come at compile time, so a typo shows up on the first run. The OPTION clause works the same way inside a stored procedure body, where the parameters are real procedure parameters.

Both mistakes are easy to reproduce. The first call below names @ProductName twice. The second names a variable that the query never declares. Run each call on its own.

EXEC sp_executesql N'SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName
     OPTION (OPTIMIZE FOR (@ProductName = ''Toy'', @ProductName = ''Book''));',
     N'@ProductName varchar(20), @id int OUTPUT', @ProductName = 'Gadget', @id = NULL;
EXEC sp_executesql N'SELECT @id = ItemID FROM dbo.Items WHERE ProductName = @ProductName
     OPTION (OPTIMIZE FOR (@Product = ''Toy''));',
     N'@ProductName varchar(20), @id int OUTPUT', @ProductName = 'Gadget', @id = NULL;

The first call fails with Msg 4131. The second fails with Msg 137. The messages read as follows.

Msg 4131, Level 16
A compile-time literal value is specified more than once for the variable "@ProductName" in one or more OPTIMIZE FOR clauses.
Msg 137, Level 15
Must declare the scalar variable "@Product".

The Price of a Hint

You could argue that OPTIMIZE FOR UNKNOWN on both parameters is the safer choice, since it asks for nothing. It also plans for nobody. Variant B expects 47.6 rows, which suits neither a Gadget nor a Toy. A value hint gives a plan that fits one case well.

A hint also freezes a choice. If the data changes and Gadget becomes the common product, the hint plans for the wrong case. Write down why you chose each value, and review the hint when the data changes shape. A comment above the hint that names the value and the reason helps the next person who reads the plan. It also helps you in a year. Test a different fix first when you can. One option is DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query.

What to Remember

Write multiple OPTIMIZE FOR hints as one list, with a value or UNKNOWN for each parameter. Read the compile values from the plan to confirm that the hint took effect. When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE OptimizeHintDemo;

A hint is not a fix, it is a decision you now have to keep making.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.