{"id":2148,"date":"2016-01-26T00:00:00","date_gmt":"2016-01-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-sql-server-2016-query-store-accessing-query-store-information-using-dmvs\/"},"modified":"2021-08-24T13:39:33","modified_gmt":"2021-08-24T13:39:33","slug":"the-sql-server-2016-query-store-accessing-query-store-information-using-dmvs","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-accessing-query-store-information-using-dmvs\/","title":{"rendered":"The SQL Server 2016 Query Store: Accessing Query Store Information Using DMVs"},"content":{"rendered":"<div class=\"article-content\">\n<ul>\n<li>The SQL Server 2016 Query Store &#8211; Part 1: <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-overview-and-architecture\/\">Overview and Architecture<\/a><\/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><\/li>\n<li>The SQL Server 2016 Query Store &#8211; Part 3: Accessing Query Store Information Using DMVs<\/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><\/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><\/li>\n<\/ul>\n<p class=\"start\">In the      <a href=\"https:\/\/www.simple-talk.com\/author\/enrico-van-de-laar\/\">previous articles in this series about the Query Store<\/a>, we discussed the architecture and options of the Query Store, and then  took a look at the various built-in reports. In this article we are taking a look at the new DMVs that were introduced to support the Query Store, what they contain, and how we can access them with custom queries. <\/p>\n<h2>Newly added Query Store DMVs<\/h2>\n<p>Let&#8217;s start this article with a list of the new DMVs that were introduced in SQL Server 2016 to support  the Query Store. In this article we will discuss every new DMV, and how they are related<\/p>\n<ul>\n<li>Sys.database_query_store_options<\/li>\n<li>Sys.query_store_query<\/li>\n<li>Sys.query_store_query_text<\/li>\n<li>Sys.query_context_settings<\/li>\n<li>Sys.query_store_plan<\/li>\n<li>Sys.query_store_runtime_stats<\/li>\n<li>Sys.query_store_runtime_stats_interval<\/li>\n<\/ul>\n<p>As you can see from this list, the DMVs are easily identified by  the      <strong>query<\/strong>     <strong>_<\/strong>     <strong>store<\/strong> string in the name of the DMV, with the exception of the      <strong>sys.query_context_settings<\/strong> DMV. <\/p>\n<p>With the exception of the      <strong>sys.database_query_store_options<\/strong>     <strong><\/strong>(this DMV only holds Query Store configuration), all of the DMVs have a relation between them which is shown in Figure 15 below. <\/p>\n<p>     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2345-1-61d929b1-2223-4198-aebe-1aeda0ff434c.png\" alt=\"2345-1-61d929b1-2223-4198-aebe-1aeda0ff4\" \/><\/p>\n<p class=\"caption\">Figure 15 Query Store DMV relations<\/p>\n<h2>Sys.database_query_store_options<\/h2>\n<p>The      <strong>sys.database_query_store_options<\/strong> DMV does not hold any query-related information, but instead records all the configuration options and their settings of the Query Store. I won&#8217;t go into detail describing every column in the DMV, as Microsoft has already done this on MSDN      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818146.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818146.aspx<\/a>, but I would like to bring some columns to your attention: <\/p>\n<ul>\n<li>         <strong>a<\/strong><strong>ctual_state_desc<\/strong>         <strong><\/strong>This returns the current operating state of the Query Store and can be either          <strong>OFF<\/strong>,          <strong>READ_ONLY<\/strong> and          <strong>READ_WRITE<\/strong>.      <\/li>\n<li>         <strong>c<\/strong><strong>urrent_storage_size_mb<\/strong>         <strong><\/strong>Shows you the current size the Query Store is taking in megabytes (MB).     <\/li>\n<li>         <strong>m<\/strong><strong>ax_storage_size_mb<\/strong>         <strong><\/strong>The maximum size the Query Store can reach in megabytes (MB). Using this column, and the          <strong>current_storage_size_mb<\/strong> value, you can easily check how much free space is left in the Query Store storage.     <\/li>\n<\/ul>\n<p>We have discussed the various Query Store options that you are able to configure in part 1 of this article series:      <a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/the-sql-server-2016-query-store-overview-and-architecture\/\">Introducing the SQL Server 2016 Query Store #1 &#8211; Overview and Query Store Architecture<\/a> and since the column names of this DMV are pretty self-explanatory I won&#8217;t describe them further. <\/p>\n<h2>Sys.query_store_query<\/h2>\n<p>A large part of the performance metrics of a query is recorded inside the      <strong>sys.query_store_query<\/strong> DMV.  <\/p>\n<p>Before we look at the various bits of information stored inside the DMV,  I want to step back a bit and discuss SQL statements and SQL batches.<\/p>\n<p>Frequently, when we talk about executing a query, we really mean a single T-SQL command executed against the database. We call this a SQL Statement. A SQL statement is one single command that gets processed by SQL Server. For instance SELECT * FROM TableX.<\/p>\n<p>However, it is also possible to execute multiple SQL statements inside one single unit called a SQL batch. An example of a SQL batch could be:<\/p>\n<pre class=\"listing\">-- Statement 1\nSELECT TOP 100 *\nFROM Person.Address;\n-- Statement 2\nSELECT TOP 100 *\nFROM Sales.SalesOrderDetail;\n<\/pre>\n<p>It is important to know that the information inside the      <strong>sys.query_store_query<\/strong> DMV is recorded at the level of the SQL statement. As a matter of fact, the Query Store will cut up SQL batches into their SQL statements and record the performance and runtime metrics of the individual SQL statement. This is very different to the      <strong>sys.dm_exec_query_stats<\/strong> DMV where runtime and performance metrics are recorded on the SQL batch level. We would have used this DMV before the  Query Store became available. By recording the statement rather than the batch, the Query Store provides a finer granularity for trouble-shooting query performance! To show you an example of the way that batches are split into their component SQL statements,  take a look at the example below.  <\/p>\n<pre class=\"listing\">-- Execute first\n-- Empty the Query Store\nALTER DATABASE AdventureWorks SET QUERY_STORE CLEAR;\n-- Execute second\n-- Statement 1\nSELECT TOP 100 *\nFROM Person.Address;\n-- Statement 2\nSELECT TOP 100 *\nFROM Sales.SalesOrderDetail;\n-- Execute third\n-- Query sys.query_store_query\nSELECT\n  query_id,\n  qt.query_sql_text AS 'Statement Text',\n  [text] AS 'Query Batch Text'\nFROM sys.query_store_query q\nCROSS APPLY sys.dm_exec_sql_text(last_compile_batch_sql_handle)\nINNER JOIN sys.query_store_query_text qt\n  ON q.query_text_id = qt.query_text_id;\n<\/pre>\n<p>Make sure to execute them in the correct order and execute both SQL SELECT statements in one batch. Figure 16 below shows the result of the third, and final, query in the batch above.<\/p>\n<p>     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2345-1-78af4714-3274-40e2-b3b4-2b8770988266.png\" alt=\"2345-1-78af4714-3274-40e2-b3b4-2b8770988\" \/><\/p>\n<p class=\"caption\">Figure 16 Query Store SQL statements inside sys.query_store_query<\/p>\n<p>As you can see in the figure above, two new queries are recorded in the Query Store (ID 1 and 2) which were the individual statements inside the SQL batch that we executed. I joined the      <strong>sys.dm_exec_sql_text<\/strong> DMF to show you both the unique queries inside the Query Store both belonged to the same SQL batch. <\/p>\n<p>Back to the DMV! Again, the DMV is described in detail by Microsoft on MSDN      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818156.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818156.aspx<\/a> so I will only focus on those columns that deserve a bit more attention. <\/p>\n<ul>\n<li>         <strong>context_settings_id<\/strong>         <strong><\/strong>This number ties the          <strong>sys.query_store_query<\/strong> DMV to the          <strong>sys.query_context_settings<\/strong> DMV. The          <strong>sys.query_context_settings<\/strong> DMV records information about the context in which the query was executed (we&#8217;ll discuss the          <strong>sys.query_context_settings<\/strong> DMV later on in the article).     <\/li>\n<li>         <strong>object_id<\/strong>         <strong><\/strong>The          <strong>object_id<\/strong> column returns the ID of the database object if the query was part of such an object (for instance a Stored Procedure). If the query isn&#8217;t part of an object like a SP, a &#8216;0&#8217; will be returned indicating the query was ad-hoc.     <\/li>\n<li>         <strong>last_compile_batch_sql_handle<\/strong>         <strong><\/strong>Returns the query text handle of the SQL batch. We used this column in the example above to join the          <strong>sys.dm_exec_sql_text<\/strong> DMF to return the query text of the SQL batch.     <\/li>\n<\/ul>\n<p>A large part of the remaining columns of the DMV show various query compilation metrics, such as  the number of times that this query was compiled, or the average compile memory that was needed.<\/p>\n<h2>Sys.query_store_query_text<\/h2>\n<p>MSDN link of the DMV:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818159.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818159.aspx<\/a>. <\/p>\n<p>The      <strong>sys.query_store_query_text<\/strong> DMV returns the Query text of the SQL statement and can be joined to the      <strong>sys.query_store_query<\/strong> DMV by the      <strong>query_text_id<\/strong> column. Keep in mind that the text is related to the SQL statement, and not the SQL batch. <\/p>\n<h2>Sys.query_context_settings<\/h2>\n<p>MSDN link of the DMV:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818148.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818148.aspx<\/a>. <\/p>\n<p>The      <strong>sys.query_context_settings<\/strong> DMV returns the context settings under which the SQL statement is executed. We can join this DMV to the      <strong>sys.query_store_query<\/strong> DMV through the      <strong>context_settings_id<\/strong> column. <\/p>\n<p>It is very important for the Query Store to capture the context settings that were active when the SQL statement was executed because it can have an impact on the performance of the SQL statement. For instance, using a different data format for identical query statements can result in slower performance in of the statements. <\/p>\n<p>Some of the context information is easily viewable in the      <strong>sys.query_context_settings<\/strong> DMV, such as the date format (     <strong>date_format<\/strong> column), of the language under which the statement was executed (     <strong>language_id<\/strong>     <strong><\/strong>column). Other options, such as the SET options, are not directly visible inside the DMV and are represented by the      <strong>set_options<\/strong> column. This column returns a bit mask to represent a number of the SET options configured. Right now it isn&#8217;t very easy to find out what SET options were used by the statement. One method that you can use is to  join the      <strong>sys.query_query_store<\/strong> DMV to the      <strong>sys.dm_exec_query_stats<\/strong> DMV on the      <strong>last_compile_batch_sql_handle<\/strong> column to retrieve the Execution Plan handle of the SQL batch that included the SQL statement, using the query below: <\/p>\n<pre class=\"listing\">SELECT\n  qsq.query_id,\n  qsqt.query_sql_text,\n  qs.plan_handle\nFROM sys.query_store_query qsq\nINNER JOIN sys.dm_exec_query_stats qs\n  ON qsq.last_compile_batch_sql_handle = qs.sql_handle\nINNER JOIN sys.query_store_query_text qsqt\n  ON qsq.query_text_id = qsqt.query_text_id\nORDER BY qsq.query_id\n<\/pre>\n<p>We can then use the Execution Plan handle as input to the      <strong>sys.dm_exec_plan_attributes<\/strong> DMF which returns a value that contains the SET options that were used. <\/p>\n<pre class=\"listing\">SELECT * FROM sys.dm_exec_plan_attributes(0x06000500A7754106208BF6BD8300000001000000000000000000000000000000000000000000000000000000)\nWHERE attribute = 'set_options'\n<\/pre>\n<p>With the number that was returned,  you have do to some subtractions to find out what set options were used: I won&#8217;t go into the detail of how you can do this since it is outside the scope of this article, but you can find some information on MSDN here:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms189472.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/ms189472.aspx<\/a>. <\/p>\n<h2>Sys.query_store_plan<\/h2>\n<p>This DMV is described on MSDN here:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818155.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818155.aspx<\/a>. <\/p>\n<p>The      <strong>sys.query_store_plan<\/strong>     <strong><\/strong>stores information about the Execution Plans that is related to the queries that are captured inside the Query Store. It returns information such as the Engine version used, the compatibility level and whether the plan was trivial. <\/p>\n<p>The Execution Plans inside this DMV are the estimated execution plans rather than  the actual ones. The Execution Plan itself is stored in as formatted XML inside the DMV. Using a CAST AS XML we can make the Execution Plan inside the DMV &#8220;hyperlinked&#8221; so you can easily view the Execution Plan inside SQL Server Management Studio. Below is a simple example of such a query:<\/p>\n<pre class=\"listing\">SELECT\n  plan_id,\n  query_id,\n  CAST(query_plan AS XML) AS 'Execution Plan'\nFROM sys.query_store_plan;\n<\/pre>\n<p>Figure 17 shows the results of the query above, we&#8217;ll expend on this query a bit more in the section &#8220;Querying DMV information&#8221; in this article.<\/p>\n<p>     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2345-1-9d5bdf1c-9efa-4264-822e-40f557260145.png\" alt=\"2345-1-9d5bdf1c-9efa-4264-822e-40f557260\" \/><\/p>\n<p class=\"caption\">Figure 17 Execution Plans inside sys.query_store_plan<\/p>\n<h2>Sys.query_store_runtime_stats<\/h2>\n<p>This DMV is described on MSDN here:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818158.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818158.aspx<\/a>. <\/p>\n<p>Inside the      <strong>sys.query_store_runtime_stats<\/strong>     <strong>,<\/strong> many of the query performance metrics are captured and aggregated. You can join this DMV to the      <strong>sys.query_store_plan<\/strong> DMV through the      <strong>plan_id<\/strong> column. <\/p>\n<p>One row for every unique Execution Plan inside the Query Store, during a specific interval, will be returned. The performance metrics inside the DMV are aggregated for the Execution Plan based on the interval configured in the Query Store options (Statistics Collection Interval). So keep in mind that you are not viewing the performance metrics for the single execution of the query, but the aggregated runtime metrics of that query during a specific time interval. This means it is possible to see rows returned which have the same      <strong>plan_id<\/strong> (and thus the same Execution Plan) as long as they were executed in different interval segments. To show this relationship, I included Figure 18 that shows the different intervals (from the      <strong>sys.query_store_runtime_stats_interval<\/strong> DMV) and the runtime statistics (from the      <strong>sys.query_store_runtime_stats<\/strong> DMV) <\/p>\n<p>     <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2345-1-72d134d1-873b-4c84-b1c3-26494b4f8bb7.png\" alt=\"2345-1-72d134d1-873b-4c84-b1c3-26494b4f8\" \/><\/p>\n<p class=\"caption\">Figure 18 Interval relation with the performance metrics<\/p>\n<p>The performance metric columns are all pretty self-explanatory so I won&#8217;t discuss them in detail here.<\/p>\n<h2>Sys.query_store_runtime_stats_interval<\/h2>\n<p>MSDN link of the DMV:      <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dn818147.aspx\">https:\/\/msdn.microsoft.com\/en-us\/library\/dn818147.aspx<\/a>. <\/p>\n<p>The last DMV we will discuss is the      <strong>sys.query_store_runtime_stats_interval<\/strong> DMV. This DMV records the start and end time of each interval based on the Statistics Collection Interval setting of the Query Store. It uses this information to aggregate the performance metrics inside the      <strong>sys.query_store_runtime_stats<\/strong> DMV. You can join the      <strong>sys.query_store_runtime_stats<\/strong> DMV to the      <strong>sys.query_store_runtime_stats_interval<\/strong> DMV using the      <strong>runtime_stats_interval_id<\/strong>     <strong><\/strong>column. <\/p>\n<h2>Querying DMV information<\/h2>\n<p>So far we&#8217;ve discussed the Query Store related DMVs and I&#8217;ve shown some query example how you can grab some information out of them. Let&#8217;s expand those examples a bit more and show you two useful queries you can use to grab some performance information out of the DMVs. Keep in mind most of the information returned is also available through the built-in reports inside the Query Store.<\/p>\n<p>     <strong>Top 10 most expensive queries based on average runtime<\/strong> <\/p>\n<pre class=\"listing\">SELECT TOP 10\n  qt.query_sql_text,\n  CAST(query_plan AS XML) AS 'Execution Plan',\n  rs.avg_duration\nFROM sys.query_store_plan qp\nINNER JOIN sys.query_store_query q\n  ON qp.query_id = q.query_id\nINNER JOIN sys.query_store_query_text qt\n  ON q.query_text_id = qt.query_text_id\nINNER JOIN sys.query_store_runtime_stats rs\n  ON qp.plan_id = rs.plan_id\nORDER BY rs.avg_duration DESC;\n<\/pre>\n<p>     <strong>Query parallel plans<\/strong> <\/p>\n<pre class=\"listing\">SELECT\n  qt.query_sql_text,\n  CAST(query_plan AS XML) AS 'Execution Plan',\n  rs.avg_duration\nFROM sys.query_store_plan qp\nINNER JOIN sys.query_store_query q\n  ON qp.query_id = q.query_id\nINNER JOIN sys.query_store_query_text qt\n  ON q.query_text_id = qt.query_text_id\nINNER JOIN sys.query_store_runtime_stats rs\n  ON qp.plan_id = rs.plan_id\nWHERE rs.last_dop &gt; 1;\n<\/pre>\n<p>As you can imagine, the queries above can easily be modified in order to return the information that you are interested in. The      <strong>sys.dm_query_store_runtime_stats<\/strong> DMV contains a wealth of different performance metrics, including IO, memory and CPU usage. <\/p>\n<h2>Summary<\/h2>\n<p>In this article we took a look at the various, new, Query Store related DMVs. We took a look what information they hold, how they are related and shown some examples how you can retrieve performance information out of them.<\/p>\n<p>This concludes the third article in the Query Store article series. In the fourth article in the series      <strong> The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store<\/strong>, we will take a look at how we can force specific Execution Plans through the Query Store. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>The SQL Server 2016 Query Store provides several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. In using the Query Store to ensure that performance is as good as possible, it isn&#8217;t long before it becomes important to be familiar with the DMVs that are associated with the query store, and using them in custom queries.&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-2148","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\/2148","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=2148"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2148\/revisions"}],"predecessor-version":[{"id":92205,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2148\/revisions\/92205"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2148"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}