Discover SQL Server 2022 Query Store Hints

Hello! Welcome to the new post on the best SQL Server blog. In today’s post we will talk about some of the most outstanding features of SQL Server 2022.

As we already know, SQL Server 2022 is currently a reality. We have already been able to verify some of its novelties and today we are going to dedicate ourselves to studying some of them in more depth.

Case Study Query Store Hints

This is a public database containing the names of boys and girls born between 1910 and the present day in every US State.

To carry out the test, we are going to use a public database such as “BabbyNames” that we obtain from the following link:

https://github.com/MaMotos/SQLData

To begin with the example, we are going to do a query with which we will obtain the most reapeated name each year in each US State, thus obtaining 2 rows per year, one for the most frequent male name and another for the name more repeated feminine.

To do this, we use this query:

WITH NameRank AS (
    SELECT
        RANK () OVER (PARTITION BY ReportYear, StateCode, Gender ORDER BY NameCount DESC) as RankByGenderAndRow,
        Id
    FROM agg.FirstNameByYearStateWide AS fnby
)
SELECT
    fnby.ReportYear, fnby.StateCode, fnby.Gender, fn.FirstName,  fnby.NameCount,
    fnby.ReportColumn1, fnby.ReportColumn2, fnby.ReportColumn3, fnby.ReportColumn4, fnby.ReportColumn5, fnby.ReportColumn6,
    fnby.ReportColumn7, fnby.ReportColumn8, fnby.ReportColumn9, fnby.ReportColumn10, fnby.ReportColumn11, fnby.ReportColumn12,
    fnby.ReportColumn13, fnby.ReportColumn14, fnby.ReportColumn15, fnby.ReportColumn16, fnby.ReportColumn17, fnby.ReportColumn18,
    fnby.ReportColumn19, fnby.ReportColumn20
FROM NameRank
JOIN agg.FirstNameByYearStateWide AS fnby ON
    fnby.Id = NameRank.Id
JOIN ref.FirstName AS fn ON fnby.FirstNameId=fn.FirstNameId
WHERE RankByGenderAndRow = 1

When executing the Query, we will obtain the following result:

As we can see, the execution has taken 44 seconds. If we access the execution plan, we can see that we have a WARNING! in the SELECT::

SELECT WARNING!

If we click on the “SELECT” box and press F4, we can see the properties box to access the WARNING! message.

WARNING! Detail on SELECT statement.

The executed Query is a CTE (Common Table Expression), which doesn’t work well with the new cardinality estimator (140). It is understanding that many more rows are going to arrive than it really does. This causes much more memory to be allocated to the execution than it actually needs.

This is a problem for this query and for the environment in general, since we are wasting resources.

Using Query Store Hints

For this use case, we have the Query Store configured as follows:

Query Store Configuration

With the following script we can see what query_id is assigned to the execution plan:

-- Identify the Query Store Query_id of the query statement you wish to modify
SELECT qt.query_sql_text, q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE N'%NameRank%'
AND query_sql_text NOT LIKE N'%query_store%';
GO

Identifying query_id

As we can see, it is query_id 1. With this information, we can check if said execution plan has any Query Store Hint active with the following script:

-- Get Query Store Hints active on the query_id plan
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 
WHERE query_id = 1;

As we can check, there is no active Query Store Hint for the execution plan with query_id = 1.

The next step will be to activate the Query Store Hint necessary for a correct execution of the query. To do this, we are going to force it to use the legacy cardinal estimator (70) which is the one for SQL Server versions up to and including 2012.

To do this, we are going to use traceflag 9481. We use the following script to add the trace to the execution plan:

-- Add Query Store Hint to plan_id
EXEC sp_query_store_set_hints 1, N'OPTION(QUERYTRACEON 9481)'

Perfect! We already have the trace added to the execution plan. Let’s check it:

Query Store Hint in query_id = 1.

Next, we are going to execute the query to check if there are any improvements:

Execution after the Query Store Hint is applied.

As we can see, the execution has gone from 44 seconds to 5 seconds. Therefore, we consider it a nice improve and a success thanks to Query Store Hint !

Conclusion

The Query Store Hint is a good tool when it comes to applying hints directly to the execution plans saved in the Query Store.

You can check the different Query Store Hints that can be applied with the following script:

SELECT * FROM sys.dm_exec_valid_use_hints

However, we recommend running an indexing study and keeping the statistics constantly updated before applying any Query Store Hints.

And here we finish this pos. If you are interested in technology and you are specializing in SQL Server database systems, we recommend our latest posts:

If you need to optimize and improve the performance of your SQL Server, at Aleson ITC we can help you.

Know the Waiting Stadistics or Waits for SQL Server

Avoid problems in your SQL Server Migration with Data Migration Assistant (DMA)

Change Data Capture in SQL Server

If you need to optimize or improve the performance of your SQL Server database, contact us.

Miguel Ángel Motos

Azure Data Engineer con más de 7 años de experiencia. Conocimiento de múltiples herramientas y enfocado en el mundo del dato. Experto en tuning de queries y mejora de rendimiento de Base de Datos. Profesional apasionado por la tecnología y los deportes al aire libre. En mi tiempo libre, me encontrarás jugando vóley playa o disfrutando con nuevos videojuegos.