{"id":1059,"date":"2016-01-31T12:29:17","date_gmt":"2016-01-31T20:29:17","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1059"},"modified":"2017-04-13T12:28:54","modified_gmt":"2017-04-13T19:28:54","slug":"sql-server-diagnostic-information-queries-detailed-day-31","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 31"},"content":{"rendered":"

For Day 31 of this series, we start out with Query #69<\/strong>, which is High Aggregate Duration Queries. This query retrieves information from the sys.query_store_query_text<\/a> query store catalog view, the sys.query_store_query<\/a> query store catalog view, the sys.query_store_plan<\/a> query store catalog view, the sys.query_store_runtime_stats<\/a> query store catalog view, and the sys.query_store_runtime_stats_interval<\/a> query store catalog view about the highest aggregate duration queries in the current database over the past hour. Query #69 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get highest aggregate duration queries over last hour (Query 69) (High Aggregate Duration Queries)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> WITH<\/span> AggregatedDurationLastHour<\/pre>\n
<\/p>\n
   3:<\/span> AS<\/span><\/pre>\n
<\/p>\n
   4:<\/span> (SELECT<\/span> q.query_id, SUM<\/span>(count_executions * avg_duration) AS<\/span> total_duration,<\/pre>\n
<\/p>\n
   5:<\/span>    COUNT<\/span> (distinct<\/span> p.plan_id) AS<\/span> number_of_plans<\/pre>\n
<\/p>\n
   6:<\/span>    FROM<\/span> sys.query_store_query_text AS<\/span> qt WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span>    INNER<\/span> JOIN<\/span> sys.query_store_query AS<\/span> q WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span>    ON<\/span> qt.query_text_id = q.query_text_id<\/pre>\n
<\/p>\n
   9:<\/span>    INNER<\/span> JOIN<\/span> sys.query_store_plan AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span>    ON<\/span> q.query_id = p.query_id<\/pre>\n
<\/p>\n
  11:<\/span>    INNER<\/span> JOIN<\/span> sys.query_store_runtime_stats AS<\/span> rs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  12:<\/span>    ON<\/span> rs.plan_id = p.plan_id<\/pre>\n
<\/p>\n
  13:<\/span>    INNER<\/span> JOIN<\/span> sys.query_store_runtime_stats_interval AS<\/span> rsi WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  14:<\/span>    ON<\/span> rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id<\/pre>\n
<\/p>\n
  15:<\/span>    WHERE<\/span> rsi.start_time >= DATEADD(hour<\/span>, -1, GETUTCDATE()) <\/pre>\n
<\/p>\n
  16:<\/span>    AND<\/span> rs.execution_type_desc = N'Regular'<\/span><\/pre>\n
<\/p>\n
  17:<\/span>    GROUP<\/span> BY<\/span> q.query_id),<\/pre>\n
<\/p>\n
  18:<\/span> OrderedDuration AS<\/span><\/pre>\n
<\/p>\n
  19:<\/span> (SELECT<\/span> query_id, total_duration, number_of_plans, <\/pre>\n
<\/p>\n
  20:<\/span>  ROW_NUMBER () OVER<\/span> (ORDER<\/span> BY<\/span> total_duration DESC<\/span>, query_id) AS<\/span> RN<\/pre>\n
<\/p>\n
  21:<\/span>  FROM<\/span> AggregatedDurationLastHour)<\/pre>\n
<\/p>\n
  22:<\/span> SELECT<\/span> OBJECT_NAME(q.object_id) AS<\/span> [Containing Object<\/span>], qt.query_sql_text, <\/pre>\n
<\/p>\n
  23:<\/span> od.total_duration AS<\/span> [Total Duration (microsecs)], <\/pre>\n
<\/p>\n
  24:<\/span> od.number_of_plans AS<\/span> [Plan<\/span> Count<\/span>],<\/pre>\n
<\/p>\n
  25:<\/span> p.is_forced_plan, p.is_parallel_plan, p.is_trivial_plan,<\/pre>\n
<\/p>\n
  26:<\/span> q.query_parameterization_type_desc, p.[compatibility_level],<\/pre>\n
<\/p>\n
  27:<\/span> p.last_compile_start_time, q.last_execution_time,<\/pre>\n
<\/p>\n
  28:<\/span> CONVERT<\/span>(xml, p.query_plan) AS<\/span> query_plan_xml <\/pre>\n
<\/p>\n
  29:<\/span> FROM<\/span> OrderedDuration AS<\/span> od <\/pre>\n
<\/p>\n
  30:<\/span> INNER<\/span> JOIN<\/span> sys.query_store_query AS<\/span> q WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  31:<\/span> ON<\/span> q.query_id  = od.query_id<\/pre>\n
<\/p>\n
  32:<\/span> INNER<\/span> JOIN<\/span> sys.query_store_query_text AS<\/span> qt WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  33:<\/span> ON<\/span> q.query_text_id = qt.query_text_id<\/pre>\n
<\/p>\n
  34:<\/span> INNER<\/span> JOIN<\/span> sys.query_store_plan AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  35:<\/span> ON<\/span> q.query_id = p.query_id<\/pre>\n
<\/p>\n
  36:<\/span> WHERE<\/span> od.RN <= 50 <\/pre>\n
<\/p>\n
  37:<\/span> ORDER<\/span> BY<\/span> total_duration DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  38:<\/span>  <\/pre>\n
<\/p>\n
  39:<\/span> -- New for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
  40:<\/span> -- Requires that QueryStore is<\/span> enabled for<\/span> this database<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #69 High Aggregate Duration Queries<\/strong><\/p>\n
If you are using the QueryStore feature in SQL Server 2016, (meaning that you have enabled it for the current database), then you can either use the built in functionality in SSMS, or use queries like this to examine the data that it collects and exposes. Personally, I like to be able to write custom queries like this to analyze the information.<\/p>\n
This query lets you identify which queries in the current database have the highest aggregate duration over the past hour. This lets you find queries that might benefit from your query and index tuning efforts, especially ones that may show a noticeable benefit from any improvements.<\/p>\n
<\/strong> <\/p>\n
Query #70<\/strong> is Recent Full Backups. This query retrieves information from the dbo.backupset<\/a> table in the msdb system database about the most recent Full database backups for the current database. Query #70 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Look at recent Full backups for the current database (Query 70) (Recent Full Backups)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> TOP<\/span> (30) bs.machine_name, bs.server_name, bs.database_name AS<\/span> [Database<\/span> Name], bs.recovery_model,<\/pre>\n
<\/p>\n
   3:<\/span> CONVERT<\/span> (BIGINT, bs.backup_size \/ 1048576 ) AS<\/span> [Uncompressed Backup<\/span> Size<\/span> (MB)],<\/pre>\n
<\/p>\n
   4:<\/span> CONVERT<\/span> (BIGINT, bs.compressed_backup_size \/ 1048576 ) AS<\/span> [Compressed Backup<\/span> Size<\/span> (MB)],<\/pre>\n
<\/p>\n
   5:<\/span> CONVERT<\/span> (NUMERIC<\/span> (20,2), (CONVERT<\/span> (FLOAT<\/span>, bs.backup_size) \/<\/pre>\n
<\/p>\n
   6:<\/span> CONVERT<\/span> (FLOAT<\/span>, bs.compressed_backup_size))) AS<\/span> [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,<\/pre>\n
<\/p>\n
   7:<\/span> DATEDIFF (SECOND<\/span>, bs.backup_start_date, bs.backup_finish_date) AS<\/span> [Backup<\/span> Elapsed Time<\/span> (sec)],<\/pre>\n
<\/p>\n
   8:<\/span> bs.backup_finish_date AS<\/span> [Backup<\/span> Finish Date<\/span>]<\/pre>\n
<\/p>\n
   9:<\/span> FROM<\/span> msdb.dbo.backupset AS<\/span> bs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> WHERE<\/span> bs.database_name = DB_NAME(DB_ID())<\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> bs.[type] = 'D'<\/span> -- Change to L if you want Log backups<\/span><\/pre>\n
<\/p>\n
  12:<\/span> ORDER<\/span> BY<\/span> bs.backup_finish_date DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  13:<\/span>  <\/pre>\n
<\/p>\n
  14:<\/span> -- Are your backup sizes and times changing over time?<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- Are you using backup compression?<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- Have you done any<\/span> backup<\/span> tuning with<\/span> striped backups, or<\/span> changing the parameters<\/span> of<\/span> the backup<\/span> command?<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #70 Recent Full Backups<\/strong><\/p>\n
This query gives you some useful statistics and properties about your most recent Full database backups for the current database. It shows you the uncompressed size of the backup, along with the compressed size and the compression ratio, if any. It also lets you know if the backup is using checksums, whether it is a copy-only backup, and whether it is using native backup encryption, which was a new feature in SQL Server 2014. Finally, it shows you when each backup finished and how long it took to complete.<\/p>\n
Keeping an eye on the size and elapsed time for your database backups is always a good idea. As your database gets larger, you may have to make changes to how and when you do your backups or to the underlying resources at location to where they are going to make sure that they are finishing in a reliable and timely manner.<\/p>\n
These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results. <\/p>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 1<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 2<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 3<\/a><\/p>\n<\/blockquote>\n
We have finally made it to the end of this series!  I\u2019ll be putting up a recap post for the entire series, with links to each post.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 31 of this series, we start out with Query #69, which is High Aggregate Duration Queries. This query retrieves information from the sys.query_store_query_text query store catalog view, the sys.query_store_query query store catalog view, the sys.query_store_plan query store catalog view, the sys.query_store_runtime_stats query store catalog view, and the sys.query_store_runtime_stats_interval query store catalog view about […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1059","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry<\/title>\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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 31 of this series, we start out with Query #69, which is High Aggregate Duration Queries. This query retrieves information from the sys.query_store_query_text query store catalog view, the sys.query_store_query query store catalog view, the sys.query_store_plan query store catalog view, the sys.query_store_runtime_stats query store catalog view, and the sys.query_store_runtime_stats_interval query store catalog view about […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-31T20:29:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:54+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-31T20:29:17+00:00\",\"dateModified\":\"2017-04-13T19:28:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 31\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry","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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry","og_description":"For Day 31 of this series, we start out with Query #69, which is High Aggregate Duration Queries. This query retrieves information from the sys.query_store_query_text query store catalog view, the sys.query_store_query query store catalog view, the sys.query_store_plan query store catalog view, the sys.query_store_runtime_stats query store catalog view, and the sys.query_store_runtime_stats_interval query store catalog view about […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-31T20:29:17+00:00","article_modified_time":"2017-04-13T19:28:54+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 31 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-31T20:29:17+00:00","dateModified":"2017-04-13T19:28:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-31\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 31"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1059"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1059\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}