Query Hint and Plan Guide

Query execution plan generated for a SQL query significantly affect the query performance. Parameter sniffing, indexes, statistics and other factors are taken into consideration for SQL Server query optimizer to produce an optimal query plan for the execution within a timely manner. Sometimes, due to certain scenarios and limitation, the query plan generated may be suboptimal. There are multiple ways to address this problem, like modifying the way the query is written, or using query hint or even plan guides under certain circumstances. This blog post discuss an example of using a query hint as well as plan guide.

First, this is a contrived stored procedure to retrieve some sales and product information based on the user input of the product number. It has a LIKE operator in the where condition to allows a specific product number or wildcard value.

CREATE PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;
END
GO

Turn on IO and Time statistics

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

The performance comparison below is tested against warm cache where the data already loaded in memory. Execute first query to return result from the first 1000 records of all the frame (FR% as ProductNumber). Before the execution, we clear the query plan cache (not the data in memory) for better comparison later on.

DBCC FREEPROCCACHE; --Remove the query plan cache (not the data)
GO

EXEC sp_ProductOrderDetail @ProductNumber = 'FR%', @RecodOffset = 0, @RecordNumber = 1000;
GO

The query executed less than 1 second. The query is written to also allow specific product number (SO-B909-L). Let's remove the previous query plan cache (not the data in memory).

DBCC FREEPROCCACHE; --Remove the query plan cache (not the data)
GO

EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

Notice that the execution plan is different from the previous query plan? SQL Server optimizer comes up with this query plan with the parameter value sniffed (SO-B909-L). Instead of scanning the respective sales header and sales details records and join with respective product, it identifies the product record first and join it with respective sales details and sales header. This may due to the records in scope is much smaller since the exact product number is provided.

As SQL Server reuse previously cached query plan, executing the first parameter with FR% yield this performance,

EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

The performance of the query went from less than 1 second to about 25 seconds! First, there is significant IO involved with this query plan for this parameter value, and also multiple of operation spilled into tempdb which makes the query run much slower.

The query was designed to allow parameter value that have very different scope. One for specific value that usually yield small result set, but another for pattern with a wildcard that usually return much larger result set.

There could be multiple options to address this. Often, the stored procedure may be rewritten in a way to allow different query plan to be used according to parameter value. Since this post is about query hint and plan guide, let's explore those method. One of the query hint is OPTIMIZE FOR UNKNOWN. OPTIMIZE FOR UNKNOWN hint asks SQL Server optimizer to use statistical data instead of the initial parameter values (sniffed value) when the query is compiled and optimized. It produces a consistent query plan regardless of what the initial value was used (sniffed).

Since the parameter value provided yield a different query plan as noticed previously, could the OPTIMIZE FOR UNKNOWN query hint help here?

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY
OPTION (OPTIMIZE FOR UNKNOWN);
END

Now, execute the stored procedure with either @ProductNumber = 'SO-B909-L' or @ProductNumber = 'FR%' produces the same query plan.

DBCC FREEPROCCACHE; 
GO
EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

DBCC FREEPROCCACHE; 
GO
EXEC sp_ProductOrderDetail @ProductNumber = 'FR%', @RecodOffset = 0, @RecordNumber = 1000;
GO

It looks good on @ProductNumer = 'FR%' performance, but the performance on @ProductNumber = 'SO-B909-L' suffers with this plan. It went from about 1 second to around 20 seconds! One scenario becomes better in the expense of the other one. Not good.

As we have noticed previously that these two types of parameter value passed yield very different query plans to obtain the most optimal performance, it seems like it is best not to use the query plan cached from the initial parameter value, but rather utilize an optimal plan depending on the provided parameter values.

OPTION (RECOMPILE) is a query hint to discard query plan previously generated and force the query optimizer to recompile a new query plan the next time the same query is executed. Let's see how it works here.

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY
OPTION (RECOMPILE);
END

Now SQL Server optimizer generates an optimal query plan for parameter value ProductNumer = 'FR%' or @ProductNumber = 'SO-B909-L' respectively regardless the initial value used. Both different parameter value provided perform as expected with the optimal duration seen previously. 

One important note. As OPTION (RECOMPILE) recompile and regenerate new query plan on each execution, there is additional CPU overhead involved. Depending on how expensive is the query plan and the frequency the stored procedure is executed, RECOMPILE may or may not be a suitable solution Also, since the cache plan is discarded after execution, it may not show up on SQL Server DMV for monitoring or troubleshooting purpose.

Now we learn that OPTION (RECOMPILE) is useful for the query to perform optimally for different parameter value, what happen if the query could not be modified due to multiple reasons, like the stored procedure is provided by vendor or ORM generated query?

SQL Server plan guides could be useful in these type of scenario when we have our hand tied trying to address the query performance. Let's see how it works.

First remove the query hint on the stored procedure.

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;
END

Using UI to create a plan guide for this query in sp_ProductOrderDetail stored procedure.

Or use SQL Statement to create the plan guide.

USE [AdventureWorks2014]
GO

EXEC sp_create_plan_guide @name = N'[PlanGuide_sp_ProductOrderDetailPlanGuide]', 
@stmt = N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
@type = N'OBJECT', 
@module_or_batch = N'[dbo].[sp_ProductOrderDetail]', 
@hints = N'OPTION (RECOMPILE)'
GO

Execute the stored procedure with different parameter and examine the query plan. One way to verify if the plan guide is being used on the execution by right clicking on the SELECT node, click properties and examine the plan guide name value.

What about if the SQL query is not a stored procedure but rather a query from ORM application. Something like this,

sp_executesql 
N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
@ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;

OR with sql_prepare

DECLARE @p1 int
EXEC sp_prepare @p1 output, N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;';

EXEC sp_execute @p1, 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
--EXEC sp_unbindefault @p1;

From the verification step earlier, the query plan generated is not using the query hint specified in the plan guide. Plan guide only applied when very specific conditions are met like in this case both the scope as well as the SQL statement. The plan guide created earlier is only scoped for the sp_ProductOrderDetail stored procedure. A proper plan guide needs to be created for this SQL query.

USE [AdventureWorks2014]
GO

EXEC sp_create_plan_guide @name = N'[PlanGuide_Query1]', 
@stmt = N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
@type = N'SQL', 
@params = N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
@hints = N'OPTION (RECOMPILE)'
GO

Now, the SQL query execute with the OPTION (RECOMPILE) query as specified in the plan guide. The setting also shows that plan guide is being used.

To view the existing plan guide from UI or using sys.plan_guides dynamic management view

Plan guide is only available on enterprise edition. Plan guide should be always used sparingly and only under very special circumstances.

Hopefully you find this post on query hint and the plan guide helpful.