{"id":92867,"date":"2021-11-29T17:00:45","date_gmt":"2021-11-29T17:00:45","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92867"},"modified":"2022-05-06T00:13:33","modified_gmt":"2022-05-06T00:13:33","slug":"query-store-hints-powerful","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/query-store-hints-powerful\/","title":{"rendered":"Query Store and hints: More Powerful than ever"},"content":{"rendered":"

I need to confess: I’m proud of the article Query Store and Parameterization Problems<\/a>\u00a0I wrote. Today it’s no news at all (although some people may not know the details about plan guides I included in the article) but I wrote it in 2017, based on SQL Server 2016<\/strong>, when the idea of identifying parameter sniffing with query store was very fresh.<\/p>\n

Jump to today: Query Store<\/strong> got a new feature, Query Store Hints<\/strong>. This feature is already being the base for many performance improvements in SQL Server<\/strong>. The idea is the possibility to include query hints on the query store queries.<\/p>\n

In my article about parameterization, I made use of plan guides exactly because it was the only way to include hints on the queries. Query Store Hints<\/strong> change that, and in my opinion, they make plan guides obsolete. What other reason would make you still use a plan guide today? I would love to hear about it on the comments.<\/p>\n

\nPreparing the Environment<\/h2>\n

Let’s prepare an environment for some tests.<\/p>\n

First Step: Provision a demonstration database<\/h3>\n

When you are provisioning an Azure SQL Database<\/strong> you can choose to provision a demo database, AdventureWorksLT<\/em>. Let’s start with one.<\/p>\n

\"\"<\/p>\n

\nSecond Step: Creating a view to replate spt_values<\/h3>\n

We need to make the tables bigger for the example. Many years ago, Adam Machanic created a script called MakeBigAdventure.sql<\/a>. The script create two tables inside the Adventureworks<\/em> database, Bigproduct<\/em> and BigTransactionHistory<\/em>.<\/p>\n

The script was made to work on AdventureWorks<\/em> in a SQL Server<\/strong> on premises. It uses the table master..spt_values<\/em> to randomize the records.<\/p>\n

On Azure SQL<\/strong>, we don’t have access to the master..spt_values<\/em> table, so we need to replace it with something else. We can create a view for this purpose. The code of the view will be the following:<\/p>\n

CREATE<\/span>\u00a0VIEW<\/span>\u00a0dbo<\/span>.<\/span>spt_values<\/span>
\nAS<\/span>
\n\u00a0\u00a0WITH<\/span>\u00a0int1<\/span>(<\/span>z<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a00<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION<\/span>\u00a0ALL<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT<\/span>\u00a00<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int2<\/span>(<\/span>z<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a00<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0int1<\/span>\u00a0a<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0JOIN<\/span>\u00a0int1<\/span>\u00a0b<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int4<\/span>(<\/span>z<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a00<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0int2<\/span>\u00a0a<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0JOIN<\/span>\u00a0int2<\/span>\u00a0b<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int8<\/span>(<\/span>z<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a00<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0int4<\/span>\u00a0a<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0JOIN<\/span>\u00a0int4<\/span>\u00a0b<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int16<\/span>(<\/span>z<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0TOP<\/span>\u00a02048<\/span>\u00a00<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0int8<\/span>\u00a0a<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0JOIN<\/span>\u00a0int4<\/span>\u00a0b<\/span>)<\/span>
\n\u00a0\u00a0SELECT<\/span>\u00a0Cast<\/span>\u00a0(<\/span>NULL<\/span>\u00a0AS<\/span>\u00a0NVARCHAR<\/span>(<\/span>35<\/span>)<\/span>)<\/span>\u00a0[name]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Row_number<\/span>(<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0z<\/span>)<\/span>\u00a0–<\/span>\u00a01<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0[number]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast<\/span>\u00a0(<\/span>‘P’<\/span>\u00a0AS<\/span>\u00a0NCHAR<\/span>(<\/span>3<\/span>)<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[type]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast<\/span>\u00a0(<\/span>NULL<\/span>\u00a0AS<\/span>\u00a0INT<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[low]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast<\/span>\u00a0(<\/span>NULL<\/span>\u00a0AS<\/span>\u00a0INT<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[high]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0[status]<\/span>
\n\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0int16<\/span>\u00a0 <\/span><\/div>\n

Third Step: Fix the script to work with AdventureWorksLT<\/h3>\n

AdventureWorks<\/em> has the schema Product<\/em>, while AdventureWorksLT<\/em> has the shema SalesLT<\/em>. You need to replace the Product<\/em> schema by SalesLT<\/em>. A simple find\/replace solves the problem.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

Fourth Step: Fix the name of spt_values in the script<\/h3>\n

A simple replace from master..spt_values<\/em> to dbo.spt_values<\/em> will do the work.<\/p>\n

\"\"<\/p>\n

Fifth Step: Create an Index<\/h3>\n

Let’s create an index over a field that we know will have an uneven distribution and due to that will cause the parameter sniffing problem.<\/p>\n

You can use the following statement:<\/p>\n

CREATE<\/span>\u00a0NONCLUSTERED<\/span>\u00a0INDEX<\/span>\u00a0indprice<\/span>
\n\u00a0\u00a0ON<\/span>\u00a0[dbo]<\/span>.<\/span>[BigProduct]<\/span>\u00a0(<\/span>[listprice]<\/span>)<\/span> <\/p>\n

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

\nEstablishing the problem<\/h2>\n

This is the same problem describe on the article Query Store and Parameterization Problems<\/a>, but we will use a different solution.<\/p>\n

Let’s create a stored procedure which causes the parameterization problem. The procedure code is below:<\/p>\n

CREATE<\/span>\u00a0PROCEDURE<\/span>\u00a0Filterprice<\/span>\u00a0@listprice<\/span>\u00a0NUMERIC<\/span>(<\/span>15<\/span>,<\/span>\u00a02<\/span>)<\/span>
\nAS<\/span>
\n\u00a0\u00a0\u00a0\u00a0SELECT<\/span>\u00a0*<\/span>
\n\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0dbo<\/span>.<\/span>bigproduct<\/span>
\n\u00a0\u00a0\u00a0\u00a0WHERE<\/span>\u00a0\u00a0listprice<\/span>\u00a0=<\/span>\u00a0@listprice<\/span>\u00a0 <\/span><\/div>\n

If you check the estimated execution plan of the code below, you will notice each procedure execution has a different query plan. This happens because the distribution of values in the field is not even. Some values appear way more frequently than others. The use of With Recompile<\/strong>\u00a0will ensure the plans of these executions will not be stored in the cache<\/p>\n

 <\/p>\n

EXEC<\/span>\u00a0Filterprice<\/span>
\n\u00a0\u00a09.99<\/span>\u00a0WITH<\/span>\u00a0recompile<\/span>
\n<\/span><\/div>\n
\u00a0<\/div>\n
\"\"<\/div>\n

\nEXEC<\/span>\u00a0Filterprice<\/span>
\n\u00a0\u00a0337.22<\/span>\u00a0WITH<\/span>\u00a0recompile<\/span>\u00a0<\/span><\/div>\n
\u00a0<\/div>\n

\"\"<\/p>\n

 <\/p>\n

Causing the problem<\/h2>\n
\u00a0 <\/span><\/div>\n

The result of the execution of the queries again, without using the With Recompile<\/strong>\u00a0is both queries will be using the same plan, which will be wrong for one of them.<\/p>\n

 <\/p>\n

EXEC<\/span>\u00a0Filterprice<\/span>
\n\u00a0\u00a09.99<\/span> <\/p>\n

EXEC<\/span>\u00a0Filterprice<\/span>
\n\u00a0\u00a0337.22<\/span>\u00a0 <\/span><\/div>\n

\"\"<\/p>\n

\nWhen one of these procedures is executed without the recompile option, the plan will be included in the query plan cache. The next execution will use the same plan again, causing a problem, because the plan may not be the best choice to the value.<\/p>\n

On the original article, we solved the problem using a plan guide to force the recompile of the query. Let’s solve the problem this time using Query Store Hints.<\/p>\n

\nUsing Query Store Hints to solve the problem<\/h2>\n

The Query Store hints are applied over the queries. We need to discover the id of this query inside query store. The following statement can do the work:<\/p>\n

SELECT<\/span>\u00a0query_sql_text<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0q<\/span>.<\/span>query_id<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>query_store_query_text<\/span>\u00a0qt<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>query_store_query<\/span>\u00a0q<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0qt<\/span>.<\/span>query_text_id<\/span>\u00a0=<\/span>\u00a0q<\/span>.<\/span>query_text_id<\/span>
\nWHERE<\/span>\u00a0\u00a0query_sql_text<\/span>\u00a0LIKE<\/span>\u00a0N’%bigproduct%’<\/span>
\ngo<\/span>\u00a0 <\/span><\/div>\n

\"\"<\/p>\n

 <\/p>\n

Even the query being inside the stored procedure, it will be individually recorded in query store. Once we know the id of the query, we can make the application of the query hint using a stored procedure:<\/p>\n

EXEC<\/span>\u00a0Sp_query_store_set_hints<\/span>
\n\u00a0\u00a0@query_id<\/span>=<\/span>1<\/span>,<\/span>
\n\u00a0\u00a0@value<\/span>\u00a0=<\/span>\u00a0N’OPTION(RECOMPILE)’<\/span>;<\/span> <\/p>\n

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

\u00a0<\/div>\n
Execute the queries again and now each one will have the best plan according the parameter. We also have an additional benefit in comparison to other solutions, such as setting the procedure as WITH RECOMPILE<\/strong> : Only a single query is being recompiled, not the entire procedure.<\/div>\n

\nMonitoring<\/h2>\n

Once you decide to apply query store hints, there are some monitoring tasks you need to execute:<\/p>\n

    \n
  • You need to monitor the hints. In case of a failure in a hint, you need to react to that.<\/li>\n
  • You need to monitor query regressions. If a hint is not needed anymore, you can suffer from a query regression and this will appear as part of the monitoring.<\/li>\n<\/ul>\n

    The following query can be used to monitor the hints:<\/p>\n

    SELECT<\/span>\u00a0query_hint_id<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_id<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_hint_text<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_query_hint_failure_reason<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_query_hint_failure_reason_desc<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query_hint_failure_count<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0source<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0source_desc<\/span>
    \nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>query_store_query_hints<\/span>;<\/span> <\/p>\n

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

    The Future<\/h2>\n

    SQL Server 2022<\/strong> is bringing many new optimization features. They are becoming more intelligent and being built over previous existing features. Some of the new optimization features will be automatically creating query store hints.<\/p>\n

    Conclusion<\/h2>\n

    The SQL Server<\/strong> optimization features are becoming more intelligent on each version. Each new optimization feature, however, brings new and more advanced needs for monitoring.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Dennes Torres explains how Query Store hints work in Azure SQL Database and Managed Instance.…<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[136322,145785,4151],"coauthors":[6810],"class_list":["post-92867","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure-sql","tag-query-store","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92867","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92867"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92867\/revisions"}],"predecessor-version":[{"id":94190,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92867\/revisions\/94190"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92867"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

\nOriginal link: https:\/\/devio.wordpress.com\/2018\/06\/03\/generating-a-range-of-numbers-and-dates-in-t-sql\/<\/a><\/p>\n