SQL Server 2022 Query Store Hints: Optimize Execution Plans for Enhanced Database Performance

Unlock Fine-Grained Control and Streamline Performance Optimization with Query Store Hints in SQL Server 2022

Microsoft has consistently improved the capabilities of its SQL Server platform, incorporating new features to improve performance and simplify database administration.

Query Store Hints, which expands on the Query Store functionality, is one such feature introduced in SQL Server 2022.

In this story, we will look at what Query Store Hints are, how they work, and what advantages they provide to database administrators and developers.

Query Store — Top Resource Consuming

First, what is a Hint?

A query hint is a directive sent to the SQL Server query optimizer that influences the execution plan chosen for a specific query.

By overriding the optimizer’s default behavior, query hints give database administrators (DBAs) and developers more control over the execution plan generation process.

They are typically used to address performance issues or fine-tune query execution when the optimizer does not produce the most efficient plan.

Examples:

The MAXDOP (maximum degree of parallelism) hint can be used to limit the number of processor cores used to execute a query.

SELECT *
FROM Sales.Orders
WHERE OrderDate >= '2021-01-01' AND OrderDate <= '2021-12-31'
OPTION (MAXDOP 2);

You can enable specific trace flags for the scope of the query by using the QUERYTRACEON hint. You can, for example, use the trace flag 9481 to specify a query timeout.

SELECT *
FROM Products p
INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
OPTION (QUERYTRACEON 9481);

Query Store Hints

Query Store Hints is an extension of the Query Store functionality that enables database administrators (DBAs) and developers to control and optimize query execution plans.

You can use Query Store Hints to add query-level hints to the execution plan generated by the SQL Server query optimizer without changing the original query text.

How does it work?

The Query Store interface is used to create and manage Query Store Hints. When a hint is applied to a specific query, the hint information is saved in the Query Store repository alongside the query. This ensures that the hint is always associated with the query, regardless of whether the query text changes or the application is redeployed.

When generating an execution plan for the query, the SQL Server query optimizer considers the hint, influencing the plan selection process. When the query optimizer’s default plan is suboptimal or causes performance issues, the Query Store Hint can point the optimizer in the right direction.

Let´s take a look at a few examples:

MAXDOP

In this scenario, we have the following query that generates an Execution Plan:

SELECT * FROM Sales.Orders 
ORDER BY DeliveryInstructions

Execution plan from the query above

As you can see, the execution plan uses Parallelism to execute this query. That means we are using more than one CPU to run it.

If want to avoid using more than one CPU we could set a Hint for MAXDOP 1

SELECT * FROM Sales.Orders
ORDER BY DeliveryInstructions
OPTION (MAXDOP 1)

Execution plans from the queries above

The Execution Plan changed and is not using parallelism now.

In this case, we changed the query code but with Query Store Hint we can apply the Hint without doing any modification to the code.

First, we run the following query to check our query_id:

SELECT TOP 100 
  query_sql_text, 
  q.query_id, 
  q.last_execution_time, 
  q.count_compiles
FROM sys.query_store_query_text qt
 INNER JOIN sys.query_store_query q ON
 qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE 'SELECT *%'
ORDER BY last_execution_time DESC

Result from query above

In this scenario, we want to set the hint to the Query with query_id = 2.

To set the hint we need to run the Stored Procedure sp_query_store_set_hints passing the query_id and the value of the hint:

EXEC sp_query_store_set_hints @QUERY_ID=2, @VALUE = N'OPTION (MAXDOP 1)'

We can check our active Query Store Hints:

SELECT * 
FROM sys.query_store_query_hints

Result of the query above

If we run the query again, we will have a different plan:

SELECT * FROM Sales.Orders 
ORDER BY DeliveryInstructions

Execution Plan after setting MAXDOP 1 Hint

Note: In this scenario, the goal of the hint is not to make the query perform better but just an example on how you could set a Query Store Hint.

You can also clear your Query Store Hints:

EXEC sp_query_store_clear_hints @QUERY_ID = 2

Avoid Parameter Sniffing

