{"id":923,"date":"2018-06-15T04:00:03","date_gmt":"2018-06-15T11:00:03","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=923"},"modified":"2018-06-15T06:30:06","modified_gmt":"2018-06-15T13:30:06","slug":"query-store-and-the-plan-cache-flushing","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/","title":{"rendered":"Query Store and the Plan Cache Flushing"},"content":{"rendered":"

I\u2019ve had two comments recently on my blog about Query Store causing the plan cache to be flushed. There was a known issue<\/a> related to the plan cache flushing after Query Store was enabled, but this was fixed in CU2 for SQL Server 2016 SP1<\/a>. So I did some\u00a0testing and here is what I think is causing the confusion:<\/p>\n

When you enable Query Store, which is done with an ALTER DATABASE SET statement, the plan cache for the database is flushed.<\/p>\n

Now, before anyone writes up a UserVoice<\/a>\u00a0item, understand that there are several ALTER DATABASE SET<\/a> commands that cause the plan cache for a database to be flushed. For example, taking a database OFFLINE causes the database plan cache to be flushed. That one seems intuitive, right?\u00a0 So why is the plan cache cleared when you enable Query Store, or change one of the settings?\u00a0 To ensure that new Query Store data is not lost.\u00a0 This relates to the internals of how Query Store works, which aren’t essential to dig into, the point is that this behavior is known by Microsoft and expected.<\/p>\n

If you review the ALTER DATABASE SET documentation, and specifically review the Query Store options, you won\u2019t find any mention of the database plan cache clearing.\u00a0 But you can test it to see that it occurs…<\/p>\n

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

First, disable Query Store for the WideWorldImporters database, and then free the plan cache:<\/p>\n

\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE = OFF;\r\nGO\r\n\r\nDBCC FREEPROCCACHE;\r\nGO\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nSELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\n\tON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = 972;\r\nGO\r\nSELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\n\tON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = 123;\r\nGO\r\n<\/pre>\n
Now query the plan cache to confirm those plans are in cache:<\/p>\n
\r\n\r\nSELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan\r\nFROM sys.dm_exec_query_stats s\r\nCROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p\r\nCROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t\r\nWHERE t.text LIKE '%Sales%';\r\nGO\r\n<\/pre>\n
\"SQL<\/a>
SQL Server’s plan cache after initial query execution<\/figcaption><\/figure>\n
Great, they’re there. Now enable Query Store, then check the plan cache again.<\/p>\n
\r\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\nSELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan\r\nFROM sys.dm_exec_query_stats s\r\nCROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p\r\nCROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t\r\nWHERE t.text LIKE '%Sales%';\r\nGO\r\n<\/pre>\n
\"SQL<\/a>
SQL Server’s plan cache after enabling Query Store (plans have been cleared)<\/figcaption><\/figure>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
The plan cache for the database has been cleared. Note that this only clears the plan cache for that database – plans for other databases still remain in cache. Run a few more queries to add some plans back to the plan cache, and confirm they’re there.<\/p>\n
\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nSELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\n\tON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = 972;\r\nGO\r\nSELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity\r\nFROM Sales.Orders o\r\nJOIN Sales.OrderLines ol\r\n\tON o.OrderID = ol.OrderID\r\nWHERE o.CustomerID = 123;\r\nGO\r\n\r\nSELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan\r\nFROM sys.dm_exec_query_stats s\r\nCROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p\r\nCROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t\r\nWHERE t.text LIKE '%Sales%';\r\nGO\r\n<\/pre>\n
\"SQL<\/a>
SQL Server’s plan cache after enabling Query Store AND running some queries<\/figcaption><\/figure>\n
This time, change one of the settings for Query Store. It’s already enabled, but perhaps we want to change the INTERVAL_LENGTH_MINUTES setting from the default of 60 minutes to 30 minutes.<\/p>\n
\r\n\r\nUSE [master]\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30)\r\nGO\r\n\r\nSELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan\r\nFROM sys.dm_exec_query_stats s\r\nCROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p\r\nCROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t\r\nWHERE t.text LIKE '%Sales%';\r\nGO\r\n<\/pre>\n
\"SQL<\/a>
SQL Server’s plan cache after changing a Query Store setting (plans have been cleared)<\/figcaption><\/figure>\n
In checking the plan cache again, the ALTER DATABASE SET statement cleared the database’s cache.<\/p>\n
Summary<\/strong><\/p>\n
As you can see, the database plan cache\u00a0is cleared after you enable Query Store, or change any settings related to Query Store. This is the same behavior we see with other ALTER DATABASE SET commands (e.g. changing the recovery model).\u00a0 Unfortunately, this is not documented, nor is anything written to the ERRORLOG.<\/p>\n
Of note: I don\u2019t expect that you are changing settings often (if you are, I\u2019d like to understand that thought process, as once you find the right values for space and interval, I expect those settings to be static\u2026and if you\u2019re not sure where to start, feel free to check out my post\u00a0discussing the different\u00a0options<\/a>). I also don\u2019t expect that you are turning Query Store on and off throughout the day; that completely defeats the purpose of the feature. It should be enabled, and left enabled, all the time. You don\u2019t know when a problem might occur, right?<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019ve had two comments recently on my blog about Query Store causing the plan cache to be flushed. There was a known issue related to the plan cache flushing after Query Store was enabled, but this was fixed in CU2 for SQL Server 2016 SP1. So I did some\u00a0testing and here is what I think […]<\/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":"\nQuery Store and the Plan Cache Flushing - Erin Stellato<\/title>\n<meta name=\"description\" content=\"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.\" \/>\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\/query-store-and-the-plan-cache-flushing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store and the Plan Cache Flushing - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-15T11:00:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-15T13:30:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/06\/plancache1.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=\"5 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\/query-store-and-the-plan-cache-flushing\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/\",\"name\":\"Query Store and the Plan Cache Flushing - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-06-15T11:00:03+00:00\",\"dateModified\":\"2018-06-15T13:30:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store and the Plan Cache Flushing\"}]},{\"@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":"Query Store and the Plan Cache Flushing - Erin Stellato","description":"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.","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\/query-store-and-the-plan-cache-flushing\/","og_locale":"en_US","og_type":"article","og_title":"Query Store and the Plan Cache Flushing - Erin Stellato","og_description":"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/","og_site_name":"Erin Stellato","article_published_time":"2018-06-15T11:00:03+00:00","article_modified_time":"2018-06-15T13:30:06+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/06\/plancache1.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/","name":"Query Store and the Plan Cache Flushing - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-06-15T11:00:03+00:00","dateModified":"2018-06-15T13:30:06+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"I've seen several concerns related to Query Store and the Plan Cache Flushing - and in some cases this occurs on purpose.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-the-plan-cache-flushing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store and the Plan Cache Flushing"}]},{"@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\/923"}],"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=923"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/923\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}