{"id":762,"date":"2016-05-16T08:51:33","date_gmt":"2016-05-16T15:51:33","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=762"},"modified":"2017-04-13T09:19:31","modified_gmt":"2017-04-13T16:19:31","slug":"sql-server-query-store-default-settings","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/","title":{"rendered":"SQL Server Query Store: Default Settings"},"content":{"rendered":"

Last week I wrote a quick post<\/a> about storage settings for Query Store.\u00a0 Today I’m going to take a moment to discuss the default settings that are applied when you initially enable Query Store.\u00a0 I’m not going go through all the settings and what they mean, you can find that information here<\/a>.\u00a0 But I do want to discuss from where they originate.<\/p>\n

When you enable Query Store through the UI, you have the option to change any of options (e.g. Max Size (MB), Size Based Cleanup Mode).\u00a0 The values for the settings are pulled from model, just like other database defaults (Recovery Model, Compatability Mode, etc.).\u00a0 Now, what’s interesting is that you can change these defaults in model, but only using T-SQL.\u00a0 In fact, you can only enable Query Store for model and msdb using TSQL, the Query Store page is not available for either database when you expand Databases, then System Database, and right-click on the database and select Properties.<\/p>\n

The Query Store documentation specifically states that it cannot be enabled for master and tempdb<\/a>, which leads us to believe we can enable it for model and msdb.\u00a0 Now, I haven’t figured out a use case for either (I have yet to troubleshoot query performance against model or msdb), but I figured I’d try it for both.\u00a0 I started with model, just using the basic syntax which would pull the default values for the options:<\/p>\n

\r\nUSE [master];\r\nGO\r\nALTER DATABASE [model] SET QUERY_STORE = ON;\r\nGO\r\nALTER DATABASE [model] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);\r\nGO\r\n<\/pre>\n
Then I checked to confirm the values that were set:<\/p>\n
\r\nUSE [model];\r\nGO\r\nSELECT actual_state_desc, readonly_reason, desired_state_desc, current_storage_size_mb,\r\nmax_storage_size_mb, flush_interval_seconds, interval_length_minutes,\r\nstale_query_threshold_days, size_based_cleanup_mode_desc,\r\nquery_capture_mode_desc, max_plans_per_query\r\nFROM sys.database_query_store_options;\r\nGO\r\n<\/pre>\n
\"Output<\/a>
Output from sys.database_query_store_options for model<\/figcaption><\/figure>\n
My output was empty.\u00a0 Huh?<\/p>\n
I tested msdb.<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [msdb] SET QUERY_STORE = ON;\r\nGO\r\nALTER DATABASE [msdb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);\r\nGO\r\nUSE [msdb];\r\nGO\r\nSELECT actual_state_desc, readonly_reason, desired_state_desc, current_storage_size_mb,\r\nmax_storage_size_mb, flush_interval_seconds, interval_length_minutes,\r\nstale_query_threshold_days, size_based_cleanup_mode_desc,\r\nquery_capture_mode_desc, max_plans_per_query\r\nFROM sys.database_query_store_options;\r\nGO\r\n<\/pre>\n
This time I had output:<\/p>\n
\"Output<\/a>
Output from sys.database_query_store_options for msdb<\/figcaption><\/figure>\n
Ok.\u00a0 So…maybe I can only use model to change default values.\u00a0 Let’s see if that works.\u00a0 I edited the Query Store settings for model:<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [model] SET QUERY_STORE (\r\nOPERATION_MODE = READ_WRITE,\r\nCLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 78),\r\nDATA_FLUSH_INTERVAL_SECONDS = 900,\r\nINTERVAL_LENGTH_MINUTES = 60,\r\nMAX_STORAGE_SIZE_MB = 2048,\r\nQUERY_CAPTURE_MODE = ALL,\r\nSIZE_BASED_CLEANUP_MODE = AUTO,\r\nMAX_PLANS_PER_QUERY = 200);\r\nGO\r\n<\/pre>\n
Then I created a new database and enabled Query Store:<\/p>\n
\r\nCREATE DATABASE [QSTest]\r\nCONTAINMENT = NONE\r\nON\u00a0 PRIMARY\r\n( NAME = N'QSTest', FILENAME = N'C:\\Databases\\QSTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )\r\nLOG ON\r\n( NAME = N'QSTest_log', FILENAME = N'C:\\Databases\\QSTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB );\r\nGO\r\nUSE [master];\r\nGO\r\nALTER DATABASE [QSTest] SET QUERY_STORE = ON;\r\nGO\r\nALTER DATABASE [QSTest] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);\r\nGO\r\n<\/pre>\n
I checked sys.database_query_store_options:<\/p>\n
\"Output<\/a>
Output from sys.database_query_store_options for QSTest user database<\/figcaption><\/figure>\n
It definitely picked up the changes I made to model.<\/p>\n
So…if I can’t see the settings for model in sys.database_query_store_options, then is Query Store really enabled?\u00a0 I ran a few queries against model, then checked the Query Store catalog views:<\/p>\n
\r\nUSE [model];\r\nGO\r\nSELECT *\r\nFROM sys.all_objects;\r\nGO\r\nSELECT *\r\nFROM sys.all_columns;\r\nGO\r\nSELECT *\r\nFROM sys.database_files;\r\nGO\r\nSELECT qst.query_text_id, qsq.query_id, qsq.object_id, qsq.count_compiles, qst.query_sql_text, qst.statement_sql_handle, qsq.query_hash\r\nFROM sys.query_store_query_text qst\r\nJOIN sys.query_store_query qsq ON qst.query_text_id = qsq.query_text_id;\r\nGO\r\n<\/pre>\n
Guess what?\u00a0 There was no data in the Query Store tables.\u00a0 So…let’s repeat the test against msdb.<\/p>\n
\r\nUSE [msdb];\r\nGO\r\nSELECT *\r\nFROM sys.all_objects;\r\nGO\r\nSELECT *\r\nFROM sys.all_columns;\r\nGO\r\nSELECT *\r\nFROM sys.database_files;\r\nGO\r\nSELECT qst.query_text_id, qsq.query_id, qsq.object_id, qsq.count_compiles, qst.query_sql_text, qst.statement_sql_handle, qsq.query_hash\r\nFROM sys.query_store_query_text qst\r\nJOIN sys.query_store_query qsq ON qst.query_text_id = qsq.query_text_id;\r\nGO\r\n<\/pre>\n
In this case, there WAS data in the Query Store tables.<\/p>\n
So, what does this mean?\u00a0 That you can only truly enable and use Query Store against one system database: msdb.<\/strong>\u00a0 It cannot be enabled for master and tempdb (it throws an error: Msg 12438, Level 16, State 1, Line 19
\nCannot perform action because Query Store cannot be enabled on system database tempdb.) and while it can be enabled for model, it’s only for the purposes of changing the default settings for Query Store for new databases on this instance<\/em> (I’ve confirmed this with the SQL Server team).\u00a0 Is that something you want to do?\u00a0 Perhaps…but I can do those on a per-database case when I’m initially enabling Query Store, or later on if so desired. I’m always wary of changing settings in model, though I do change Recovery Model to SIMPLE so I don’t end up with a ridiculously large transaction log…the default is FULL and if someone doesn’t setup transaction log backups then that log is going to grow until it consumes all space on the disk.\u00a0 I also set Auto Growth values to fixed, reasonable numbers.<\/p>\n
For each database where I enable Query Store, I’d consider the workload and then look at the settings.\u00a0 I tend to think that the default value of 100MB for MAX_STORAGE_SIZE_MB is really<\/em> low, and I would be inclined to bump up STALE_QUERY_THRESHOLD_DAYS from 30 to something a bit higher.\u00a0 I’d also probably drop \u00a0DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.\u00a0 This setting determines how often Query Store data is flushed to disk.\u00a0 If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.\u00a0 I’d also think about changing INTERVAL_LENGTH_MINUTES to a value smaller than 60 if I wanted to aggregate my query data over a smaller amount of time.\u00a0 Sometimes interesting events happen within a 60 minute time frame, and they get can lost when data is aggregated across that window.\u00a0 However, aggregating more<\/em> frequently means I’m adding processing overhead to the system – there’s a trade-off there to figure out.<\/p>\n
The take home message…you probably don’t need to enable Query Store for any system database, but if you want to change the default values that user databases pick up when you enable it, you can do that using model.<\/p>\n","protected":false},"excerpt":{"rendered":"
Last week I wrote a quick post about storage settings for Query Store.\u00a0 Today I’m going to take a moment to discuss the default settings that are applied when you initially enable Query Store.\u00a0 I’m not going go through all the settings and what they mean, you can find that information here.\u00a0 But I do […]<\/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":"\nSQL Server Query Store: Default Settings - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.\" \/>\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\/sql-server-query-store-default-settings\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Query Store: Default Settings - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-16T15:51:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/05\/qs1-1024x94.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\/sql-server-query-store-default-settings\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/\",\"name\":\"SQL Server Query Store: Default Settings - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-05-16T15:51:33+00:00\",\"dateModified\":\"2017-04-13T16:19:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Query Store: Default Settings\"}]},{\"@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":"SQL Server Query Store: Default Settings - Erin Stellato","description":"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.","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\/sql-server-query-store-default-settings\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Query Store: Default Settings - Erin Stellato","og_description":"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/","og_site_name":"Erin Stellato","article_published_time":"2016-05-16T15:51:33+00:00","article_modified_time":"2017-04-13T16:19:31+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/05\/qs1-1024x94.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\/sql-server-query-store-default-settings\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/","name":"SQL Server Query Store: Default Settings - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-05-16T15:51:33+00:00","dateModified":"2017-04-13T16:19:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The default settings for Query Store are pulled from the model database, which means they can be changed if that's appropriate for your environment.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-default-settings\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQL Server Query Store: Default Settings"}]},{"@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\/762"}],"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=762"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/762\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}