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:
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::
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
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 !
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.
If you need to optimize or improve the performance of your SQL Server database, contact us.
Azure Data Engineer, apasionado del mundo de los datos y con más de 6 años de experiencia.