Joey on SQL Server

How To Use Query Store Hints in Azure SQL

Microsoft has just added support for query hints to Azure SQL Database, giving database administrators a powerful tool to manage the performance of ISV application code. Here's how it works.

• By Joey D'Antoni
• 06/15/2021

Database performance is a very popular topic for several reasons. The biggest is that the most frequent source of application bottlenecks is the database layer. Performance tuning also offers the near-immediate gratification of vastly improving the run time of a query by making a small code change or indexing an additional column. There is a whole third-party software industry built around SQL Server database performance.

In SQL Server 2016, Microsoft introduced the Query Store, a feature set designed to capture performance information around query execution. This allows the administrator to quickly identify a poorly performing query, or a query that has suddenly regressed in performance. However, the capabilities of the Query Store feature go well beyond just capturing data.

When the Query Store was introduced, it collected query execution plan and runtime information. The execution plan is the series of logical steps that the database engine creates and uses to return the results of a given query. Reading these plans allows an administrator to identity anomalous behavior that may be impacting performance. Microsoft has continually added additional functionality to the Query Store, making it a hub of database performance information.

In SQL Server 2017, automatic plan correction was added, which allows for the database engine to automatically use the "last known good plan" for a query that has suddenly regressed in performance. Beyond that, the Query Store started tracking additional performance data like wait statistics and use of TempDB by query.

The latest addition to the toolkit was introduced to Azure SQL Database last week: support for query hints.

Query hints are used to override the normal behavior of the SQL Server query optimizer to change query execution behavior. Microsoft recommends that they should be used carefully, as in general the query optimizer tries to select the best possible execution plan for a given query. However, in practice, especially on larger systems, you may run into edge cases where performance suddenly degrades for a given query. In these cases, when you control the source code for your application, you can add hints to the T-SQL to change optimizer behavior.

To share an example, at one client, we had a scenario where tables that had clustered columnstore indexes and additional non-clustered (b-tree) indexes were causing the optimizer to primarily use the non-clustered index. This led to dramatic performance degradation, to the point where queries were not completing at all. We were able to remedy this behavior by adding a hint to our procedures to force use of the columnstore index, which quickly solved our performance issues.

While using query hints in this fashion can be a straightforward way to solve a performance issue, many DBAs support applications where they do not have access to the source queries or cannot modify the code. Most independent software vendor (ISV) applications do not allow access to the source queries and they typically frown on changes to stored procedures, and in some cases they will frown on your adding indexes -- even if they are clearly needed. Due to these restrictions, managing the performance of these ISV applications can be a major headache for DBAs.

With the introduction of Query Store hints, DBAs can now change the execution plans for queries, without making any changes to the application itself. The way Microsoft has implemented the process is shown in the graphic below:

This process does require the query to be executed without the hint, but typically you would find poor performance by tracking the query in the Query Store. Let's walk through an example of where you might you use this. Consider the following stored procedure that runs against the AdventureWorks sample database:

CREATE PROCEDURE GetSalesQuantity (@ProductId INT)
AS
SELECT SalesOrderDetailID
	,OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;

This is a basic stored procedure, but the SalesOrderDetail table has a fairly significant amount of data skew, which means depending on the parameter used the first time the procedure is executed, the query can have a suboptimal execution plan (this is because of a behavior called parameter sniffing -- that is a net positive for overall server performance). While this table doesn't have enough data to cause major performance differences, if you run the query with a parameter value of 870, which has 4,688 rows, you will see a different execution plan than if you run that same query with a value of 897, which has two rows. You can identity this behavior in the execution plan by looking at the actual number of rows versus SQL Server's estimate.

Because a parameter value with smaller rows was initially run, and that execution plan was cached, for all subsequent executions SQL Server uses the estimate that the plan was created with -- which is a much smaller number of rows.

This specific subset of performance problem is called parameter-sensitive plans (PSPs), which simply means that for a given set of parameters with the same execution plan, you may experience dramatically different performance. There are several ways to solve for this problem, but one approach that can work if the query is not run very frequently is the RECOMPILE hint. This hint means that SQL Server will generate a new execution plan for each execution of a given query. Using the RECOMPILE hint is not for all queries, and you should be careful with it; it just provides an easy-to-use example of how Query Store hints can be implemented.

Since I have run this query a few times, I have captured the query and its plan information in the Query Store as shown below:

This screenshot shows the query and the fact that there are two execution plans for it. It also shows me the query ID, which in this case is 6. To add a RECOMPILE hint using the Query Store, I need to execute the following T-SQL:

EXEC sp_query_store_set_hints @query_id=6, @value = N'OPTION(RECOMPILE)';
GO

After executing that statement, every time the stored procedure is executed, the engine will generate a new execution plan. There is a new catalog view associated with this feature called sys.query_store_query_hints, where you can see any hints that have been added. Because of the persisted nature of the Query Store, these hints will exist after server restarts, failover and override hints that are coded into the existing queries.

You can learn more about how to implement this feature in Microsoft docs here. The Query Store hints feature is available in Azure SQL Database Managed Instance, and like most database engine features, I would expect it to make its way into SQL Server at some point in the future. While there are still some limitations around what hints you can implement, Query Store hints provides the DBA a powerful tool to manage performance of ISV application code.