{"id":922,"date":"2018-06-18T05:00:04","date_gmt":"2018-06-18T12:00:04","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=922"},"modified":"2018-06-14T08:23:07","modified_gmt":"2018-06-14T15:23:07","slug":"can-you-force-a-plan-for-a-different-query-with-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/","title":{"rendered":"Can you force a plan for a different query with Query Store?"},"content":{"rendered":"

This is question I\u2019ve gotten a few times in class…Can you force a plan for a different query with Query Store?<\/p>\n

tl;dr<\/p>\n

No.<\/p>\n

Assume you have two similar queries, but they have different query_id values in Query Store.\u00a0 One of the queries has a plan that\u2019s stable, and I want to force that plan for the other query.\u00a0 Query Store provides no ability to do this in the UI, but you can try it with the stored procedure.\u00a0 Let\u2019s take a look\u2026<\/p>\n

Testing<\/strong><\/p>\n

Within WideWorldImporters we’ll execute an ad-hoc query with two different input values:<\/p>\n

\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);\r\nGO\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nDECLARE @CustomerID INT;\r\nSET @CustomerID = 972;\r\n\r\nSELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\nON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = @CustomerID;\r\nGO\r\n\r\nDECLARE @CustomerID2 INT;\r\nSET @CustomerID2 = 972;\r\n\r\nSELECT o.ContactPersonID, o.OrderDate, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\nON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = @CustomerID2;\r\nGO\r\n\r\n<\/pre>\n
Let’s see what’s in Query Store:<\/p>\n
\r\n\r\nSELECT qt.query_text_id, q.query_id, qt.query_sql_text, p.plan_id, TRY_CAST(p.query_plan AS XML)\r\nFROM sys.query_store_query_text qt\r\nJOIN sys.query_store_query q\r\nON qt.query_text_id = q.query_text_id\r\nJOIN sys.query_store_plan p\r\nON q.query_id = p.query_id\r\nWHERE qt.query_sql_text LIKE '%Sales.Orders%';\r\nGO\r\n\r\n<\/pre>\n
\"Query<\/a>
Query information from Query Store<\/figcaption><\/figure>\n
We see that we have two different queries and one plan for each. We can force the plan for the first query:<\/p>\n
\r\nEXEC sp_query_store_force_plan @query_id = 3, @plan_id = 3;\r\nGO\r\n<\/pre>\n
This works.\u00a0 If we try to force that same plan for the other query:<\/p>\n
\r\nEXEC sp_query_store_force_plan @query_id = 4, @plan_id = 3;\r\nGO\r\n<\/pre>\n
\"Error<\/a>
Error when trying to force a different plan for a query<\/figcaption><\/figure>\n
Trying to force plan_id\u00a03 for query_id\u00a04 throws this error:<\/p>\n
Msg 12406, Level 11, State 1, Procedure sp_query_store_force_plan, Line 1 [Batch Start Line 34]<\/span>\r\n Query plan with provided plan_id (2) is not found in the Query Store for query (4). Check the plan_id value and rerun the command.<\/span><\/pre>\n
Summary<\/strong>
\nWithin Query Store, the relationship between query_id and plan_id is managed internally (i.e. there are no foreign key constraints for the underlying tables), and there is a validation that any plan_id that you want to force for a query_id must have been generated for that specific query.<\/p>\n
In this type of scenario, you have to get the plan shape you want for the query, which may require trying different input parameters.\u00a0 The example I\u2019ve provided is very simple, but when in doubt, check the input parameters for the plan that you want, then try those with the other query (that doesn\u2019t yet have the plan you want to force).\u00a0 Of course, if you have to use a query or index hint to get the plan that you want, then it’s going to be a little trickier to get the plan you want for the original query.\u00a0 Good luck!<\/p>\n","protected":false},"excerpt":{"rendered":"
This is question I\u2019ve gotten a few times in class…Can you force a plan for a different query with Query Store? tl;dr No. Assume you have two similar queries, but they have different query_id values in Query Store.\u00a0 One of the queries has a plan that\u2019s stable, and I want to force that plan for […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nCan you force a plan for a different query with Query Store? - Erin Stellato<\/title>\n<meta name=\"description\" content=\"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can you force a plan for a different query with Query Store? - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-18T12:00:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-14T15:23:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/06\/plans1.png\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/\",\"name\":\"Can you force a plan for a different query with Query Store? - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-06-18T12:00:04+00:00\",\"dateModified\":\"2018-06-14T15:23:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can you force a plan for a different query with Query Store?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Can you force a plan for a different query with Query Store? - Erin Stellato","description":"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Can you force a plan for a different query with Query Store? - Erin Stellato","og_description":"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2018-06-18T12:00:04+00:00","article_modified_time":"2018-06-14T15:23:07+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/06\/plans1.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/","name":"Can you force a plan for a different query with Query Store? - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-06-18T12:00:04+00:00","dateModified":"2018-06-14T15:23:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"There are some interesting, but valid, scenarios where you may want to force a plan for a different query using Query Store.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/can-you-force-a-plan-for-a-different-query-with-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Can you force a plan for a different query with Query Store?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/922"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=922"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/922\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}