I will run the following query filtered by ProductID, each by filteres by a different Id:

SELECT SalesOrderDetailID, OrderQty, UnitPrice 
FROM Sales.SalesOrderDetail
WHERE ProductID=897;

SELECT SalesOrderDetailID, OrderQty, UnitPrice 
FROM Sales.SalesOrderDetail
WHERE ProductID=870;

Execution plans from the queries above

As you can see, depending on the ProductId I filter, the engine will build a different execution plan. A better one is ProductID=897 since it can do an Index Seek and the worst one is when ProductID=870 because it´s doing an Index Scan.

If we run our original query on a stored procedure and pass as a parameter the ProductId, in a situation where the engine selects the 2nd Execution Plan, it will store that plan in the cache for future executions.

CREATE OR ALTER PROCEDURE Get_SalesOrderDetail
@PRODUCTID INT 
AS 
SELECT SalesOrderDetailID, OrderQty, UnitPrice 
FROM Sales.SalesOrderDetail
WHERE ProductID=@PRODUCTID;
GO

Run SP when ProductId = 870:

EXEC Get_SalesOrderDetail @PRODUCTID = 870;

The engine built the worst execution plan. If now the same SP is executed for ProductId = 897:

EXEC Get_SalesOrderDetail @PRODUCTID = 897;

Now it uses the worst plan.

What we can do is run SP_RECOMPILE to recompile the Stored Procedure, we will set this as a Query Store Hint, so every time the Get_SalesOrderDetail is executed it recompiles so the next time the engine builds the optimal execution plan.

Again, I have to know the query_id:

Then we set the Hint:

EXEC sp_query_store_set_hints @QUERY_ID=5, @VALUE = N'OPTION (RECOMPILE)';

Now I will execute Get_SalesOrderDetail SP:

Now it uses the optimal execution plan because I´m recompiling the stored procedure after each execution.

Benefits

• Fine-grained control over query execution plans: Query Store Hints give DBAs and developers granular control over query execution plans. This allows for better query optimization and overall performance improvement.
• No need to change query text: Using hints from the Query Store eliminates the need to change the original query text. This ensures that the application code remains unchanged while benefiting from performance enhancements.
• Query Store Hints are managed centrally via the Query Store interface, which provides a centralized location for managing hints and analyzing their impact on query performance. This simplifies hint management and allows for more effective optimization efforts.
• Hint persistence: Query Store Hints are stored in the Query Store repository alongside their associated queries, ensuring that hints remain even if the query text changes or the application is redeployed. This eliminates the need to reapply hints following application updates, allowing for consistent performance optimization.
• Query Store Hints help database professionals identify and address performance issues more effectively by allowing fine-grained control over query execution plans. As a result, database environments become more stable and efficient.

Important Notes

• Query Store Hints are persisted even after a restart.
• Query Store Hints override any hard-coded statement-level hints and plan guide hints.
• If a Hint is set but it contradicts what is possible, then the engine will execute the query ignoring the Hint.

Use Cases

• Recompile a query on each execution.
• Memory grant size limit.
• Maximum degree of parallelism.
• Use a lower Compatibility Level on a specific query.
• And more…

Query Store Hints, give database administrators and developers a powerful tool for controlling and optimizing query execution plans.

Query Store Hints streamline the performance optimization process and improve the overall efficiency of database environments by providing fine-grained control over execution plans without modifying the original query text.

As organizations increasingly rely on data-driven decision-making, having tools that enable efficient and stable database management is critical.

The addition of Query Store Hints to the SQL Server feature set is very welcome, allowing database developers to maintain optimal performance in their environments.

If you enjoyed this story, don´t forget to follow and subscribe to receive weekly stories by email.

Also, consider becoming a Medium Member for just 5$ a month and you will get unlimited access to every story on Medium. If you do that through my page or one of my starred stories I receive compensation for it that could really help me on my work here 😊

#SQLServerOptimization

#QueryHintsTips

#DatabasePerformance

#ExecutionPlanTuning

#SQLServer2022

Go from SELECT * to interview-worthy project. Get our free 5-page guide.