Managing Query Store Hints

I wrote about query store hints before when they were first released in Azure SQL databases.

However, there are some tricks related to managing query store hints we will explore on this blog post.

Applying Query Store Hint

When we apply a query store hint, the query already exists in query store, and it has at least one plan registered. It’s even possible to have more.

The query hint will only be applied to further executions of the query, so the existing plans in query store don’t have the query hint applied.

The query store DMVs have relationships to each other, so we can relate the query_store_query_hints DMV with the query plans. However, the result will not identify if the plan was already there before the query hint was created, resulting in a false information about which plan was using which query hint.

The query to retrieve hints and plans is the following:

SELECT query_hint_id,
       qsq.query_id,
       query_hint_text,
       qsq.query_text_id,
       query_sql_text,
       qsp.plan_id,
       query_plan
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

Changing the applied hint

When we change the applied hint to a query, the new hint overwrites the previous one. Once again, the new hint will only be applied when the query is compiled again.

As a result, if you immediately use the query to show which plan is using which hint, you will get a completely wrong information.

Retrieving the correct hint applied to each plan

Each plan contains inside its definition, the hint which was used during the plan compilation. If you use the query below, the plan definitions appear in XML format. You can click the definition to open the plan. The Query Hint used with the plan will be in the properties window.

SELECT query_hint_id,
       qsq.query_id,
       query_hint_text,
       qsq.query_text_id,
       query_sql_text,
       qsp.plan_id,
       CONVERT(XML, query_plan) query_plan
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

Using the XML Queries to find the correct Query Hint

Using XML queries over the stored query plans we can extract the actual query hint used during the query compilation.

To make this example better, let’s replace the previous query hint with a new one, using the following query:

EXEC Sp_query_store_set_hints
  @query_id=1,
  @value = N’ OPTION (OPTIMIZE FOR unknown)’

go 

The query, using XML to retrieve the actual query hint in the plan is the following:

WITH xmlnamespaces ( ‘https://ift.tt/vWdzG3s;
AS ns1 )

SELECT
query_hint_id,
qsq.query_id,
query_hint_text,
qsq.query_text_id,
query_sql_text,
qsp.plan_id,
CONVERT(XML, query_plan)
query_plan,
CONVERT(XML, query_plan).value(‘(/ns1:ShowPlanXML/ns1:BatchSequence/ns1:Batch/ns1:Statements/ns1:StmtSimple/@QueryStoreStatementHintText)[1]’, ‘varchar(100)’) actualQueryHint
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

This result compares the hint applied in query store, which will be enforced on the next compilation of the plan, with the hint on the current stored plan.

One plan has no hint

The query will very frequently return one plan with no hint. This happens because a combination of reasons:

• The plan was created before the hint was applied.
• One of the executions with the hint results in a plan exactly as one already stored. In this case, the stored one is not replaced, staying without the hint application.

This makes especially trick to identify which hint was applied to this plan creation. The only additional option we have is to check the last execution of this plan.

If the plan last execution was before the query hint creation, so the current query hint stored was never applied to the plan. If the plan last execution was after the hint creation, the hint was applied.

To makes things work, query store doesn’t hold date and time of the query hint creation. So, we can check the last time of the plan execution, but we can only guess if it was before or after the query hint application.

The query, with addition of the date:

WITH xmlnamespaces ( ‘https://ift.tt/vWdzG3s; AS ns1 )
SELECT     query_hint_id,
           qsq.query_id,
           query_hint_text,
           qsq.query_text_id,
           query_sql_text,
           qsp.plan_id,
           CONVERT(xml,query_plan)                                                                                                                                      query_plan,
           CONVERT(xml,query_plan).value(‘(/ns1:ShowPlanXML/ns1:BatchSequence/ns1:Batch/ns1:Statements/ns1:StmtSimple/@QueryStoreStatementHintText)[1]’,‘varchar(100)’) actualqueryhint,
           (
                  SELECT max(last_execution_time) last_plan_execution
                  FROM   sys.query_store_runtime_stats
                  WHERE  plan_id=qsp.plan_id) AS last_plan_execution
FROM       sys.query_store_query_hints qsqh
INNER JOIN sys.query_store_query qsq
ON         qsqh.query_id =qsq.query_id
INNER JOIN sys.query_store_query_text qsqt
ON         qsq.query_text_id=qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp
ON         qsq.query_id=qsp.query_id

Summary

Managing query store hints can prove to be trickier than initially imagined. A considerable care is needed when analysing the query store hints applied to each plan.

This blog was inspired by a session delivered by Erin Stellato during the Malta SQL Server 2022 Query Optimization conference

 

The post Managing Query Store Hints appeared first on Simple Talk.

from Simple Talk https://ift.tt/TDEZtfH
via