{"id":2112,"date":"2015-11-16T00:00:00","date_gmt":"2015-11-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-sql-server-2016-query-store-overview-and-architecture\/"},"modified":"2021-08-24T13:39:35","modified_gmt":"2021-08-24T13:39:35","slug":"the-sql-server-2016-query-store-overview-and-architecture","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-sql-server-2016-query-store-overview-and-architecture\/","title":{"rendered":"The SQL Server 2016 Query Store: Overview and Architecture"},"content":{"rendered":"<div id=\"pretty\">\n<h1>Structure of the article series<\/h1>\n<p>Before we get started digging into the internals and  components making up the Query Store, I want to describe how this article series  is structured. The following aspects of the Query Store will be covered by the  article series.<\/p>\n<ul>\n<li>The SQL Server 2016 Query Store &#8211; Part 1: Overview and Architecture<br \/><em>This is the part of the series you are reading right now. After a short overview of the Query Store features and use-cases, we will be taking a look at the Query Store Architecture and end the article describing the various Query Store configuration options and how you can enable it.<\/em><\/li>\n<li>The SQL Server 2016 Query Store &#8211; Part 2: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-built-in-reporting\/\">Built-In Reporting<\/a><br \/><em>In this article I will describe the build-in Query Store reports, what they show and the options we have to modify their results.<\/em><\/li>\n<li>The SQL Server 2016 Query Store &#8211; Part 3: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-accessing-query-store-information-using-dmvs\/\">Accessing Query Store Information Using DMVs<\/a><br \/><em>This article dives a bit deeper into the DMVs that are related to the Query Store. What information is available in the different DMVs and how can we use that information to write custom reporting or queries.<\/em><\/li>\n<li>The SQL Server 2016 Query Store &#8211; Part 4: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-forcing-execution-plans-using-the-query-store\/\">Forcing Execution Plans Using the Query Store<\/a><br \/><em>In part four we will use the Query Store to force specific Execution Plans to be used when a query executes. We also spend some time looking at plan regression, why it is bad and the options we have to avoid it.<\/em><\/li>\n<li>The SQL Server 2016 Query Store &#8211; Part 5: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-analyzing-query-store-performance\/\">Analyzing Query Store Performance<\/a><br \/><em>Enabling the Query Store on your database can potentially have an impact on the performance of your database. In part six we will describe the methods we can use to analyze the performance of the Query Store and the impact it has on your system.<\/em><\/li>\n<\/ul>\n<h1>Introduction<\/h1>\n<p>Once the first Community Technology Preview (CTP) of SQL  Server 2016 was released, we were finally able to play around with, what is, in  my opinion, one of the most anticipated new features: The Query Store.<\/p>\n<p>The Query Store is a feature that can help us to  troubleshoot query performance, as well as capturing query execution  information, such as the number of executions or the average duration of a  query. The Query Store also captures every Execution Plan that has been  generated for a specific query and you can then decide what Execution Plan you  want SQL Server to use for that specific query.<\/p>\n<p>Some common uses for the Query Store are described by  Microsoft as follows:<\/p>\n<ul>\n<li>Quickly find and  \tfix a plan performance regression by forcing the previous query plan. Fix  \tqueries that have recently regressed in performance due to execution plan  \tchanges.<\/li>\n<li>Determine the  \tnumber of times a query was executed in a given time window, assisting a DBA  \tin troubleshooting performance resource problems.<\/li>\n<li>Identify  \ttop\u00a0n\u00a0queries (by execution time, memory consumption, etc.) in the  \tpast\u00a0x\u00a0hours.<\/li>\n<li>Audit the history  \tof query plans for a given query.<\/li>\n<li>Analyze the  \tresource (CPU, I\/O, and Memory) usage patterns for a particular database.<\/li>\n<\/ul>\n<p>Even though it is already possible for us to perform many  of the same actions as the Query Store in earlier SQL Server versions, capturing  specific query runtime performance counters or forcing Execution Plans is a  complex, specialized, task. The Query Store makes these actions far easier to  use and available to everyone without needing to rewrite any queries.  <\/p>\n<p>As a quick overview, these are some of the advantages of  the Query Store:<\/p>\n<ul>\n<li>The Query Store is integrated directly in SQL Server, this  \tmeans that it can capture query runtime information directly at the source  \tlevel;<\/li>\n<li>Without the Query  \tStore we frequently depend on the Plan Cache, where Execution Plans are  \tstored in-memory, when we need to analyze query Execution Plans. However, if  \ta SQL Server restart occurs then all the Execution Plans will be removed  \tfrom the Plan Cache and we are forced to start over again. The Query Store,  \tinstead, stores the information it captures on disk, which means that a SQL  \tServer restart doesn&#8217;t affect any information that has been captured in the  \tQuery Store;<\/li>\n<li>The Query Store  \tcan be enabled on an individual database;<\/li>\n<li>A large amount of  \tinformation is directly available through built-in Query Store reports;<\/li>\n<\/ul>\n<p>All the  \tinformation inside the Query Store can also be accessed through Dynamic  \tManagement Views (DMVs) which opens the door for custom reporting and\/or  \talerting.<\/p>\n<p>We will take a thorough look at some of the various  advantages I mentioned above, and more, further on in this series of articles.<\/p>\n<p>Another bonus I want to mention before we continue, is that  the Query Store will be available in all editions of SQL Server! So no  &#8220;Enterprise-only&#8221; this time!<\/p>\n<p>Finally, it is important to understand that the Query Store  is a complete new feature inside SQL Server 2016, which is only available as a  preview version at the time of writing these articles. This means that  information inside the articles can change when SQL Server 2016 is officially  released. Personally I don&#8217;t think this is very likely at the moment, seeing how  the Query Store functionality remained stable during the various CTP versions,  but I&#8217;m obliged to give a warning.<\/p>\n<h1>Query Store Architecture<\/h1>\n<p>So how does the Query Store work underneath the hood? The  following figure shows the basic architecture of the Query Store.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2308-img123.jpg\" height=\"198\" width=\"427\" alt=\"2308-img123.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1 Query Store architecture (Conor Cunningham, used  with permission)<\/p>\n<p>What you can see in the figure above is that the Query  Store consists of actually two &#8220;stores&#8221;, the <b>Plan Store<\/b> and the <b>Runtime Stats<\/b> <b>Store<\/b>. Both these stores are  in-memory objects that hold query information.<\/p>\n<p>When a query gets submitted against a database that has the  Query Store enabled, the compiled query Execution Plan is written to the Query  Store Plan Store and the runtime information of the query is recorded in the  Runtime Stats Store. As said before, this information is stored initially  in-memory. After a specific interval the information inside the in-memory stores  is asynchronously written to disk to harden the information. The Query Store  writes the Execution Plans and runtime statistics into the primary filegroup of  the database that you queried against. <\/p>\n<p>By hardening the Execution Plans and runtime statistics to  disk, the information inside the Query Store is not lost whenever a SQL Server  restart occurs. This is more valuable than analyzing the Execution Plans inside  the Plan Cache, since the Plan Cache will be empty again whenever a SQL Server  restart occurs. The same goes for the <b>  sys.dm_exec_query_stats<\/b> DMV, which is frequently used to analyze query runtime information. This  DMV will also be reset after a SQL Server restart. The interval at which the  Query Store flushes data to disk can be configured but, flushing more often  comes at a performance cost. By default the flush to disk interval is set to 15  minutes, this will result in an average performance overhead of 3-5% when you  enabled the Query Store. The lower the interval, the higher the impact on  performance. New Execution Plans that are first seen by the Query Store will be  flushed to disk more aggressively than Execution Plans that already exist inside  the Query Store. When enabling the Query Store or the first time, or after  purging it, it is common to see flushes occur more often since more new  Execution Plans are encountered.<\/p>\n<p>So how does the Query Store access data from disk and  in-memory simultaneously when we query either the Query Store reports or access  the DMVs? To make this possible with the least amount of performance impact a  new Table Defined Function (TDF) was developed that grabs data still in-memory  while simultaneously grabbing the data that was already persisted on disk.  Figure 2 below shows the Execution Plan of a query against one of the new Query  Store DMVs. In this figure you can see the TDF (<b>QUERY_STORE_RUNTIME_STATS_IN_MEM<\/b>) that grabs the in-memory data and  a Clustered Index Scan on <b> plan_persist_runtime_stats<\/b> that collects the data from disk.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"195\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2308-clip_image004.jpg\" width=\"624\" alt=\"2308-clip_image004.jpg\" \/><\/p>\n<p class=\"caption\">Figure 2 Gathering Query Store data from memory and disk  <\/p>\n<p>Now that we have looked at bits of the architecture of the  Query Store, let&#8217;s get our hands dirty and take look at the configuration  options available to us for the Query Store.<\/p>\n<h1>Examining Query Store Options<\/h1>\n<p>Before we can dive into the details about all the different  knobs we can turn for the Query Store, we will first have to enable it on a  database. For this article series I downloaded the AdventureWorks2014 database  from <a href=\"http:\/\/msftdbprodsamples.codeplex.com\/\"> http:\/\/msftdbprodsamples.codeplex.com\/<\/a> and restored it on my SQL Server 2016  Instance.<\/p>\n<p>There are two methods we can use to enable the Query Store  on a database, using the GUI inside SQL Server Management Studio (SSMS), or by  using T-SQL.<\/p>\n<p>If we want to use the GUI to enable the Query Store we only  need to go to the &#8220;<i>Query Store<\/i>&#8221;  options inside the database properties as shown in Figure 3 below.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"489\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2308-clip_image006.jpg\" width=\"577\" alt=\"2308-clip_image006.jpg\" \/><\/p>\n<p class=\"caption\">Figure 3 Query Store properties<\/p>\n<p>By changing the &#8220;<i>Operation  Mode (Requested)&#8221;<\/i> option from <b>Off<\/b>  to <b>Read Write<\/b> we can enable the Query  Store and allow it to capture query Execution Plans and runtime information.<\/p>\n<p>Enabling the Query Store using T-SQL is just as easy. The  query below will enable the Query Store for the AdventureWorks database I use on  my demo machine.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">ALTER DATABASE AdventureWorks2012 SET  QUERY_STORE = ON;<\/pre>\n<p> Now that we have the  Query Store enabled, let&#8217;s take a look at some of the options we have to  configure its behavior.<\/p>\n<p>The first option &#8220;<i>Operation  Mode (Actual)&#8221;<\/i> cannot be changed since it only indicates the current mode of  the Query Store. The value can be either <b> Off, Read Write <\/b>or <b>Read<\/b>. When the  Query Store has a mode of <b>Read<\/b> no new  Execution Plans or query runtime statistics will be collected.<\/p>\n<p>The options <i>&#8220;Operation Mode (Requested)&#8221;<\/i> can also  be set to the same values as the previous option. Changing this option like we  did when we enabled the Query Store directly changed the operation mode of the  Query Store.<\/p>\n<p>The &#8220;<i>Data  Flush Interval (Minutes)&#8221; <\/i>allows you to set the interval how quickly the  collected Execution Plans and query runtime statistics are being flushed from  memory to disk. By default this option will be set to <b>15 minutes<\/b>. Changing the interval to  a lower value means Query Store information inside the memory of your SQL Server  Instances will be written to disk faster, this can have a negative impact on the  performance of your Instance. Setting the value to a higher interval means  memory flushes occur less, but it also means more Query Store information that  resides in memory will be lost when a SQL Server restart occurs. In most cases  the default value of 15 minutes will suffice. However, if you experience  performance degradation by the frequent flushing of the Query Store memory to  disk you can choose to increase the interval (we will dig deeper into Query  Store performance at a later point in the article series).<\/p>\n<p><i> &#8220;Statistics Collection Interval&#8221;<\/i>  allows you to configure yet another interval. This one allows you to configure  the level of granularity of the collected query runtime statistics. By default  it is set to <b>1 hour<\/b>. Basically this means that it set the aggregation interval of  query runtime statistics inside the Query Store. The lower the value of this  option the finer the granularity of the runtime statistics becomes, but this  also means that more intervals occur and thus more space is required to store  the query runtime statistics on disk.<\/p>\n<p>The &#8220;<i>Max  Size (MB)&#8221;<\/i> option allows you to configure a maximum size for the Query  Store. By default this value is set to <b> 100MB<\/b>. One important thing to remember is that data inside the Query Store  is stored inside the Primary Filegroup of the database that has the Query Store  enabled. Changing the option to a higher value will allow you to store more  Query Store data but it also means your database will grow larger. Whenever the  Query Store hits the maximum size configured by this option it&#8217;s Operation Mode  will automatically switch to Read-only and no new data will be collected.<\/p>\n<p>The <i>&#8220;Query Store Capture Mode&#8221;<\/i> option  makes it possible to filter out queries with very low execution times or  execution counts. By default the option is set to <b>All<\/b> which means every query executed  against the database will be recorded in the Query Store. By setting the option  to <b>Auto<\/b> the Query Store will filter  out insignificant queries. What queries are filtered is determined by an  internal process and we do not have any influence on it. The final value for  this option is <b>None<\/b> which means no  new Execution Plans or query runtime statistics are captured.<\/p>\n<p><i> &#8220;Size Based Cleanup Mode&#8221;<\/i>  can be configured to automatically let the Query Store clean up data when it  reaches 90% of the maximum Query Store size you configured in the <i>&#8220;Max Size (MB)&#8221;<\/i> option. The cleanup  will remove the oldest and least expensive query data and stops the cleanup at  80% of the <i>&#8220;Max Size (MB)&#8221;<\/i> value. By  default this option is set to <b>Off<\/b>. Setting it to <b>Auto<\/b>  will enable the automatic cleanup.<\/p>\n<p>The final option, <i>&#8220;Stale Query Threshold (Days)&#8221; <\/i>sets  how long query data is retained inside the Query Store. By default the setting  is configured to <b>367<\/b> days. The value  of this option should reflect your historic analysis needs. If you don&#8217;t need to  store query information for 376 days but for a shorter period of time it is  advices to lower this option so you are not wasting space inside the Query Store  for data you will never use.<\/p>\n<p>Besides setting all  the options through the Query Store GUI, you can also configure all of these  options through T-SQL. The table below shows all of the Query Store options we  discussed above and shows the corresponding T-SQL command. The table also shows  one additional option which you cannot configure through the GUI (yet),  MAX_PLANS_PER_QUERY. By default this option is set to 200 which means the Query  Store will store a maximum of 200 Execution Plans per query.<\/p>\n<table class=\"MsoTable15Grid1Light\">\n<thead>\n<tr>\n<td valign=\"top\">\n<p><b>GUI Option<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>T-SQL Option<\/b><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p> \t\tOperation Mode (Requested)<\/p>\n<\/td>\n<td valign=\"top\">\n<p>OPERATION_MODE<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tData Flush Interval (Minutes)<\/p>\n<\/td>\n<td valign=\"top\">\n<p>DATA_FLUSH_INTERVAL_SECONDS<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tStatistics Collection Interval <\/p>\n<\/td>\n<td valign=\"top\">\n<p>INTERVAL_LENGHT_MINUTES<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tMax Size (MB)<\/p>\n<\/td>\n<td valign=\"top\">\n<p>MAX_STORAGE_SIZE_MB<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tQuery Store Capture Mode<\/p>\n<\/td>\n<td valign=\"top\">\n<p>QUERY_CAPTURE_MODE<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tSize Based Cleanup Mode<\/p>\n<\/td>\n<td valign=\"top\">\n<p>SIZE_BASED_CLEANUP_MODE<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p> \t\tStale Query Threshold (Days)<\/p>\n<\/td>\n<td valign=\"top\">\n<p>CLEANUP_POLICY  \t\t(STALE_QUERY_THRESHOLD_DAYS)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>\u00a0<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>MAX_PLANS_PER_QUERY<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Changing Query Store  options through T-SQL can be achieved using the following command:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">ALTER DATABASE [db_name]\r\nSET QUERY_STORE\r\n\u00a0 (\r\n\u00a0 [Query Store options]\r\n\u00a0 );<\/pre>\n<p>For example, to  change the maximum storage size to 250MB of the Query Store on the  AdventureWorks database you can use the following T-SQL command:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">ALTER DATABASE AdventureWorks\r\nSET QUERY_STORE\r\n\u00a0 (\r\n\u00a0 MAX_STORAGE_SIZE_MB = 250\r\n\u00a0 );<\/pre>\n<p>We can also issue  multiple configuration changes at once, in the example below we change the Query  Store size of the AdventureWorks to 250MB, change the Size Based Cleanup Mode to <b>Auto<\/b> and retain 30 days of query  data.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">ALTER DATABASE AdventureWorks\r\nSET QUERY_STORE\r\n\u00a0 (\r\n\u00a0 MAX_STORAGE_SIZE_MB = 250,\r\n\u00a0 SIZE_BASED_CLEANUP_MODE = AUTO,\r\n\u00a0 CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)\r\n\u00a0 );<\/pre>\n<p>One final option I  would like to highlight is the ability to empty, or purge, all the data inside  the Query Store. You can either do this through the Query Store properties  window inside SSMS by clicking the <i>&#8220;Purge  Query Data&#8221;<\/i> button in the bottom right, or by using the T-SQL command below.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">ALTER DATABASE [db_name] SET QUERY_STORE CLEAR;<\/pre>\n<p>Now that we know how  we can enable and configure the Query Store through either the GUI or T-SQL, we  will take a look at some of the build-in Query Store reporting available through  SQL Server Management Studio in the next article: <strong>Introducing the SQL  Server 2016 Query Store &#035;2 &#8211; Query Store Build-in Reporting.<\/strong><\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server&#8217;s Query Store, introduced in SQL Server 2016, helps to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I\/O and execution time, and retaining every Execution Plan for analysis. Much of this information is available through queries.<br \/>\nIt looks set to be the most significant enhancement of SQL Server 2016.&hellip;<\/p>\n","protected":false},"author":221970,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4150,4151],"coauthors":[],"class_list":["post-2112","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221970"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2112"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2112\/revisions"}],"predecessor-version":[{"id":92209,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2112\/revisions\/92209"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2112"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}