How to setup Query Store in SQL Server 2016

        MSDN's definition for Query Store is Query Store 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. It sounds great so let's look at it how it works and why should you know about this features.

      First of all, Query Store is enable per database and it will give your only for the enabled database not the whole server. It is pretty easy to enable the Query Store, since it watches the running queries constantly you need to have some kind of control for how much data needs to be stored in the DMV tables or how long it should watch the queries to create statistics. Query Store stores its data in DMV tables. According to Microsoft, Query Store could generate a performance overhead up to 1.5 - 2% for the most intensive workloads.

     Let's look at Query Store's structure. Query Store has two stores, Each store has bunch of DMVs that represents them as a store.

Plan Store

    It has all information about query compilation. Plan Store uses the following DMV tables.

• Sys.query_store_query_text
• It contains the query texts executed against the database.
• Sys.query_store_plan
• It contains estimated plan for the queries with the compile time statistics
•  Sys.query_context_settings
• It contains unique combinations of plan affecting settings under which queries are executed.
•  Sys.query_store_query
• It contains query entries that are tracked and forces separately in the Query Store.

Runtime Stats Store

     It has statistics represent query execution data. This store is the most frequently updated one. Data is coming form the following DMV Tables.

• Sys.query_store_runtime_stats_interval
•   It contains the aggregated statics on time windows (interval) for every executed plan.
•  Sys.query_store_runtime_stats
•   It contains aggregated runtime statistics for executed plans.

    Let's enable the query store first. As I said before, it is in database level. So right click on one of your databases and select Properties. Query Store should be the last one in the right side. Change the Operation mode to Read and Write and click OK to enable the query store.

 

    There are bunch of options here as you can see, here is their definitions.

 

    Data Flush Interval : Be careful with this one because its value play a role in the performance impact of the Query Store. It determines the frequency at which data written to the query store is persisted to disk. Default value is 15 minutes (900 seconds)

 

    Statistics Collection Interval : To optimize for space usage, the runtime execution statistics in the Runtime Stats store are aggregated over a fixed time window. This option controls that fixed time. Default value is 1 hour (60 minutes)

 

   Max Size: This controls the maximum size of the query store. If the size of the query store hits to the max size, query store will stop writing to DMV tables and changes its state from read-write to read only.

   Query Store Capture Mode: Designates if the Query Store captures all queries or relevant queries based on execution count and resource consumption, or stops adding new queries and just track current queries. Options are ALL (capture all queries), AUTO (ignore infrequent/insignificant queries), NONE (stop capturing new queries)

 

  Size Based Cleanup Mode : Controls whether the cleanup process will be automatically activated when total amount of data gets close to max size.

 

  Stale Query Threshold : Number of days to retain data in the query store. Default: 30 days.

 

---

  Now, right click on the database and click Refresh to see the Query Store reports.

 

    You should see four reports in SQL Server 2016 when you click on the Query Store folder, Here is their definition and purposes.

 

  Regressed Queries : Identify all queries which execution metrics have been degraded in the period of time of interest.

 

 

 Overall Resource Consumption : Overall Resource consumption during the last month in 4 charts. (Duration / Execution Count / CPU time / Logical Reads)

 

 

 Tracked Queries : You can monitor a specific query with this report. You need to know its query ID to that.

 

 

 Top Resource Consuming Queries : Queries that are consuming most of the resources.