{"id":888,"date":"2018-01-31T15:37:40","date_gmt":"2018-01-31T23:37:40","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=888"},"modified":"2018-01-31T16:15:12","modified_gmt":"2018-02-01T00:15:12","slug":"query-store-and-in-memory","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/","title":{"rendered":"Query Store and \u201cin memory\u201d"},"content":{"rendered":"<p>If you\u2019ve ever been to one of my sessions you know that I really like demos.\u00a0 I find they can illustrate how things work extremely well and I use them to compliment an explanation.\u00a0 I\u2019m a very visual learner so it also helps me to understand and then explain a concept.\u00a0 The drawback of demos is that they sometimes fail, partly or entirely.\u00a0 I really work to make mine as infallible as possible, but every so often I run into a problem.<\/p>\n<p>When I was working on Query Store demos, one thing I noticed is that sometimes the data from the runtime stats system view seems to be duplicated.\u00a0 And this happens in my Pluralsight course on <a href=\"https:\/\/app.pluralsight.com\/library\/courses\/sqlserver-azure-database\/table-of-contents\">Query Store and Automatic Tuning<\/a>, funny enough.\u00a0 After restoring a database and stepping through a blocking scenario, when I query the runtime and wait stats with the query below, I get the following output:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\n&#x5B;rs].&#x5B;runtime_stats_interval_id],\r\n&#x5B;rsi].&#x5B;start_time] AS &#x5B;IntervalStartTime],\r\n&#x5B;rsi].&#x5B;end_time] AS &#x5B;IntervalEndTime],\r\n&#x5B;qsq].&#x5B;query_id],\r\n&#x5B;qst].&#x5B;query_sql_text],\r\n&#x5B;qsp].&#x5B;plan_id],\r\n&#x5B;rs].&#x5B;count_executions],\r\n&#x5B;rs].&#x5B;avg_duration],\r\n&#x5B;rs].&#x5B;avg_logical_io_reads],\r\n&#x5B;ws].&#x5B;wait_category_desc],\r\n&#x5B;ws].&#x5B;total_query_wait_time_ms]\r\nFROM &#x5B;sys].&#x5B;query_store_query] &#x5B;qsq]\r\nJOIN &#x5B;sys].&#x5B;query_store_query_text] &#x5B;qst]\r\nON &#x5B;qsq].&#x5B;query_text_id] = &#x5B;qst].&#x5B;query_text_id]\r\nJOIN &#x5B;sys].&#x5B;query_store_plan] &#x5B;qsp]\r\nON &#x5B;qsq].&#x5B;query_id] = &#x5B;qsp].&#x5B;query_id]\r\nJOIN &#x5B;sys].&#x5B;query_store_runtime_stats] &#x5B;rs]\r\nON &#x5B;qsp].&#x5B;plan_id] = &#x5B;rs].&#x5B;plan_id]\r\nJOIN &#x5B;sys].&#x5B;query_store_runtime_stats_interval] &#x5B;rsi]\r\nON &#x5B;rs].&#x5B;runtime_stats_interval_id] = &#x5B;rsi].&#x5B;runtime_stats_interval_id]\r\nJOIN &#x5B;sys].&#x5B;query_store_wait_stats] ws\r\nON &#x5B;qsp].&#x5B;plan_id] = &#x5B;ws].&#x5B;plan_id]\r\nAND &#x5B;rsi].&#x5B;runtime_stats_interval_id] = &#x5B;ws].&#x5B;runtime_stats_interval_id]\r\nWHERE &#x5B;qst].&#x5B;query_sql_text] LIKE '%UPDATE%'\r\nAND &#x5B;rs].&#x5B;execution_type] = 0\r\nORDER BY &#x5B;rsi].&#x5B;start_time] ASC, &#x5B;ws].&#x5B;total_query_wait_time_ms] DESC;\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<figure id=\"attachment_889\" aria-describedby=\"caption-attachment-889\" style=\"width: 1024px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"wp-image-889 size-large\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-1024x60.jpg\" alt=\"Query Store Runtime and Wait Statistics\" width=\"1024\" height=\"60\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-1024x60.jpg 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-300x18.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-900x53.jpg 900w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush.jpg 1852w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption id=\"caption-attachment-889\" class=\"wp-caption-text\">Query Store Runtime and Wait Statistics<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>You can see that there are two rows for the same query_id and plan_id, but the count_executions is different, as are the avg_duration and avg_logical_io_reads values .\u00a0 The data is not truly a duplicate.\u00a0 This behavior occurs because the Query Store data is stored in memory before it is flushed to disk, and when you query the data SQL Server is pulling it from both locations (and doing a UNION ALL) and displaying it in the output.\u00a0 If I waited a bit and ran the query again, the two rows for that interval would probably disappear \u2013 most likely because the in memory data had been flushed to disk.\u00a0 You can force the data to be flushed manually using <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-query-store-flush-db-transact-sql\">sp_query_store_flush_db<\/a>:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nEXEC sp_query_store_flush_db;\r\nGO\r\n<\/pre>\n<p>After I execute that stored procedure, when I query the runtime and stats data again, the output is only one line:<\/p>\n<p>&nbsp;<\/p>\n<figure id=\"attachment_890\" aria-describedby=\"caption-attachment-890\" style=\"width: 1024px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush.jpg\"><img decoding=\"async\" class=\"wp-image-890 size-large\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush-1024x44.jpg\" alt=\"Query Store Runtime and Wait Statistics After SP Flush\" width=\"1024\" height=\"44\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush-1024x44.jpg 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush-300x13.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush-900x39.jpg 900w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_AfterFlush.jpg 1852w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption id=\"caption-attachment-890\" class=\"wp-caption-text\">Query Store Runtime and Wait Statistics After SP Flush<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>The Query Store data is held in memory as an optimization \u2013 if the runtime stats had to be updated on disk every time a query executed, the amount of I\/O could easily overload a high-volume system.\u00a0 Therefore, data is stored in memory and flushed to disk based on the <strong>DATA_FLUSH_INTERVAL_SECONDS <\/strong>setting for Query Store, which defaults to 15 minutes.\u00a0 I recommend reading <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/how-query-store-collects-data\">How Query Store Collects Data<\/a> for more detail (and a nice visual).\u00a0 It\u2019s important to understand that even though I keep writing \u201cin memory\u201d and the documentation states that the sp_query_store_flush_db procedure \u201cflushes the in-memory portion of the Query Store data to disk\u201d, this data does not reside in In-Memory OLTP structures.<\/p>\n<p><em>Note: You can use Query Store to capture data for Natively Compiled stored procedures that access In-Memory OLTP objects, but you need to enable the collection of execution statistics using <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sys-sp-xtp-control-proc-exec-stats-transact-sql\">sys.sp_xtp_control_proc_exec_stats<\/a>, as described in <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/in-memory-oltp\/monitoring-performance-of-natively-compiled-stored-procedures\">Microsoft Docs<\/a>.<\/em><\/p>\n<p>If you&#8217;re using Query Store and you&#8217;ve run into similar behavior, hopefully this explains what you&#8217;ve seen and why, and you know how to address it going forward, if it presents an issue for some reason.<\/p>\n<p>Lastly, just yesterday the Tiger Team released some code you may need to run if you&#8217;re <a href=\"https:\/\/github.com\/Microsoft\/tigertoolbox\/tree\/master\/After-Installing-SQL-Server-2017_CU3\">using Query Store and you&#8217;ve upgraded to SQL Server 2017 CU3<\/a>.\u00a0 Please take a minute to review in case it applies to you!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you\u2019ve ever been to one of my sessions you know that I really like demos.\u00a0 I find they can illustrate how things work extremely well and I use them to compliment an explanation.\u00a0 I\u2019m a very visual learner so it also helps me to understand and then explain a concept.\u00a0 The drawback of demos [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Query Store and \u201cin memory\u201d - Erin Stellato<\/title>\n<meta name=\"description\" content=\"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.\" \/>\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-in-memory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store and \u201cin memory\u201d - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-31T23:37:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-02-01T00:15:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-1024x60.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=\"4 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-in-memory\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/\",\"name\":\"Query Store and \u201cin memory\u201d - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-01-31T23:37:40+00:00\",\"dateModified\":\"2018-02-01T00:15:12+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store and \u201cin memory\u201d\"}]},{\"@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 \u201cin memory\u201d - Erin Stellato","description":"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.","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-in-memory\/","og_locale":"en_US","og_type":"article","og_title":"Query Store and \u201cin memory\u201d - Erin Stellato","og_description":"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/","og_site_name":"Erin Stellato","article_published_time":"2018-01-31T23:37:40+00:00","article_modified_time":"2018-02-01T00:15:12+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/01\/RuntimeWaitStats_BeforeFlush-1024x60.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/","name":"Query Store and \u201cin memory\u201d - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-01-31T23:37:40+00:00","dateModified":"2018-02-01T00:15:12+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"An optimization to Query Store includes holding data in memory before flushing to disk, read why this is important and potential side-effects in this post.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store and \u201cin memory\u201d"}]},{"@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\/888"}],"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=888"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/888\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}