{"id":857,"date":"2017-09-25T12:28:04","date_gmt":"2017-09-25T19:28:04","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=857"},"modified":"2017-09-26T08:42:57","modified_gmt":"2017-09-26T15:42:57","slug":"changes-to-query-text-and-the-effects-in-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/","title":{"rendered":"Changes to query text and the effects in Query Store"},"content":{"rendered":"

In this week’s Insider newsletter<\/a>, Paul talked about an interesting customer issue I ran into last week related to Query Store, and an inadvertent change to a query’s text.\u00a0 I thought I’d provide some demo code so that those of you using (or planning to use) Query Store could see how this shows up in your database.<\/p>\n

Recreating the issue<\/strong><\/p>\n

I started by restoring a copy of the WideWorldImporters database, which you can download here<\/a>.\u00a0 I enabled Query Store and cleared out the data, just as a precaution.<\/p>\n

\r\nUSE [master];\r\nGO\r\nRESTORE DATABASE [WideWorldImporters]\r\nFROM\u00a0 DISK = N'C:\\Backups\\WideWorldImporters-Full.bak' WITH\u00a0 FILE = 1,\r\nMOVE N'WWI_Primary' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters.mdf',\r\nMOVE N'WWI_UserData' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters_UserData.ndf',\r\nMOVE N'WWI_Log' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters.ldf',\r\nMOVE N'WWI_InMemory_Data_1' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters_InMemory_Data_1',\r\nNOUNLOAD,\r\nREPLACE,\r\nSTATS = 5;\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters]\r\nSET QUERY_STORE = ON;\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters]\r\nSET QUERY_STORE CLEAR;\r\nGO\r\n<\/pre>\n
I then created a stored procedure with just one query in it:<\/p>\n
\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nCREATE PROCEDURE [Sales].[usp_GetFullProductInfo]\r\n@StockItemID INT\r\nAS\r\n\r\nSELECT\r\n[o].[CustomerID],\r\n[o].[OrderDate],\r\n[ol].[StockItemID],\r\n[ol].[Quantity],\r\n[ol].[UnitPrice]\r\nFROM [Sales].[Orders] [o]\r\nJOIN [Sales].[OrderLines] [ol]\r\nON [o].[OrderID] = [ol].[OrderID]\r\nWHERE [ol].[StockItemID] = @StockItemID\r\nORDER BY [o].[OrderDate] DESC;\r\nGO\r\n<\/pre>\n
Then I executed the stored procedure twice, with two different input parameters.\u00a0 I purposely used WITH RECOMPILE here because I knew the values would generate different plans.<\/p>\n
\r\nEXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;\r\nGO\r\n<\/pre>\n
Within Object Explorer I then drilled into the Query Store reports (Databases | WideWorldImporters | Query Store) and double-clicked to run Top Resource Consuming Queries.\u00a0 My query has two different plans:<\/p>\n
\"Initial<\/a>
Initial query execution, two plans<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Plan_id 1 has a clustered index scan with a filter, that feeds into the probe phase of a hash match (with a columnstore index scan for the build). This was generated with the input value of 90.<\/p>\n
\"Plan<\/a>
Plan 1 for query_id 1<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Plan_id 2 has the same columnstore index scan, but it feeds into a nested loop, with a clustered index seek for inner input, and was generated with the input value of 224.<\/p>\n
\"Plan<\/a>
Plan 2 for query_id 1<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Now let’s suppose I decide to force one plan for that query. After analysis, I decide to force the second plan (plan_id 2), which I can do either through the UI, or using a stored procedure. Since change control and documentation is a good thing, we’ll use the stored procedure:<\/p>\n
\r\nEXEC sp_query_store_force_plan @query_id = 3, @plan_id = 8;\r\nGO\r\n<\/pre>\n
At this point, if I re-run the stored procedure with the two different input parameters and WITH RECOMPILE, I will get the plan with the nested loop.<\/p>\n
\r\nEXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;\r\nGO\r\n<\/pre>\n
\"SP<\/a>
SP execution after plan is forced<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Now…let’s assume that this forced plan is used going forward and there are no issues. And then let’s assume that a developer decides to add some comments to the stored procedure, and they know they should use ALTER to keep the object_id the same…but somehow, a couple extra spaces end up in the query.<\/p>\n
\r\nALTER PROCEDURE [Sales].[usp_GetFullProductInfo]\r\n@StockItemID INT\r\nAS\r\n\/*\r\nThis is getting information based on ID\r\n*may need to add more columns per BL\r\n*\/\r\nSELECT\r\n[o].[CustomerID],\r\n[o].[OrderDate],\r\n[ol].[StockItemID],\r\n[ol].[Quantity],\r\n[ol].[UnitPrice]\r\nFROM [Sales].[Orders] [o]\r\nJOIN [Sales].[OrderLines] [ol]\r\nON [o].[OrderID] = [ol].[OrderID]\r\nWHERE [ol].[StockItemID] = @StockItemID\r\nORDER BY [o].[OrderDate] DESC;\r\n\r\nGO\r\n<\/pre>\n
I re-run the stored procedures again (WITH RECOMPILE) and now I get that other plan again for the input value of 90:<\/p>\n
\"SP<\/a>
SP execution after ALTER, spaces inadvertenly added<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
If I look in Query Store, I can see I now have two queries that look nearly identical:<\/p>\n
\"Two<\/a>
Two queries in QS after SP was changed<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
There are different query_id values in sys.query_store_query, as well as different entries for each query in sys.query_store_query_text. Both queries are still tied to the stored procedure (object_id 1483152329) which can be confusing if you don’t understand what occurred. In order to confirm which query has executed most recently, you can use the query below, which joins both system views (replace the query_ids as appropriate):<\/p>\n
\r\nSELECT\r\n[qst].[query_text_id],\r\n[qst].[query_sql_text],\r\n[qst].[statement_sql_handle],\r\n[qs].[query_id],\r\n[qs].[query_hash],\r\n[qs].[last_execution_time],\r\n[qs].[object_id]\r\nFROM [sys].[query_store_query] [qs]\r\nJOIN [sys].[query_store_query_text] [qst]\r\nON [qs].[query_text_id] = [qst].[query_text_id]\r\nWHERE [qs].[query_id] IN (1, 14);\r\nGO\r\n<\/pre>\n
\"Information<\/a>
Information for both queries from Query Store views<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Summary<\/strong><\/p>\n
The take home message here is that you need to be careful when changing objects (stored procedures, functions) where you have queries with forced plans. Inadvertent changes in spacing or casing create entirely separate queries in Query Store, which means that the original query with a forced plan is no longer in use.\u00a0 You’ll either have to ALTER the procedure again without the extra spaces in the query (which may not be as easy as it sounds), or get the new version of the query to execute the “right” plan, and then force that plan.\u00a0 It may be appropriate to set up monitoring for object changes, or teach your developers how to identify what queries are forced and to what objects they below (hint: use the Queries With Forced Plans report that’s available in SSMS 17.x). Hope this helps, and if you want to learn more about Query Store, consider my full day pre-con at Summit<\/a> next month where we’ll cover this type of scenario and a whole lot more! Feel free to reach out if you have questions about it \ud83d\ude42<\/p>\n
p.s. did you see that they announced the release date for SQL Server 2017<\/a>? I’ll be running that build at my pre-con!<\/p>\n","protected":false},"excerpt":{"rendered":"
In this week’s Insider newsletter, Paul talked about an interesting customer issue I ran into last week related to Query Store, and an inadvertent change to a query’s text.\u00a0 I thought I’d provide some demo code so that those of you using (or planning to use) Query Store could see how this shows up in […]<\/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":"\nChanges to query text and the effects in Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.\" \/>\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\/changes-to-query-text-and-the-effects-in-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Changes to query text and the effects in Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-09-25T19:28:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-09-26T15:42:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/09\/twoplans-1024x243.jpg\" \/>\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=\"6 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\/changes-to-query-text-and-the-effects-in-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/\",\"name\":\"Changes to query text and the effects in Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-09-25T19:28:04+00:00\",\"dateModified\":\"2017-09-26T15:42:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Changes to query text and the effects in 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":"Changes to query text and the effects in Query Store - Erin Stellato","description":"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.","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\/changes-to-query-text-and-the-effects-in-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Changes to query text and the effects in Query Store - Erin Stellato","og_description":"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2017-09-25T19:28:04+00:00","article_modified_time":"2017-09-26T15:42:57+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/09\/twoplans-1024x243.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/","name":"Changes to query text and the effects in Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-09-25T19:28:04+00:00","dateModified":"2017-09-26T15:42:57+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you're using Query Store, changing the text of a query can result in a forced plan no longer being used, and potentially a negative change performance.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/changes-to-query-text-and-the-effects-in-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Changes to query text and the effects in 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\/857"}],"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=857"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/857\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}