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.
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 Off, Read
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 pans. By
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