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.
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:
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!
