logo

Thông báo

Icon
Error

Chia sẻ
Tùy chọn
Xem
Xem bài viết cuối
Offline admin  
#1 Đã gửi : 20/05/2019 lúc 01:26:16(UTC)
admin

Danh hiệu: Administration

Chức danh:

Nhóm: Administrators
Gia nhập: 23-07-2013(UTC)
Bài viết: 5,849
Man
Viet Nam
Đến từ: Vietnam

Cảm ơn: 8 lần
Được cảm ơn: 2 lần trong 2 bài viết

Enable Query Store from Management Studio

From the Object Explorer pane, right-click the database and select the Properties option.

Click the Query Store tab and change the ‘Enable’ to TRUE:

sql query store

Enable Query Store from T-SQL statement

In a new query window, the following statement enables the Query Store feature on the database ‘QueryStoreDB’

Mã:
  ALTER DATABASE QueryStoreDB SET QUERY_STORE = ON;

Configuration options

The Query Store has a series of configuration options. All of them can be set from SQL Server Management Studio through th GUI or using T-SQL statements.

OPERATION_MODE – This can be READ_WRITE or READ_ONLY and states if the Query Store is to collect new data (READ_WRITE) or not to collect data and just hold current data (READ_ONLY).

CLEANUP_POLICY – Specifies through the STALE_QUERY_THRESHOLD_DAYS the number of days for the query store to retain data.

DATA_FLUSH_INTERVAL_SECONDS – Gives the interval in which the data written to the Query Store is persisted to the disk. The frequency, which is asynchronous, for which the transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS.

MAX_STORAGE_SIZE_MB – This gives the maximum size of the total data in the Query Store. If and when the limit is reached, the OPERATION_MODE is automatic changed to READ_ONLY and no more data is collected.

INTERVAL_LENGTH_MINUTES – Gives the interval at which the data from runtime execution stats is aggregated. The option gives the fixed time window for this aggregation.

SIZE_BASED_CLEANUP_MODE – When the data in the Query Store gets close to the configured number in MAX_STORAGE_SIZE_MB this option can control the automatic cleanup process.

QUERY_CAPTURE_MODE – Gives the Query Store option to capture all queries or relevant queries based on execution count and resource usage.

MAX_PLANS_PER_QUERY – The maximum number of execution plans maintained for queries.

From SQL Server Management Studio, the window look like below when the Query Store is enabled. Also in the bottom of this window, you can see the current disk usage:

sql query store

The T-SQL syntax for setting the Query Store options is as follows:

Mã:
ALTER DATABASE <database name>

SET QUERY_STORE (

    OPERATION_MODE = READ_WRITE,

    CLEANUP_POLICY =

    (STALE_QUERY_THRESHOLD_DAYS = 30),

    DATA_FLUSH_INTERVAL_SECONDS = 3000,

    MAX_STORAGE_SIZE_MB = 500,

    INTERVAL_LENGTH_MINUTES = 15,

    SIZE_BASED_CLEANUP_MODE = AUTO,

    QUERY_CAPTURE_MODE = AUTO

    MAX_PLANS_PER_QUERY = 1000

);

What information can be found in the Query Store

Specific queries in the SQL server normally has evolving execution plans over time. Thisis due to e.g. schema changes, changes in statistics, indexes etc. Also the plan cache evicts execution plans due to a memory pressure. The result is that the query performance troubleshooting can be non-trivial and time consuming to resolve.

The Query Store retains multiple execution plans per query. Therefore, it can be used to enforce certain execution plans to specific queries. This is called plan forcing (see below for stored procedure to do this).

Prior to SQL 2016 the hint ‘USE PLAN’ was used, but now it is a fairly easy task to enforce a specific execution plan to the query processor.

More scenarios for using the Query Store:

  • Find and fix queries that have a regression in performance due to plan changes
  • Overview of how often and in which context a query has been executed, helping the DBA on performance tuning tasks
  • Overview of the historic plan changes for a given query
  • Identity top n queries (by time, CPU time, IO, etc.) in the past x hours
  • Analyze the use of resources (IO, CPU and memory)

