{"id":802,"date":"2017-02-02T07:00:27","date_gmt":"2017-02-02T15:00:27","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=802"},"modified":"2017-04-13T09:19:55","modified_gmt":"2017-04-13T16:19:55","slug":"forced-plans-and-compatibility-mode","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/","title":{"rendered":"Forced Plans and Compatibility Mode"},"content":{"rendered":"

A question came up recently about plan guides and compatibility mode, and it got me thinking about forced plans in Query Store and compatibility mode.\u00a0 Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator.\u00a0 At some point, you have a plan that you force for a specific query, and that works great.\u00a0 As time goes on, you do testing with the new CE and eventually are ready to make the switch to compatibility mode 130.\u00a0 When you do that, does the forced plan continue to use compatibility mode 110?\u00a0 I had a guess at the answer but thought it was worth testing.<\/p>\n

Setup<\/strong><\/p>\n

I restored a copy of WideWorldImporters to my SQL 2016 SP1 instance and set the compatibility mode to 110:<\/p>\n

\r\nUSE [master];\r\nGO\r\n\r\nRESTORE DATABASE [WideWorldImporters]\r\nFROM\u00a0 DISK = N'C:\\Backups\\WideWorldImporters-Full.bak'\r\nWITH\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] SET COMPATIBILITY_LEVEL = 110\r\nGO\r\n<\/pre>\n
Then I enabled Query Store and cleared out any old data that might exist (remember that WideWorldImporters is a sample database so who knows what might exist in the Query Store views):<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE (\r\nOPERATION_MODE = READ_WRITE,\r\nCLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),\r\nDATA_FLUSH_INTERVAL_SECONDS = 900,\r\nINTERVAL_LENGTH_MINUTES = 60,\r\nMAX_STORAGE_SIZE_MB = 512,\r\nQUERY_CAPTURE_MODE = ALL,\r\nSIZE_BASED_CLEANUP_MODE = AUTO,\r\nMAX_PLANS_PER_QUERY = 200);\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;\r\nGO\r\n<\/pre>\n
Next I\u2019ll create a stored procedure to use for testing, and then I\u2019ll run it twice with the RECOMPILE option, as this will generate two different plans.<\/p>\n
\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nDROP PROCEDURE IF EXISTS [Sales].[usp_GetFullProductInfo];\r\nGO\r\n\r\nCREATE PROCEDURE [Sales].[usp_GetFullProductInfo]\r\n@StockItemID INT\r\nAS\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] on [o].[OrderID] = [ol].[OrderID]\r\nWHERE [StockItemID] = @StockItemID\r\nORDER BY [o].[OrderDate] DESC;\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 220\u00a0 WITH RECOMPILE;\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 105\u00a0 WITH RECOMPILE;\r\nGO\r\n<\/pre>\n
Forcing a plan<\/strong><\/p>\n
We\u2019ll start by looking at the two different plans in Query Store.\u00a0 You can do this through the UI, or by using TSQL.\u00a0 I\u2019ll use both, just for fun, and we\u2019ll start with TSQL.<\/p>\n
\r\nSELECT\r\n[q].[query_id],\r\n[q].[object_id],\r\n[o].[name],\r\n[p].[compatibility_level],\r\n[qt].[query_sql_text],\r\n[p].[plan_id],\r\nTRY_CONVERT(XML,[p].[query_plan]) AS [QueryPlan]\r\nFROM [sys].[query_store_query] [q]\r\nJOIN [sys].[query_store_query_text] [qt]\r\nON [q].[query_text_id] = [qt].[query_text_id]\r\nJOIN [sys].[query_store_plan] [p]\r\nON [q].[query_id] = [p].[query_id]\r\nJOIN [sys].[objects] [o]\r\nON [q].[object_id] = [o].[object_id]\r\nWHERE [q].[object_id] = OBJECT_ID(N'Sales.usp_GetFullProductInfo');\r\nGO\r\n<\/pre>\n
 <\/p>\n
\"Query<\/a>
Query Store output – two different plans<\/figcaption><\/figure>\n
 <\/p>\n
You can see in the output that there are two different plans (plan_id 3 and plan_id 4)for this stored procedure query.\u00a0 I can click on the XML link to see each plan, and then compare them, or I can do this from within Query Store.\u00a0 It\u2019s easier within Query Store, I just need to know the query_id (3).\u00a0 Within Management Studio, expand the WideWorldImporters database, expand Query Store, then double-click on Tracked Queries and enter the query_id in the Tracking Query box.<\/p>\n
 <\/p>\n
\"Two<\/a>
Two different plans for query_id 3<\/figcaption><\/figure>\n
 <\/p>\n
You\u2019ll see that there are two plans, and to compare them you click on both plans in the plan id window (hold down the CTRL key to get them both) and then select Compare Plans.<\/p>\n
 <\/p>\n
\"Comparing<\/a>
Comparing both plans<\/figcaption><\/figure>\n
 <\/p>\n
