{"id":97600,"date":"2023-08-02T17:00:40","date_gmt":"2023-08-02T17:00:40","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97600"},"modified":"2023-08-09T15:27:57","modified_gmt":"2023-08-09T15:27:57","slug":"managing-query-store-hints","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/managing-query-store-hints\/","title":{"rendered":"Managing Query Store Hints"},"content":{"rendered":"

I wrote about query store hints<\/a> before when they were first released in Azure SQL databases.<\/p>\n

However, there are some tricks related to managing query store hints we will explore on this blog post.<\/p>\n

Applying Query Store Hint<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

The query to retrieve hints and plans is the following:<\/p>\n

SELECT<\/span>\u00a0query_hint_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsq<\/span>.<\/span>query_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_hint_text<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsq<\/span>.<\/span>query_text_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_sql_text<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsp<\/span>.<\/span>plan_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_plan<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>query_store_query_hints<\/span>\u00a0qsqh<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query<\/span>\u00a0qsq<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsqh<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query_text<\/span>\u00a0qsqt<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_text_id<\/span>\u00a0=<\/span>\u00a0qsqt<\/span>.<\/span>query_text_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_plan<\/span>\u00a0qsp<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsp<\/span>.<\/span>query_id<\/span>\u00a0 <\/span><\/div>\n

<\/p>\n

Changing the applied hint<\/h2>\n

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.<\/p>\n

As a result, if you immediately use the query to show which plan is using which hint, you will get a completely wrong information.<\/p>\n

Retrieving the correct hint applied to each plan<\/h2>\n

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.<\/p>\n

SELECT<\/span>\u00a0query_hint_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsq<\/span>.<\/span>query_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_hint_text<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsq<\/span>.<\/span>query_text_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_sql_text<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qsp<\/span>.<\/span>plan_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CONVERT<\/span>(<\/span>XML<\/span>,<\/span>\u00a0query_plan<\/span>)<\/span>\u00a0query_plan<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>query_store_query_hints<\/span>\u00a0qsqh<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query<\/span>\u00a0qsq<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsqh<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query_text<\/span>\u00a0qsqt<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_text_id<\/span>\u00a0=<\/span>\u00a0qsqt<\/span>.<\/span>query_text_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_plan<\/span>\u00a0qsp<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsp<\/span>.<\/span>query_id<\/span>\u00a0 <\/span><\/div>\n

<\/p>\n

\"Texto\n\nDescri\u00e7\u00e3o<\/p>\n

\"Uma<\/p>\n

Using the XML Queries to find the correct Query Hint<\/h2>\n

Using XML queries over the stored query plans we can extract the actual query hint used during the query compilation.<\/p>\n

To make this example better, let\u2019s replace the previous query hint with a new one, using the following query:<\/p>\n

EXEC<\/span>\u00a0Sp_query_store_set_hints<\/span>
\n\u00a0\u00a0@query_id<\/span>=<\/span>1<\/span>,<\/span>
\n\u00a0\u00a0@value\u00a0<\/span>=<\/span>\u00a0N’\u00a0OPTION\u00a0(OPTIMIZE\u00a0FOR\u00a0unknown)’<\/span> <\/p>\n

go<\/span>\u00a0 <\/span><\/div>\n

The query, using XML to retrieve the actual query hint in the plan is the following:<\/p>\n

WITH<\/span>\u00a0xmlnamespaces<\/span>\u00a0(<\/span>\u00a0‘http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan’<\/span>
\nAS<\/span>\u00a0ns1<\/span>\u00a0)<\/span> <\/p>\n

SELECT<\/span>
\nquery_hint_id<\/span>,<\/span>
\nqsq<\/span>.<\/span>query_id<\/span>,<\/span>
\nquery_hint_text<\/span>,<\/span>
\nqsq<\/span>.<\/span>query_text_id<\/span>,<\/span>
\nquery_sql_text<\/span>,<\/span>
\nqsp<\/span>.<\/span>plan_id<\/span>,<\/span>
\nCONVERT<\/span>(<\/span>XML<\/span>,<\/span>\u00a0query_plan<\/span>)<\/span>
\nquery_plan<\/span>,<\/span>
\nCONVERT<\/span>(<\/span>XML<\/span>,<\/span>\u00a0query_plan<\/span>)<\/span>.<\/span>value<\/span>(<\/span>‘(\/ns1:ShowPlanXML\/ns1:BatchSequence\/ns1:Batch\/ns1:Statements\/ns1:StmtSimple\/@QueryStoreStatementHintText)[1]’<\/span>,<\/span>\u00a0‘varchar(100)’<\/span>)<\/span>\u00a0actualQueryHint<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>query_store_query_hints<\/span>\u00a0qsqh<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query<\/span>\u00a0qsq<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsqh<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query_text<\/span>\u00a0qsqt<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_text_id<\/span>\u00a0=<\/span>\u00a0qsqt<\/span>.<\/span>query_text_id<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_plan<\/span>\u00a0qsp<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qsq<\/span>.<\/span>query_id<\/span>\u00a0=<\/span>\u00a0qsp<\/span>.<\/span>query_id<\/span>\u00a0 <\/span><\/div>\n

<\/p>\n

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.<\/p>\n

One plan has no hint<\/h2>\n

The query will very frequently return one plan with no hint. This happens because a combination of reasons:<\/p>\n