SQL Server Diagnostic Information Queries Detailed, Day 31

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 the highest aggregate duration queries in the current database over the past hour. Query #69 is shown in Figure 1.

   1: -- Get highest aggregate duration queries over last hour (Query 69) (High Aggregate Duration Queries)

   2: WITH AggregatedDurationLastHour

   3: AS

   4: (SELECT q.query_id, SUM(count_executions * avg_duration) AS total_duration,

   5:    COUNT (distinct p.plan_id) AS number_of_plans

   6:    FROM sys.query_store_query_text AS qt WITH (NOLOCK)

   7:    INNER JOIN sys.query_store_query AS q WITH (NOLOCK)

   8:    ON qt.query_text_id = q.query_text_id

   9:    INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)

  10:    ON q.query_id = p.query_id

  11:    INNER JOIN sys.query_store_runtime_stats AS rs WITH (NOLOCK)

  12:    ON rs.plan_id = p.plan_id

  13:    INNER JOIN sys.query_store_runtime_stats_interval AS rsi WITH (NOLOCK)

  14:    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

  15:    WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE()) 

  16:    AND rs.execution_type_desc = N'Regular'

  17:    GROUP BY q.query_id),

  18: OrderedDuration AS

  19: (SELECT query_id, total_duration, number_of_plans, 

  20:  ROW_NUMBER () OVER (ORDER BY total_duration DESC, query_id) AS RN

  21:  FROM AggregatedDurationLastHour)

  22: SELECT OBJECT_NAME(q.object_id) AS [Containing Object], qt.query_sql_text, 

  23: od.total_duration AS [Total Duration (microsecs)], 

  24: od.number_of_plans AS [Plan Count],

  25: p.is_forced_plan, p.is_parallel_plan, p.is_trivial_plan,

  26: q.query_parameterization_type_desc, p.[compatibility_level],

  27: p.last_compile_start_time, q.last_execution_time,

  28: CONVERT(xml, p.query_plan) AS query_plan_xml 

  29: FROM OrderedDuration AS od 

  30: INNER JOIN sys.query_store_query AS q WITH (NOLOCK)

  31: ON q.query_id  = od.query_id

  32: INNER JOIN sys.query_store_query_text AS qt WITH (NOLOCK)

  33: ON q.query_text_id = qt.query_text_id

  34: INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)

  35: ON q.query_id = p.query_id

  36: WHERE od.RN <= 50 

  37: ORDER BY total_duration DESC OPTION (RECOMPILE);


  39: -- New for SQL Server 2016

  40: -- Requires that QueryStore is enabled for this database

Figure 1: Query #69 High Aggregate Duration Queries

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.

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.


Query #70 is Recent Full Backups. This query retrieves information from the dbo.backupset table in the msdb system database about the most recent Full database backups for the current database. Query #70 is shown in Figure 2.

   1: -- Look at recent Full backups for the current database (Query 70) (Recent Full Backups)

   2: SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,

   3: CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],

   4: CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],

   5: CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /

   6: CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,

   7: DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],

   8: bs.backup_finish_date AS [Backup Finish Date]

   9: FROM msdb.dbo.backupset AS bs WITH (NOLOCK)

  10: WHERE bs.database_name = DB_NAME(DB_ID())

  11: AND bs.[type] = 'D' -- Change to L if you want Log backups

  12: ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);


  14: -- Are your backup sizes and times changing over time?

  15: -- Are you using backup compression?

  16: -- Have you done any backup tuning with striped backups, or changing the parameters of the backup command?

Figure 2: Query #70 Recent Full Backups

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.

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.

These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results.

SQL Server 2014 DMV Diagnostic Queries – Part 1

SQL Server 2014 DMV Diagnostic Queries – Part 2

SQL Server 2014 DMV Diagnostic Queries – Part 3

We have finally made it to the end of this series!  I’ll be putting up a recap post for the entire series, with links to each post.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.