{"id":1193,"date":"2021-06-09T22:16:28","date_gmt":"2021-06-10T05:16:28","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/erin\/?p=1193"},"modified":"2021-06-09T22:28:06","modified_gmt":"2021-06-10T05:28:06","slug":"query-store-hints","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-hints\/","title":{"rendered":"Query Store Hints"},"content":{"rendered":"\n

Microsoft just announced that Query Store Hints<\/a> have made it into public preview for Azure SQL Database (thank you Joe and team).  For any of you with a single database, elastic pool, managed instance (!) or hyperscale, that means you have access to the feature today<\/strong>.<\/p>\n\n\n\n

What do you mean, Query Store Hints?<\/h4>\n\n\n\n

Joe Sack<\/a> and Pedro Lopes<\/a> first mentioned Query Store hints in their Azure SQL: Path to an Intelligent Database session at the 2020 PASS Summit.  (You can watch the video here<\/a>, thanks RedGate!)  As many of you know, the query optimizer typically generates a good plan for a query, but there are times where you might want (or need) to add a hint to the query to direct the optimizer in its decision.  Common query hints are adding OPTION (RECOMPILE) to force a recompile for every execution or OPTION (MAXDOP 1) to force a query to run single-threaded.  <\/p>\n\n\n\n

Query hints can be really helpful in some scenarios, but if you do not have direct access to the code (hello third party applications), then you cannot add a query hint to the statement.  Historically, you\u2019ve been able to use a plan guide to apply a hint, and that doesn\u2019t require direct code access, but plan guides are not always intuitive and I find that most people are not familiar with how to use them.<\/p>\n\n\n\n

Enter Query Store Hints.  With this feature, you can now add a hint to a specific statement, even if you don\u2019t have access to the code base.  You simply need to have Query Store enabled, and for anyone using Azure SQL Database, that\u2019s turned on by default. <\/p>\n\n\n\n

Let\u2019s start exploring.<\/h4>\n\n\n\n

I\u2019ve connected to my Azure SQL Database via Management Studio (I still prefer SSMS to Azure Data Studio because it has the Query Store reports available), and I also confirmed that Query Store is enabled.  I cleared out any old data I had in there (because this is a demo database<\/em>; do not clear out your QS data in production) and ran a query:<\/p>\n\n\n

\nSELECT o.CustomerID, ol.OrderID, ol.OrderLineID, o.OrderDate\nFROM Sales.Orders o\nJOIN Sales.OrderLines ol\n\tON o.OrderID = ol.OrderID\nWHERE o.CustomerID = 402\nORDER BY CustomerID, o.OrderID;\nGO\n<\/pre><\/div>\n\n\n
I opened up the Top Resource Consuming Queries report and found my query, and checked the plan:<\/p>\n\n\n\n
\"query_id<\/a>
query_id 1 and its plan, which uses a nested loop<\/figcaption><\/figure>\n\n\n\n
Ok, there\u2019s a nested loop and seeks against the two nonclustered indexes, and honestly, this is a fine plan.  But let\u2019s see what happens if I use Query Store to hint it to use a hash join instead.  I need the query_id for the query, which is 1 as noted in the screenshot above.  The T-SQL syntax I need to add the hint is:<\/p>\n\n\n
\nEXEC sp_query_store_set_hints @query_id = 1, @value = N'OPTION (HASH JOIN)';\nGO\n<\/pre><\/div>\n\n\n
After executing, I run the query again, then refresh the Top Resource Consuming Queries report:<\/p>\n\n\n\n
\"query_id<\/a>
query_id 1 and its new plan, after the HASH JOIN hint is applied<\/figcaption><\/figure>\n\n\n\n
Notice that I now have two plans for the query, plan_id 1 and plan_id 7, and the new plan uses a hash match instead of nested loop.<\/p>\n\n\n\n
It\u2019s pretty damn cool.  If you are not using Azure SQL Database do not despair, this feature will likely make it into vNext…so start thinking about your upgrade.  If you\u2019re wondering when you might want to use Query Store hints, here are a few additional examples based on customer scenarios I\u2019ve encountered in the last couple months:<\/p>\n\n\n\n