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:
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.
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:
@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:
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:
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
Load comments