The Query Store contains two stores – a plan store and a runtime stats store. The Plan Store persists the execution plan information and the Runtime Stats Store persists the execution statistics information. Information is written to the two stores asynchronously to optimize performance.

The space used to hold the runtime execution information can grow over time, so the data is aggregated over a fixed time window as per setting made in the configuration.

When the Query Store feature is enabled in the database, a set of system views will be ready for queries.

sys.database_query_store_options

sys.query_context_settings

sys.query_store_query

sys.query_store_query_text

sys.query_store_plan

sys.query_store_runtime_stats

sys.query_store_runtime_stats_interval

Furthermore, a series of system stored procedures can be called:

sp_query_store_flush_db

sp_query_store_reset_exec_stats

sp_query_store_force_plan

sp_query_store_unforce_plan

sp_query_store_remove_plan

sp_query_store_remove_query

How to use Query Store

The Query Store comes with 4 standard reports as shown below:

sql query store

All standard reports can be modified in several ways to fit your personal needs. This is done by selection in drop-downs and point-and-click.

The Regressed Queries gives an overview of the top 25 most resource consuming queries in the last hour. This includes the execution plan, a time table to see when and for how long the query took to run etc.:

The Overall Resource Consumption shows 4 charts as standard based on duration, execution count, CPU time and Logical reads:

The Top Resource Consuming Queries report shows in the same format as Regressed Queries only non-aggregated and with more details.

The Tracked Queries report shows detailed data from the selected query – here you need to find and remember the query id – this can be found, among other ways, from below queries against the Query Store system views.

The data from the Query Store can be accessed from the above described system views. Examples of usage can be found below.

Top 5 queries with the longest average execution time the last hour

Mã:
SELECT TOP 5

   rs.avg_duration

   ,qt.query_sql_text

   ,rs.last_execution_time

FROM

   sys.query_store_query_text AS qt

   RIGHT JOIN sys.query_store_query AS q

      ON qt.query_text_id = q.query_text_id

   RIGHT JOIN sys.query_store_plan AS p

      ON q.query_id = p.query_id

   RIGHT JOIN sys.query_store_runtime_stats AS rs

      ON p.plan_id = rs.plan_id

WHERE  1=1

   AND rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())

ORDER BY

   rs.avg_duration DESC;

Last 10 queries executed on the server

Mã:
SELECT TOP 10 qt.query_sql_text, q.query_id,

    qt.query_text_id, p.plan_id, rs.last_execution_time

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

    ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

    ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

    ON p.plan_id = rs.plan_id

ORDER BY rs.last_execution_time DESC;

Queries with more than one execution plan

Mã:
SELECT

q.query_id

,qt.query_sql_text

,p.query_plan AS plan_xml

,p.last_execution_time

FROM (SELECT COUNT(*) AS count, q.query_id 

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

    ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

    ON p.query_id = q.query_id

GROUP BY q.query_id

HAVING COUNT(distinct plan_id) > 1) AS qm

JOIN sys.query_store_query AS q

    ON qm.query_id = q.query_id

JOIN sys.query_store_plan AS p

    ON q.query_id = p.query_id

JOIN sys.query_store_query_text qt

    ON qt.query_text_id = q.query_text_id

ORDER BY query_id, plan_id;

Source: Monitoring Performance By Using the Query Store

Ai đang xem chủ đề này?
OceanSpiders 2.0
Di chuyển  
Bạn không thể tạo chủ đề mới trong diễn đàn này.
Bạn không thể trả lời chủ đề trong diễn đàn này.
Bạn không thể xóa bài của bạn trong diễn đàn này.
Bạn không thể sửa bài của bạn trong diễn đàn này.
Bạn không thể tạo bình chọn trong diễn đàn này.
Bạn không thể bỏ phiếu bình chọn trong diễn đàn này.

| Powered by YAF.NET 2.2.4.14 | YAF.NET © 2003-2019, Yet Another Forum.NET
Thời gian xử lý trang này hết 0.282 giây.