In looking at the plans, you see that the shapes are similar, but Plan 3 has a Nested Loop, while Plan 4 Merge Join that\u2019s fed by a Sort.\u00a0 For this example, we\u2019ll decide that the Nested Loop plan is \u201cbetter\u201d for this query, so that\u2019s the one we will force.<\/p>\n
However, before we make that change, let\u2019s see if we get a different plan with compatibility mode 130.<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;\r\nGO\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 105\u00a0 WITH RECOMPILE;\r\nGO\r\n<\/pre>\n
Check Query Store again\u2026<\/p>\n
\r\nSELECT\r\n[q].[query_id],\r\n[q].[object_id],\r\n[o].[name],\r\n[p].[compatibility_level],\r\n[qt].[query_sql_text],\r\n[p].[plan_id],\r\nTRY_CONVERT(XML,[p].[query_plan]) AS [QueryPlan]\r\nFROM [sys].[query_store_query] [q]\r\nJOIN [sys].[query_store_query_text] [qt]\r\nON [q].[query_text_id] = [qt].[query_text_id]\r\nJOIN [sys].[query_store_plan] [p]\r\nON [q].[query_id] = [p].[query_id]\r\nJOIN [sys].[objects] [o]\r\nON [q].[object_id] = [o].[object_id]\r\nWHERE [q].[object_id] = OBJECT_ID(N'Sales.usp_GetFullProductInfo');\r\nGO\r\n<\/pre>\n
\"Query<\/a>
Query Store output – now three different plans<\/figcaption><\/figure>\n
We DO have a different plan!\u00a0 If we look at the plan, we see that the shape is still similar, but now we have a Hash Match with a Filter operator and a Clustered Index Scan.<\/p>\n
\"Plan<\/a>
Plan from compatibility mode 130<\/figcaption><\/figure>\n
 <\/p>\n
Now we want to force that Nested Loop plan.\u00a0 First, change the compatibility mode back to 110:<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;\r\nGO\r\n<\/pre>\n
Next, force the plan that has the Nested Loop, and we can do this in the UI, or with TSQL.\u00a0 In the UI just go back to the Tracked Queries window, select the plan, and then Force Plan.\u00a0 To force the plan with TSQL, you need to know the query_id and plan_id:<\/p>\n
\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nEXEC sp_query_store_force_plan @query_id = 3, @plan_id = 3;\r\nGO\r\n<\/pre>\n
Now the plan is forced.\u00a0 If we enable the actual execution plan and re-run our stored procedure\u00a0without the RECOMPILE on it<\/em> (because why would you use RECOMPILE on a query with a forced plan?) we see that the Nested Loop plan is used:<\/p>\n
\r\nEXEC [Sales].[usp_GetFullProductInfo] 105;\r\nGO\r\n<\/pre>\n
\"Stored<\/a>
Stored procedure’s execution plan, after being forced<\/figcaption><\/figure>\n
And here\u2019s the big test\u2026\u00a0 Change compatibility mode to 130 again, free procedure cache just for fun (this does not matter \u2013 when a plan is forced, it doesn\u2019t matter if the plan exists in cache or not), and then run the stored procedure and check the plan:<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;\r\nGO\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nEXEC [Sales].[usp_GetFullProductInfo] 105\u00a0 WITH RECOMPILE;\r\nGO\r\n<\/pre>\n
\"Stored<\/a>
Stored procedure’s execution plan, after compatibility mode changed to 130<\/figcaption><\/figure>\n
Surprised? \u00a0The Nested Loop plan is still used.\u00a0 This is expected!\u00a0 It does not matter if the compatibility mode for the database is different than the compatibility mode for the plan.\u00a0 The forced plan is what\u2019s used.<\/p>\n
Summary<\/strong><\/p>\n
In this example, even when the compatibility mode for the database changed, the forced plan was still used.\u00a0 Thus, forced plans are not tied to compatibility mode.\u00a0 This is a good thing.\u00a0 If you’ve upgraded to SQL Server 2016 and you are working to fix query performance issues related to the new cardinality estimator, forcing plans can be incredibly helpful in stabilizing performance without changing code to include trace flags or hints.\u00a0 However, do not assume that a forced plan will always<\/em> be used.\u00a0 If you look at the Best Practice with the Query Store<\/a> guidelines, there’s a section titled “Check the Status of Forced Plans Regularly.”\u00a0 Within that section is this note:<\/p>\n
However, as with plan hints and plan guides, forcing a plan is not a guarantee that it will be used in future executions.<\/span><\/p>\n
Therefore, while you force a plan because you want it to be used – to make query performance more stable – SQL Server does not guarantee it will always<\/em> be used.\u00a0 There are cases when it cannot, and should not, be used, hence the recommendation to check the status of forced plans in sys.query_store_plan.<\/p>\n","protected":false},"excerpt":{"rendered":"
A question came up recently about plan guides and compatibility mode, and it got me thinking about forced plans in Query Store and compatibility mode.\u00a0 Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator.\u00a0 At some point, you have a plan […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nForced Plans and Compatibility Mode - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in 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\/forced-plans-and-compatibility-mode\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Forced Plans and Compatibility Mode - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in Query Store.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-02-02T15:00:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/02\/query-store-output-two-different-plans.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=\"7 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\/forced-plans-and-compatibility-mode\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/\",\"name\":\"Forced Plans and Compatibility Mode - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-02-02T15:00:27+00:00\",\"dateModified\":\"2017-04-13T16:19:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in Query Store.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Forced Plans and Compatibility Mode\"}]},{\"@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":"Forced Plans and Compatibility Mode - Erin Stellato","description":"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in 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\/forced-plans-and-compatibility-mode\/","og_locale":"en_US","og_type":"article","og_title":"Forced Plans and Compatibility Mode - Erin Stellato","og_description":"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in Query Store.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/","og_site_name":"Erin Stellato","article_published_time":"2017-02-02T15:00:27+00:00","article_modified_time":"2017-04-13T16:19:55+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/02\/query-store-output-two-different-plans.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/","name":"Forced Plans and Compatibility Mode - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-02-02T15:00:27+00:00","dateModified":"2017-04-13T16:19:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Changing compatibility mode for a SQL Server database does not affect the use of a forced plan in Query Store.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/forced-plans-and-compatibility-mode\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Forced Plans and Compatibility Mode"}]},{"@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\/802"}],"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=802"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/802\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}