{"id":943,"date":"2018-07-17T06:00:29","date_gmt":"2018-07-17T13:00:29","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=943"},"modified":"2018-07-17T09:18:24","modified_gmt":"2018-07-17T16:18:24","slug":"monitoring-space-used-by-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/","title":{"rendered":"Monitoring Space Used by Query Store"},"content":{"rendered":"<p>Last week I presented a session on Query Store and when talking about the settings I mentioned that monitoring space used by Query Store is extremely important when you first enable it for a database.\u00a0 Someone asked me how I would do that and as I provided an explanation I realized that I should document my method&#8230;because I give the same example every time and I would be nice to have the code.<\/p>\n<p>For those of you not familiar with the Query Store settings, please check out <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\/\">my post<\/a>\u00a0which lists each one, the defaults, and what I would recommend for values and why.\u00a0 When discussing MAX_STORAGE_SIZE_MB, I mention monitoring via sys.database_query_store_options or Extended Events.\u00a0 As much as I love Extended Events, there isn&#8217;t an event that fires based on a threshold exceeded.\u00a0 The event related to size is query_store_disk_size_over_limit, and it fires when the space used exceeds the value for\u00a0MAX_STORAGE_SIZE_MB, which is too late.\u00a0 I want to take action\u00a0<em>before<\/em> the maximum storage size is hit.<\/p>\n<p>Therefore, the best option I&#8217;ve found is to create an Agent job which runs on a regular basis (maybe every four or six hours initially) that checks\u00a0current_storage_size_mb in sys.database_query_store_options and calculates the space used by Query Store as a\u00a0percentage of the total allocated, and then if that exceeds the threshold you set, send an email.\u00a0 The code that you can put into an Agent job is below.\u00a0 Please note you want to make sure the job runs in the context of the user database with Query Store enabled (as sys.database_query_store_options is a database view), and configure the threshold to a value that makes sense to your MAX_STORAGE_SIZE_MB.\u00a0 In my experience, 80% has been a good starting point, but feel free to adjust as you see fit!<\/p>\n<p>Once your Query Store size has been tweaked and stabilized, I would leave this job in place as a safety to alert you should anything change (e.g. someone else changes a Query Store setting which indirectly affects the storage used).<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n\/* Change DBNameHere as appropriate *\/\r\nUSE &#x5B;DBNameHere]\r\n\r\n\/* Change Threshold as appropriate *\/\r\nDECLARE @Threshold DECIMAL(4,2) = 80.00\r\nDECLARE @CurrentStorage INT\r\nDECLARE @MaxStorage INT\r\n\r\nSELECT @CurrentStorage = current_storage_size_mb, @MaxStorage = max_storage_size_mb\r\nFROM sys.database_query_store_options\r\n\r\nIF (SELECT CAST(CAST(current_storage_size_mb AS DECIMAL(21,2))\/CAST(max_storage_size_mb AS DECIMAL(21,2))*100 AS DECIMAL(4,2))\r\nFROM sys.database_query_store_options) &gt;= @Threshold\r\nBEGIN\r\n\r\n     DECLARE @EmailText NVARCHAR(MAX) = N'The Query Store current space used is ' + CAST(@CurrentStorage AS NVARCHAR(19)) + 'MB\r\n     and the max space configured is ' + CAST(@MaxStorage AS NVARCHAR(19)) + 'MB,\r\n     which exceeds the threshold of ' + CAST(@Threshold AS NVARCHAR(19) )+ '%.\r\n     Please allocate more space to Query Store or decrease the amount of data retained (stale_query_threshold_days).'\r\n\r\n     \/* Edit profile_name and recipients as appropriate *\/\r\n     EXEC msdb.dbo.sp_send_dbmail\r\n     @profile_name = 'SQL DBAs',\r\n     @recipients = 'DBAs@yourcompany.com',\r\n     @body = @EmailText,\r\n     @subject = 'Storage Threshold for Query Store Exceeded' ;\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Last week I presented a session on Query Store and when talking about the settings I mentioned that monitoring space used by Query Store is extremely important when you first enable it for a database.\u00a0 Someone asked me how I would do that and as I provided an explanation I realized that I should document [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,46],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Monitoring Space Used by Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.\" \/>\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\/monitoring-space-used-by-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Monitoring Space Used by Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-17T13:00:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-07-17T16:18:24+00:00\" \/>\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\/monitoring-space-used-by-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/\",\"name\":\"Monitoring Space Used by Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-07-17T13:00:29+00:00\",\"dateModified\":\"2018-07-17T16:18:24+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Monitoring Space Used by 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":"Monitoring Space Used by Query Store - Erin Stellato","description":"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.","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\/monitoring-space-used-by-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Monitoring Space Used by Query Store - Erin Stellato","og_description":"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2018-07-17T13:00:29+00:00","article_modified_time":"2018-07-17T16:18:24+00:00","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\/monitoring-space-used-by-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/","name":"Monitoring Space Used by Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-07-17T13:00:29+00:00","dateModified":"2018-07-17T16:18:24+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Monitoring space used by Query Store is important after you first enable this feature, and can be easily automated with a SQL Agent job.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/monitoring-space-used-by-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Monitoring Space Used by 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\/943"}],"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=943"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/943\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}