Query Store changes in SQL Server vNext CPT1

Not only did we receive a brand new Service Pack for SQL Server 2016 this week, since yesterday we are able to play with the first Community Technology Preview (CTP) of SQL Server vNext!

Since I adore the Query Store feature which was introduced in SQL Server 2016, a large portion of my time today went to finding out what has changed so far for the Query Store in vNext CPT1. Below is a list of changes I have found so far.

Brand new “Queries with forced plans” report
Ok technically this isn’t a change in SQL Server vNext, the new “Queries with forced plans” built-in report is available when you install the new version of SQL Server Management Studio which has support for SQL Server vNext CPT 1.

The new “Queries with forced plans” built-in report can be used to monitor execution plans we forced through the Query Store.

17112016_qs_sql_vnext_01

This is a great new addition to the built-in reports and makes keeping track of your forced plans a lot easier than having to query a DMV. The report also directly shows you the performance of forced execution plans in the “Plan summary” graph so you can easily identify performance related issues.

More runtime statistics captured
15 new additional query runtime statistics are recorded in the sys.dm_query_store_runtime_stats DMV including TempDB space usage! The full list of new metrics collected:

  • avg_num_physical_io_reads
  • last_num_physical_io_reads
  • min_num_physical_io_reads
  • max_num_physical_io_reads
  • stdev_num_physical_io_reads
  • avg_log_bytes_used
  • last_log_bytes_used
  • min_log_bytes_used
  • max_log_bytes_used
  • stdev_log_bytes_used
  • avg_tempdb_space_used
  • last_tempdb_space_used
  • min_tempdb_space_used
  • max_tempdb_space_used
  • stdev_tempdb_space_used

New Query Store Stored Procedure
One new Query Store related Stored Procedure made its way in SQL Server vNext: sp_query_store_consistency_check. If the name gives any hints about its function it suggests it has something to do with a consistency check. Interestingly enough you can only execute it against a database that has the Query Store feature disabled:

17112016_qs_sql_vnext_02

Nothing about this new Stored Procedure is documented yet so its uses remain a mystery a bit longer.

17 additional Query Store related Extended Events
Quite a few new Extended Events have been added in vNext that can trigger on specific Query Store behavior:

query_store_task_submitted Fired when a task for Query Store is submitted for execution
query_store_task_started Fired when a task for Query Store starts execution
query_store_task_finished Fired when a task for Query Store finishes execution
query_store_disk_size_over_limit Fired when Query Store disk size grows over allowed limit
query_store_disk_size_below_limit Fired when Query Store disk size drops below allowed limit, enabling Query Store to go back to read-write state
query_store_database_out_of_disk_space Fired when Query Store hits an error because database is out of disk space
query_store_stmt_hash_map_memory_below_read_write_target Fired when Query Store statement hash map memory usage goes below target for turning Query Store back to read-write state
query_store_db_settings_and_state Periodically fired with Query Store settings on database level.
query_store_db_cleared Fired when Query Store is cleared for a database.
query_store_resource_total_over_instance_limit Fired when Query Store sum of sizes for specified resource for all databases on instance is over the instance limit for that resource
query_store_resource_total_below_instance_target Fired when Query Store sum of sizes for specified resource for all databases on instance goes below target for turning Query Store back to read-write state
query_store_aprc_check_completed Fired when APRC completes plan regression check
query_store_async_queue_diagnostics Fired periodically with diagnostics about Query Store async queue
query_store_spinlock_stats Fired periodically with Query Store spinlock statistics
query_store_matching_query_text_found Fired if query text referenced by two queries is found during query removal
query_store_query_text_removal_skipped Fired if query text is not deleted when query is removed
query_store_resource_type Resource type on which sizes are summed when determining the instance total

 

I nominate the query_store_db_settings_and_state for most vague Extended Event description of the year :-).

One new Wait Type
And finally, one new additional Wait Type is added QDS_HOST_INIT.

As you can see lots of new Query Store stuff is making its way in SQL Server vNext. Keep in mind though this is still CTP 1 which means a lot can change before the final release!

Write a Reply or Comment

Your email address will not be published.