Query Store and hints: More Powerful than ever

Dennes Torres explains how Query Store hints work in Azure SQL Database and Managed Instance.

I need to confess: I’m proud of the article Query Store and Parameterization Problems I wrote. Today it’s no news at all (although some people may not know the details about plan guides I included in the article) but I wrote it in 2017, based on SQL Server 2016, when the idea of identifying parameter sniffing with query store was very fresh.

Jump to today: Query Store got a new feature, Query Store Hints. This feature is already being the base for many performance improvements in SQL Server. The idea is the possibility to include query hints on the query store queries.

In my article about parameterization, I made use of plan guides exactly because it was the only way to include hints on the queries. Query Store Hints change that, and in my opinion, they make plan guides obsolete. What other reason would make you still use a plan guide today? I would love to hear about it on the comments.

Preparing the Environment

Let’s prepare an environment for some tests.

First Step: Provision a demonstration database

When you are provisioning an Azure SQL Database you can choose to provision a demo database, AdventureWorksLT. Let’s start with one.

Second Step: Creating a view to replate spt_values

We need to make the tables bigger for the example. Many years ago, Adam Machanic created a script called MakeBigAdventure.sql. The script create two tables inside the Adventureworks database, Bigproduct and BigTransactionHistory.

The script was made to work on AdventureWorks in a SQL Server on premises. It uses the table master..spt_values to randomize the records.

On Azure SQL, we don’t have access to the master..spt_values table, so we need to replace it with something else. We can create a view for this purpose. The code of the view will be the following:

CREATE VIEW dbo.spt_values
AS
  WITH int1(z)
       AS (SELECT 0
           UNION ALL
           SELECT 0),
       int2(z)
       AS (SELECT 0
           FROM   int1 a
                  CROSS JOIN int1 b),
       int4(z)
       AS (SELECT 0
           FROM   int2 a
                  CROSS JOIN int2 b),
       int8(z)
       AS (SELECT 0
           FROM   int4 a
                  CROSS JOIN int4 b),
       int16(z)
       AS (SELECT TOP 2048 0
           FROM   int8 a
                  CROSS JOIN int4 b)
  SELECT Cast (NULL AS NVARCHAR(35)) [name],
         Row_number()
           OVER (
             ORDER BY z)  1         AS [number],
         Cast (‘P’ AS NCHAR(3))      [type],
         Cast (NULL AS INT)          [low],
         Cast (NULL AS INT)          [high],
         0                           [status]
  FROM   int16 

Original link: https://devio.wordpress.com/2018/06/03/generating-a-range-of-numbers-and-dates-in-t-sql/

Third Step: Fix the script to work with AdventureWorksLT

AdventureWorks has the schema Product, while AdventureWorksLT has the shema SalesLT. You need to replace the Product schema by SalesLT. A simple find/replace solves the problem.

 

 

Fourth Step: Fix the name of spt_values in the script

A simple replace from master..spt_values to dbo.spt_values will do the work.

Fifth Step: Create an Index

Let’s create an index over a field that we know will have an uneven distribution and due to that will cause the parameter sniffing problem.

You can use the following statement:

CREATE NONCLUSTERED INDEX indprice
  ON [dbo].[BigProduct] ([listprice])

go 

Establishing the problem

This is the same problem describe on the article Query Store and Parameterization Problems, but we will use a different solution.

Let’s create a stored procedure which causes the parameterization problem. The procedure code is below:

CREATE PROCEDURE Filterprice @listprice NUMERIC(15, 2)
AS
    SELECT *
    FROM   dbo.bigproduct
    WHERE  listprice = @listprice 

If you check the estimated execution plan of the code below, you will notice each procedure execution has a different query plan. This happens because the distribution of values in the field is not even. Some values appear way more frequently than others. The use of With Recompile will ensure the plans of these executions will not be stored in the cache

 

EXEC Filterprice
  9.99 WITH recompile
 

EXEC Filterprice
  337.22 WITH recompile 
 

 

Causing the problem

 

The result of the execution of the queries again, without using the With Recompile is both queries will be using the same plan, which will be wrong for one of them.

 

EXEC Filterprice
  9.99

EXEC Filterprice
  337.22 

When one of these procedures is executed without the recompile option, the plan will be included in the query plan cache. The next execution will use the same plan again, causing a problem, because the plan may not be the best choice to the value.

On the original article, we solved the problem using a plan guide to force the recompile of the query. Let’s solve the problem this time using Query Store Hints.

Using Query Store Hints to solve the problem

The Query Store hints are applied over the queries. We need to discover the id of this query inside query store. The following statement can do the work:

SELECT query_sql_text,
       q.query_id
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 N’%bigproduct%’
go 

 

Even the query being inside the stored procedure, it will be individually recorded in query store. Once we know the id of the query, we can make the application of the query hint using a stored procedure:

EXEC Sp_query_store_set_hints
  @query_id=1,
  @value = N’OPTION(RECOMPILE)’;

go 

 
Execute the queries again and now each one will have the best plan according the parameter. We also have an additional benefit in comparison to other solutions, such as setting the procedure as WITH RECOMPILE : Only a single query is being recompiled, not the entire procedure.

Monitoring

Once you decide to apply query store hints, there are some monitoring tasks you need to execute:

  • You need to monitor the hints. In case of a failure in a hint, you need to react to that.
  • You need to monitor query regressions. If a hint is not needed anymore, you can suffer from a query regression and this will appear as part of the monitoring.

The following query can be used to monitor the hints:

SELECT query_hint_id,
       query_id,
       query_hint_text,
       last_query_hint_failure_reason,
       last_query_hint_failure_reason_desc,
       query_hint_failure_count,
       source,
       source_desc
FROM   sys.query_store_query_hints;

go 

The Future

SQL Server 2022 is bringing many new optimization features. They are becoming more intelligent and being built over previous existing features. Some of the new optimization features will be automatically creating query store hints.

Conclusion

The SQL Server optimization features are becoming more intelligent on each version. Each new optimization feature, however, brings new and more advanced needs for monitoring.