Qery Store


SQL Server Query Store

What is Query Store?

Workload data recorder for your database

Automatically captures a history of queries, plans and their statistics.
Data stored in system tables.
All 2016 editions even in express..
Lightweight performance impact of 3-5%.
It is enabled at database level
Query store works on even in azure
default it will store 30 days history.
Capture data only from DML statements.
Not included DDL, bulk-insert, DBCC, Backup. etc.,
can't be enabled for master or tempdb

The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.

Here are the common scenarios where the SQL Server Query Store feature can be useful:
  • Find the most expensive queries for CPU, I/O, Memory etc.
  • Get full history of query executions
  • Get information about query regressions (a new execution plan generated by query engine is worse than the older one). Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan
  • Determine how many times a query was executed in the given range of time

Quick facts

  • The SQL Server Query Store is per-database-level feature which means that it can be enabled on every SQL database separately by using SQL Server Management Studio or T-SQL. It is not an instance level setting.
  • The SQL Server Query Store allows analyzing query performance using built-in reports and DMWs quickly and easily.
  • The SQL Server Query Store feature is available on every SQL Server editions.
  • Note, on Azure databases, the SQL Server Query Store feature is enabled by default.
  • The minimum permissions for using the SQL Server Query Store is VIEW DATABASE STATE permission:
USE AdventureWorks2014
GO
GRANT VIEW DATABASE STATE TO [USER];
  • This permission is not for forcing execution plan, data flush Interval etc. For this, you need to have db_owner fixed database role.
 Setup
To enable SQL Server Query Store for a database on on-promise SQL Server, right click on a database in Object Explorer and from the context menu, choose the Properties option:
From the Database Properties dialog in the Select a page section, select the Query Store page:
From the Operation Mode (Requested) drop down box, choose the Read Write item:
As soon as the Read Write item is chosen, the other fields under the Operation Mode (Requested) drop down box will be pre-populated with default values:
By pressing the OK button on the Database Properties dialog, the SQL Server Query Store is enabled to capture query execution plans and runtime information.
To confirm that the SQL Server Query Store is enabled on the chosen database, go to Object Explorer, refresh and expand the database. The SQL Server Query Store folder will appear with the list of available built-in reports:
To enable the SQL Server Query Store by using T-SQL execute the following statement in a query window:
ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON;
Note, SQL Server Query Store cannot be enabled for master or tempdb database.
The SQL Server Query Store will have performance impact on SQL Server 3-5% on average by Microsoft indication.

Options

The first option under the SQL Server Query Store page of the Database Properties dialog is the Operation Mode (Active) option:
This option is disabled and cannot be changed. This option indicates the state of the SQL Server Query Store. There are three modes of the SQL Server Query Store and those are OffRead Only and Read Write.
Off – The SQL Server Query Store turned off
Read Only – This mode indicates that new query runtime statistics or executed plans will not be tracked (collected)
Read Write – Allows capturing query executed plans and query runtime statistics
In the Operation Mode (Requested), in the drop-down box, the same options can be set as those mentioned for the previously option. Setting here the values will directly have impact on the state of the Query Store.
For example, if in the Operation Mode (Requested), from the drop-down box the Read Only value is chosen:
Reports
As mentioned earlier in the article, once enabled, the SQL Server Query Store will start to collect query runtime statistics and query Execution Plans. Built-in reports use collected data analyzing them and show them in grid or chart format depending on what is set in the reports.
Currently, there are six built-in reports
Regressed Queries is a built-in report that shows all queries that execution matrices are degraded in specific time range (last hour, day, week)
The Regressed Queries built-in report is divided in several pansBy default, the top 25 regressed queries in the last hour are shown.
Here, different options can be set in order to see desired information. For example, if want to see the raw date instead of the graph press the button for showing regressed queries in a grid format:
The Overall Resource Consumption built-in report shows summary resource consumption during the specific set of time. By default, results are shown for the last month and the results are shown in four charts: Duration, CPU Time, Logical Reads and Execution count:
To set additional charts report, time and aggregation interval, press the Configure button and the Configure Overall Resource Consumption dialog will appear where different options can be set for the Overall Resource Consumption report:
The Top Resource Consuming Queries built-in report shows, by default, the top 25 queries against specific database that consume most of resources like CPU Time, Memory Consumption, Physical Reads etc. over specific set of time:
With the Tracked Queries built-in report, query runtime statistics and query Execution plans can be tracked for the specific query over time. In the Tracking query text box, enter the query id (e.g. 205) and press the green play button next to the Tracking query box:
The Queries With Forced Plans built-in report shows all forced Execution Plans for specific queries:
To force SQL Server to use a specific Execution Plan for the particular query, in the Regressed Queries, Top Resource Consuming Queries, Queries With Hight Variation or Tracked Queries built-in reports, first select the Execution Plan Id and click the Force Plan button:
Press the Yes button on the Confirmation message box:
By doing this, you force SQL Server to use this Execution Plan for specific query from now on when that query is executed. This means that SQL Server will not generate a new Execution Plans for that query until unforce that plan.
To unforce SQL Server to use a specific Execution Plan for the particle query in the Queries With Forced Plans, Regressed Queries, Top Resource Consuming Queries, Queries With High Variation or Tracked Queries report, select the Execution Plan and press the Unforce Plan button:
In the Confirmation message box, press the Yes button to confirm removing forced Execution Plan:
This will remove the Execution Plan from the Queries With Forced Plans report.
The Queries With High Variation built-in report analyze the queries and show the queries with the most frequent parameterization problems:
Conclusion
The SQL Server Query Store is an powerful feature, for users of SQL Server 2016 and higher, that keeps tracking of query runtime execution and query execution plans, monitoring and analyzing the performance of queries and showing results in built-in reports. This provides powerful new capabilities comparedto previous methods DBAs were relegated to using in previous versions of SQL Server

No comments:

Post a Comment

Popular Posts