SQL Server 2016 Query Store Introduction

Problem

Among all of the new SQL Server 2016 Database Engine features Query Store stands out as a great addition to the performance tuning tools currently available out of the box. In this tip, we will look at how you can use this new feature.

Solution

The Query Store feature helps you to track query plans, runtime statistics and queries/plans history. It also helps you find regressing queries. You can quickly find new queries with multiple plans, identify un-efficient plans and force a better plan.

We will be using the latest SQL Server 2016 CTP version 2.2 in all our examples.

Enable SQL Server 2016 Query Store

Query Store could be enabled and configured using the new “Query Store” database property page:

It could also be enabled using T-SQL:

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON
GO

SQL 2016 Query Store Configuration

Once you enabled the Query Store for the database you can configure other Query Store settings (configurable settings are in bold font and highlighted):

Click on each property to see it’s description. You can find more details about each configuration option here.

The Query Store configuration can also be changed using T-SQL:

ALTER DATABASE [DEMO_1] 
SET QUERY_STORE (OPERATION_MODE = READ_ONLY, 
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), 
DATA_FLUSH_INTERVAL_SECONDS = 900, 
INTERVAL_LENGTH_MINUTES = 60, 
MAX_STORAGE_SIZE_MB = 100, 
QUERY_CAPTURE_MODE = AUTO, 
SIZE_BASED_CLEANUP_MODE = AUTO)
GO

SQL Server Query Store Property Information

Let’s review other information that is available on the “Query Store” property page.

Current Disk Usage

This section displays the Database Size and the Query Store usage on the left and the Query Store Size and usage on the right:

Note the “Purge Query Data” button on the “Query Store” page. You can remove the contents of the Query Store using this button or use one of these statements below:

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL
GO

EXEC sys.sp_query_store_flush_db

Note, that the sp_query_store_flush_db procedure still has some bugs that Microsoft is probably working on.

SQL Server Query Store System Objects and New Extended Events

There are 6 new system stored procedures and 7 catalog views related to the Query Store in SQL Server 2016 CTP 2.2 which can be found by running this query:

SELECT name, type_desc FROM sys.all_objects 
WHERE name LIKE '%query_store%' or name= 'query_context_settings'

You can find description of the stored procedures here: https://msdn.microsoft.com/en-us/library/dn818153.aspx and description of the catalog views here: https://msdn.microsoft.com/en-us/library/dn818149.aspx.

Also, there are 19 new extended events:

query_store_background_task_persist_started – Fired if the background task for Query Store data persistence started execution
query_store_background_task_persist_finished – Fired if the background task for Query Store data persistence is completed successfully
query_store_load_started – Fired when query store load is started
query_store_db_data_structs_not_released – Fired if Query Store data structures are not released when feature is turned OFF.
query_store_db_diagnostics – Periodically fired with Query Store diagnostics on database level.
query_store_db_settings_changed – Fired when Query Store settings are changed.
query_store_db_whitelisting_changed – Fired when Query Store database whitelisting state is changed.
query_store_global_mem_obj_size_kb – Periodically fired with Query Store global memory object size.
query_store_size_retention_cleanup_started – Fired when size retention policy clean-up task is started.
query_store_size_retention_cleanup_finished – Fired when size retention policy clean-up task is finished.
query_store_size_retention_cleanup_skipped – Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
query_store_size_retention_query_deleted – Fired when size based retention policy deletes a query from Query Store.
query_store_size_retention_plan_cost – Fired when eviction cost is calculated for the plan.
query_store_size_retention_query_cost – Fired when query eviction cost is calculated for the query.
query_store_generate_showplan_failure – Fired when Query Store failed to store a query plan because the showplan generation failed.
query_store_capture_policy_evaluate – Fired when the capture policy is evaluated for a query.
query_store_capture_policy_start_capture – Fired when an UNDECIDED query is transitioning to CAPTURED.
query_store_capture_policy_abort_capture – Fired when an UNDECIDED query failed to transition to CAPTURED.
query_store_schema_consistency_check_failure – Fired when the Query Store schema consistency check failed.

SQL Server Management Studio (SSMS) Query Store Features

SSMS “Query Store” container

After enabling Query Store the database will have the new “Query Store” container in SSMS:

You can right click the “Query Store” container to view available options:

Or you can expand the container and use the Query Store SSMS panes:

Top Resource Consumers

Most of the Query Store panes have similar structure and display options. Let’s review them on the “Top Resource Consumers” pane example:

The chart on the left of the pane displays total statistics based on selected metrics for vertical and horizontal axis. It also has “Metric” (1) and “Statistics” (2) drop downs.

The right chart of the pane (called “Plan Summary”) displays statistics of the selected/highlighted query (green bar on the left chart) based on the selected “Metric” (1) and “Statistic” (2). Selected “Statistic” becomes a vertical axis for the right chart.

Refresh button on the left chart will refresh both charts. The same button on the right chart will only refresh the right chart.

Note that we have selected query with multiple plans for review (“num plans” vertical axis on the left chart). The right chart displays each plan in different color.

You can mouse over (3) on a left or right chart’s object and see the detailed statistics for specific query_id or plan_id.

The details will be different depending on the metric selected:

TOP Resource Consumers - Plan Summary - Chart View

The size of bubbles on the right chart (4) depends on total number of executions.

When you click on different plans ((3) or (4)) the bottom part of the pane (5) will display the execution plan for this particular plan_id.

Note some of the titles that are dynamic. The pane’s name (6) will be “Top Resource Consumers [db_name]“. The header in our example is: “Top 25 Top Resource Consumers During the last hour for database qstore_test”. Where these parts of the title are dynamic:

(7) – “Top 25” – configurable value (we will review configuration options below)
(8) – “last hour” – configurable value
(9) – “qstore_test” – monitored database name

Let’s review the dropdown menus in the “Top Resource Consumers” pane:

(1) Metrics available:

CPU Time
Duration (Default)
Execution Count
Logical Reads
Logical Writes
Memory Consumption
Physical Reads

(2) Left Chart – Vertical Axis:

“exec count” – executions count
“num plans” – number of plans
the third, default option is dynamic and it is based on the metric (1) and statistic (3) selected. The display format is “[statistic] [metric]”. For example: “avg logical reads”.
Note, that when “Execution count” metric is selected then only 2 options are available: “exec count” and “num plans”.

(3) Statistics:

Avg (Default)
Max
Min
Std Dev
Total

(4) Left Chart – Horizontal Axis:

“query id” (Default)
“exec count”
the third option is dynamic and it is based on the metric (1) and statistic (3) selected. The display format is “[statistic] [metric]”. For example: “total logical reads”. Note, that when “Execution count” metric is selected then only 2 options are available: “exec count” and “num plans”.

(5) Right Chart (“Plan Summary”) Vertical Axis changes based on the “Statistics” selected on the left chart:

Avg (Default)
Max
Min
Std Dev

Note, that if “Total” statistics selected on the left chart the right chart’s axis is set to “Avg”. This might change in the final release.

(6) If you have smaller screen resolution some buttons will be hidden.

Let’s click “Grid” on the right chart and “Vertical View” next to the title and review the buttons available:

Changing the “Plan Summary” from chart to grid allows us to view statistics for every plan in table format.

The “Track Query” (1) button will open “Tracked Queries” pane. We will review this pane in our next tip.

The “View Query” (2) will open the new SSMS window with T-SQL script of the query:

The “Detailed Grid” button (3) on the left chart will display a list of the top queries with all statistics (more columns displayed):

The “Grid” button (4) on the left chart displays list of top queries, but the number of columns will be limited and the columns displayed will depend on the statistic and metric selected: