Getting started with SQL Server Query Store

DBA Julian Fletcher introduces SQL Server Query Store

Query Store is a new set of diagnostic tools, available from 2016. From Monitoring performance by using the Query Store:

“The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.”

It’s disabled by default but if you enable it on a working database, you’ll find that the following DMVs will start to return information:

• sys.database_query_store_options
• sys.query_context_settings
• sys.query_store_plan
• sys.query_store_query
• sys.query_store_query_text
• sys.query_store_runtime_stats
• sys.query_store_runtime_stats_interval
• sys.query_store_wait_stats -- 2017 only

There’s also a handful of stored procedures for managing the store:

• sp_query_store_flush_db
• sp_query_store_force_plan
• sp_query_store_remove_plan
• sp_query_store_remove_query
• sp_query_store_reset_exec_stats
• sp_query_store_unforce_plan

Finally, if the store has been enabled, you’ll find a new “Query Store” node:

This makes use of the new DMVs to deliver the information in a nice graphical way: