使用查询存储监视性能Monitoring performance by using the Query Store

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 查询存储功能让你可以探查查询计划选项和性能。The SQL ServerSQL Server Query Store feature 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. 查询存储将自动捕获查询、计划和运行时统计信息的历史记录,并保留它们以供查阅。Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. 它按时间窗口将数据分割开来,使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. 可以使用 ALTER DATABASE SET 选项来配置查询存储。You can configure query store using the ALTER DATABASE SET option.

有关在 Azure SQL 数据库SQL Database中运行查询存储的信息,请参阅在 Azure SQL 数据库中运行查询存储For information about operating the Query Store in Azure SQL 数据库SQL Database, see Operating the Query Store in Azure SQL Database.

重要

如果仅对 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中正在运行的工作负载见解使用查询存储,请尽快安装 KB 4340759 中的性能可伸缩性修补程序。If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

启用查询存储Enabling the Query Store

默认情况下,新数据库的查询存储处于非活动状态。Query Store is not active for new databases by default.

SQL Server Management StudioSQL Server Management Studio 中使用“查询存储”页Use the Query Store Page in SQL Server Management StudioSQL Server Management Studio

  1. 在对象资源管理器中,右键单击数据库,然后单击“属性” 。In Object Explorer, right-click a database, and then click Properties.

    备注

    至少需要 16 版本的 Management StudioManagement StudioRequires at least version 16 of Management StudioManagement Studio.

  2. 在“数据库属性” 对话框中,选择“查询存储” 页。In the Database Properties dialog box, select the Query Store page.

  3. 在“操作模式(要求)” 对话框中,选择“读写” 。In the Operation Mode (Requested) box, select Read Write.

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

使用 Transact-SQL 语句Use Transact-SQL Statements

使用 ALTER DATABASE 语句来启用查询存储。Use the ALTER DATABASE statement to enable the query store. 例如:For example:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE (OPERATION_MODE = READ_WRITE); 

有关与查询存储相关的语法选项的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact SQL)For more syntax options related to the query store, see ALTER DATABASE SET Options (Transact-SQL).

备注

无法为 mastertempdb 数据库启用查询存储。You cannot enable the query store for the master or tempdb database.

重要

有关启用查询存储并使其适用于你的工作负载,请参阅查询存储最佳做法For information on enabling Query Store and keeping it adjusted to your workload, refer to Best Practice with the Query Store.

查询存储中的信息Information in the Query Store

由于统计信息更改、架构更改、索引的创建/删除等多种不同原因,SQL ServerSQL Server 中任何特定查询的执行计划通常会随着时间而改进。过程缓存(其中存储了缓存的查询计划)仅存储最近的执行计划。Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. 还会由于内存压力从计划缓存中逐出计划。Plans also get evicted from the plan cache due to memory pressure. 因此,执行计划更改造成的查询性能回归可能非常重大,且长时间才能解决。As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

由于查询存储会保留每个查询的多个执行计划,因此它可以强制执行策略,以引导查询处理器对某个查询使用特定执行计划。Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query. 这称为“计划强制”。This is referred to as plan forcing. 查询存储中的计划强制是通过使用类似于 USE PLAN 查询提示的机制来提供的,但它不需要在用户应用程序中进行任何更改。Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. 计划强制可在非常短的时间内解决由计划更改造成的查询性能回归。Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

备注

查询存储收集 DML 语句(如 SELECT、INSERT、UPDATE、DELETE、MERGE 和 BULK INSERT)的计划。Query Store collects plans for DML Statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT.

“等待统计信息” 是有助于排除 SQL Server 中的性能问题的另一信息来源。Wait stats are another source of information that helps to troubleshoot performance in SQL Server. 长期以来,等待统计信息仅适用于实例级别,难以回溯到实际查询。For a long time, wait statistics were available only on instance level, which made it hard to backtrack it to the actual query. 在 SQL Server 2017 和 Azure SQL 数据库中,我们在跟踪等待统计信息的查询存储中添加了另一个维度。In SQL Server 2017 and Azure SQL Database we added another dimension in Query Store that tracks wait stats.

使用查询存储功能的常见方案为:Common scenarios for using the Query Store feature are:

  • 快速查找并修复通过强制使用先前查询计划而造成的计划性能回归。Quickly find and fix a plan performance regression by forcing the previous query plan. 修复近期由于执行计划更改而出现性能回归的查询。Fix queries that have recently regressed in performance due to execution plan changes.
  • 确定在给定时间窗口中查询执行的次数,从而帮助 DBA 对性能资源问题进行故障排除。Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • 标识过去 x 小时内的前 n 个查询(按执行时间、内存占用等)。Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • 审核给定查询的查询计划历史记录。Audit the history of query plans for a given query.
  • 分析特定数据库的资源(CPU、I/O 和内存)使用模式。Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
  • 确定资源上正在等待的前 n 个查询。Identify top n queries that are waiting on resources.
  • 了解特定查询或计划的等待性质。Understand wait nature for a particular query or plan.

查询存储包含三个存储:The query store contains three stores:

  • 计划存储:用于保存执行计划信息 。a plan store for persisting the execution plan information.
  • 运行时统计信息存储: 用于保存执行统计信息。a runtime stats store for persisting the execution statistics information.
  • 等待统计信息存储: 用于保存等待统计信息。a wait stats store for persisting wait statistics information.

max_plans_per_query 配置选项限制了计划存储中查询可存储的唯一计划数。The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. 为增强性能,通过异步方式向存储写入信息。To enhance performance, the information is written to the stores asynchronously. 为尽量减少空间使用量,将在按固定时间窗口上聚合运行时统计信息存储中的运行时执行统计信息。To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 可通过查询查询存储目录视图来查看这些存储中的信息。The information in these stores is visible by querying the query store catalog views.

以下查询返回查询存储中查询和计划的相关信息。The following query returns information about queries and plans in the query store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*  
FROM sys.query_store_plan AS Pl  
INNER JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
INNER JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id ;  

使用回归查询功能Use the Regressed Queries Feature

在启用查询存储后,刷新对象资源管理器的数据库部分,以添加“查询存储” 部分。After enabling the query store, refresh the database portion of the Object Explorer pane to add the Query Store section.

SSMS 对象资源管理器中的 SQL Server 2016 查询存储树 SSMS 对象资源管理器中的 SQL Server 2017 查询存储树SQL Server 2016 Query Store tree in SSMS Object Explorer SQL Server 2017 Query Store tree in SSMS Object Explorer

选择“回归查询” ,以在 中打开“回归查询” SQL Server Management StudioSQL Server Management Studio窗格。Select Regressed Queries to open the Regressed Queries pane in SQL Server Management StudioSQL Server Management Studio. “回归查询”窗格将显示查询存储中的查询和计划。The Regressed Queries pane shows you the queries and plans in the query store. 使用顶部的下拉框根据各种条件筛选查询:持续时间(单位 ms,此为默认值)、CPU 时间 (ms)、逻辑读取 (KB)、逻辑写入 (KB)、物理读取 (KB)、CLR 时间 (ms)、DOP、内存使用量 (KB)、行计数、已用日志内存 (KB)、已用临时 DB 内存 (KB) 和等待时间 (ms) 。Use the drop down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).
选择某个计划以查看图形查询计划。Select a plan to see the graphical query plan. 可以使用按钮查看源查询、强制执行和取消强制执行查询计划、在网格和图表格式之间进行切换、比较所选的计划(如果选择多个)及刷新显示。Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

SSMS 对象资源管理器中的 SQL Server 2016 回归查询SQL Server 2016 Regressed Queries in SSMS Object Explorer

若要强制执行某一计划,请选择查询和计划,然后单击“强制计划” 。To force a plan, select a query and plan, and then click Force Plan. 你只可以强制执行由查询计划功能保存且仍保留在查询计划缓存中的计划。You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

查找等待查询Finding wait queries

SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 2.0 和 Azure SQL DatabaseAzure SQL Database 开始,查询存储中将提供随时间变化每个查询的等待统计信息。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 2.0 and Azure SQL DatabaseAzure SQL Database, wait statistics per query over time are available in Query Store. 在查询存储中,等待类型将合并到等待类别中 。In Query Store, wait types are combined into wait categories. sys.query_store_wait_stats (Transact-SQL).中提供从等待类别到等待类型的映射。The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

SQL Server Management StudioSQL Server Management Studio v18 或更高版本中,选择“查询等待统计信息”以打开“查询等待统计信息”窗格 。Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management StudioSQL Server Management Studio v18 or higher. “查询等待统计信息”窗格显示包含查询存储中排在前面的等待类别的条形图。The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. 使用顶部的下拉框选择等待时间的聚合条件:平均值、最大值、最小值、标准偏差和总计(默认) 。Use the drop down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

SSMS 对象资源管理器中的 SQL Server 2017 查询等待统计信息SQL Server 2017 Query Wait Statistics in SSMS Object Explorer

通过单击条形图和所选等待类别展示的详细信息视图,选择等待类别。Select a wait category by clicking on the bar and a detail view on the selected wait category displays. 这个新的条形图包含对该等待类别有贡献的查询。This new bar chart contains the queries that contributed to that wait category.

SSMS 对象资源管理器中的 SQL Server 2017 查询等待统计信息的详细信息视图SQL Server 2017 Query Wait Statistics detail view in SSMS Object Explorer

使用顶部的下拉框根据各种等待时间条件为所选等待类别筛选查询:平均值、最大值、最小值、标准偏差和总计(默认) 。Use the drop down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). 选择某个计划以查看图形查询计划。Select a plan to see the graphical query plan. 可使用按钮来查看源查询,强制执行和取消强制执行某一查询计划,以及刷新显示内容。Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

等待类别 可将不同等待类型按性质合并为类似的桶。Wait categories are combining different wait types into buckets similar by nature. 不同的等待类别需要不同的后续分析才能解决此问题,但相同类别的等待类型可引起非常相似的故障排除体验,并假定基于等待的受影响的查询会成为用于成功完成此类调查的大部分内容的缺少的部分。Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

下面的示例介绍如何在查询存储中引入等待类别前后更深入了解工作负荷:Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

曾经的体验Previous experience 新的体验New experience 操作Action
每个数据库的高 RESOURCE_SEMAPHORE 等待High RESOURCE_SEMAPHORE waits per database 特定查询在查询存储中的高内存等待High Memory waits in Query Store for specific queries 查找查询存储中前几个使用内存最多的查询。Find the top memory consuming queries in Query Store. 这些查询可能会进一步延迟受影响查询的进度。These queries are probably delaying further progress of the affected queries. 请考虑对这些查询或受影响的查询使用 MAX_GRANT_PERCENT 查询提示。Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
每个数据库的高 LCK_M_X 等待High LCK_M_X waits per database 特定查询在查询存储中的高锁定等待High Lock waits in Query Store for specific queries 检查受影响查询的查询文本,并确定目标实体。Check the query texts for the affected queries and identify the target entities. 在查询存储中查找修改同一实体的其他查询,该实体频繁执行和/或具有较高持续时间。Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. 确定这些查询后,请考虑更改应用程序逻辑,以提高并发性,或使用限制性较弱的隔离级别。After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
每个数据库的高 PAGEIOLATCH_SH 等待High PAGEIOLATCH_SH waits per database 特定查询在查询存储中的高缓冲 IO 等待High Buffer IO waits in Query Store for specific queries 在查询存储中查找读取数越高的查询。Find the queries with a high number of physical reads in Query Store. 如果它们与含较高 IO 等待的查询匹配,执行执行搜索而不是扫描时,请考虑引入关于基础实体的索引,以便减少查询的 IO 开销。If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
每个数据库的高 SOS_SCHEDULER_YIELD 等待High SOS_SCHEDULER_YIELD waits per database 特定查询在查询存储中的高 CPU 等待High CPU waits in Query Store for specific queries 查找查询存储中前几个使用 CPU 最多的查询。Find the top CPU consuming queries in Query Store. 其中,请确定其高 CPU 趋势与受影响查询的高 CPU 等待关联的查询。Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. 重点优化这些查询 - 可能存在计划回归,或缺失的索引。Focus on optimizing those queries - there could be a plan regression, or perhaps a missing index.

配置选项Configuration Options

以下选项可用于配置查询存储参数。The following options are available to configure query store parameters.

OPERATION_MODE OPERATION_MODE
可以为 READ_WRITE(默认)或 READ_ONLY 。Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS) CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
配置 STALE_QUERY_THRESHOLD_DAYS 参数,以指定数据在查询存储中保留的天数。Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. 默认值为 30。The default value is 30. 对于 SQL 数据库SQL Database 基本版,默认值为 7 天 。For SQL 数据库SQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDS DATA_FLUSH_INTERVAL_SECONDS
确定写入到查询存储的数据保留到磁盘的频率。Determines the frequency at which data written to the query store is persisted to disk. 为了优化性能,由查询存储收集的数据应以异步方式写入到磁盘。To optimize for performance, data collected by the query store is asynchronously written to the disk. 此异步传输发生的频率通过 DATA_FLUSH_INTERVAL_SECONDS 进行配置。The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. 默认值为 900(15 分钟) 。The default value is 900 (15 min).

MAX_STORAGE_SIZE_MB MAX_STORAGE_SIZE_MB
配置查询存储的最大大小。Configures the maximum size of the query store. 如果查询存储中的数据达到 MAX_STORAGE_SIZE_MB 限制,查询存储会自动从读写状态更改为只读状态,并停止收集新数据。If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. 默认值为 100 MB。The default value is 100 MB. 对于 SQL 数据库SQL Database 高级版,默认值为 1 GB,对于 SQL 数据库SQL Database 基本版,默认值为 10 MB 。For SQL 数据库SQL Database Premium edition, default is 1 GB and for SQL 数据库SQL Database Basic edition, default is 10 MB.

INTERVAL_LENGTH_MINUTES INTERVAL_LENGTH_MINUTES
确定运行时执行统计数据聚合到查询存储中的时间间隔。Determines the time interval at which runtime execution statistics data is aggregated into the query store. 为了优化空间使用情况,将在固定时间窗口上聚合运行时统计信息存储中的运行时执行统计信息。To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. 此固定时间窗口通过 INTERVAL_LENGTH_MINUTES 进行配置。This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 默认值是 60秒。The default value is 60.

SIZE_BASED_CLEANUP_MODE SIZE_BASED_CLEANUP_MODE
控制当数据总量接近最大大小时是否自动激活清除进程。Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. 可为“AUTO”(默认)或“OFF” 。Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODE QUERY_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 tracks current queries. 可以是 ALL(捕获所有查询)、AUTO(忽略频率较低的查询以及编译和执行持续时间较短的查询)或 NONE(停止捕获新查询) 。Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)上的默认值为 ALL,而 Azure 上的默认值为 AUTOSQL 数据库SQL DatabaseThe default value on SQL ServerSQL Server (from SQL Server 2016 (13.x)SQL Server 2016 (13.x) to SQL Server 2017SQL Server 2017) is ALL, while on Azure SQL 数据库SQL Database is AUTO.

MAX_PLANS_PER_QUERY MAX_PLANS_PER_QUERY
一个整数,表示为每个查询保留的最大计划数。An integer representing the maximum number of plans maintained for each query. 默认值为 200 。The default value is 200.

WAIT_STATS_CAPTURE_MODE WAIT_STATS_CAPTURE_MODE
控制查询存储是否捕获等待统计信息。Controls if Query Store captures wait statistics information. 可以为“OFF”或“ON”(默认) 。Can be OFF or ON (default).

查询 sys.database_query_store_options 视图以确定查询存储的当前选项。Query the sys.database_query_store_options view to determine the current options of the query store. 有关值的详细信息,请参阅 sys.database_query_store_optionsFor more information about the values, see sys.database_query_store_options.

若要深入了解如何使用 Transact-SQLTransact-SQL 语句来设置选项,请参阅 选项管理For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

通过 Management StudioManagement Studio 或使用以下视图和过程来查看和管理查询存储。View and manage Query Store through Management StudioManagement Studio or by using the following views and procedures.

查询存储函数Query Store Functions

此函数有助于执行查询存储操作。Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

查询存储目录视图Query Store Catalog Views

目录视图提供了查询存储的相关信息。Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings (Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query (Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval (Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

查询存储存储过程Query Store Stored Procedures

存储过程配置了查询存储。Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats (Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan (Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transact-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query (Transact-SQL)sp_query_store_remove_query (Transact-SQL)
sp_query_store_consistency_check (Transct-SQL)sp_query_store_consistency_check (Transct-SQL)

键使用方案Key Usage Scenarios

选项管理Option Management

本部分提供一些有关如何管理查询存储功能本身的准则。This section provides some guidelines on managing Query Store feature itself.

查询存储当前是否处于活动状态?Is Query Store currently active?

查询存储将其数据存储在用户数据库内,正因为此,它具有大小限制(使用 MAX_STORAGE_SIZE_MB 进行配置)。Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). 如果查询存储中的数据命中该限制,则查询存储将自动从读写状态更改为只读状态,并停止收集新数据。If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

查询 sys.database_query_store_options ,以确定当前查询存储是否可用,以及当前是否在收集运行时状态。Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

SELECT actual_state, actual_state_desc, readonly_reason,   
    current_storage_size_mb, max_storage_size_mb  
FROM sys.database_query_store_options;  

actual_state 列决定查询存储状态。Query Store status is determined by actual_state column. 如果不处于所需状态,请查看 readonly_reason 列,了解详细信息。If it's different than the desired status, the readonly_reason column can give you more information.
查询存储大小超过配额时,该功能将切换到 readon_only 模式。When Query Store size exceeds the quota, the feature will switch to readon_only mode.

获取查询存储选项Get Query Store options

若要了解查询存储状态的相关详细信息,请在用户数据库中执行以下操作。To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

设置查询存储间隔Setting Query Store interval

你可以覆盖用于聚合查询运行时统计信息的时间间隔(默认值为 60 分钟)。You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>   
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);  

备注

INTERVAL_LENGTH_MINUTES 不允许使用任意值。Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. 使用下列其中一个:1、5、10、15、30、60 或 1440 分钟。Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

间隔的新值通过 sys.database_query_store_options 视图公开。New value for interval is exposed through sys.database_query_store_options view.

查询存储空间使用情况Query Store space usage

若要检查当前的查询存储大小和限制,请在用户数据库中执行以下语句。To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb   
FROM sys.database_query_store_options;  

如果查询存储已满,请使用以下语句来扩展存储。If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>   
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);  

设置所有查询存储选项Set all Query Store options

你可以使用单个 ALTER DATABASE 语句同时设置多个查询存储选项。You can set multiple Query Store options at once with a single ALTER DATABASE statement.

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,
    WAIT_STATS_CAPTURE_MODE = ON 
);  

清理空间Cleaning up the space

查询存储时间间隔表是在数据库创建期间在 PRIMARY 文件组中创建的,且之后不可更改此配置。Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. 如果空间不足,你可以要使用以下语句来清理旧的查询存储数据。If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;  

或者,你可以只清理临时查询数据,因为此数据与查询优化和计划分析的相关性更低,但却占用了大量空间。Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

“删除临时查询”会删除只执行了一次且已超过 24 小时的查询。Delete ad-hoc queries This deletes the queries that were only executed only once and that are more than 24 hours old.

DECLARE @id int  
DECLARE adhoc_queries_cursor CURSOR   
FOR   
SELECT q.query_id  
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan AS p   
    ON p.query_id = q.query_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  
  
OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @id  
        EXEC sp_query_store_remove_query @id  
        FETCH NEXT FROM adhoc_queries_cursor INTO @id  
    END   
CLOSE adhoc_queries_cursor ;  
DEALLOCATE adhoc_queries_cursor;  

你可以使用其他逻辑来定义自己的过程,以清理不再需要的数据。You can define your own procedure with different logic for clearing up data you no longer want.

以上示例使用 sp_query_store_remove_query 扩展存储过程来删除不必要的数据。The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. 还可以使用:You can also use:

  • sp_query_store_reset_exec_stats 用于清除给定计划的运行时统计信息 。sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
  • sp_query_store_remove_plan 用于删除单个计划 。sp_query_store_remove_plan to remove a single plan.

性能审核和疑难解答Performance Auditing and Troubleshooting

查询存储将保存整个查询过程中的编译历史记录和运行时度量,使你能询问有关工作负载的问题。Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

在数据库上执行的最后 n 个查询 ?Last n queries executed on the database?

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;  

每个查询的执行数量?Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
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  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

过去一小时内具有最长平均执行时间的查询数量?The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    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  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

在相应的平均行计数和执行计数下,过去 24 小时内具有最大平均物理 IO 读取数的查询数量?The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
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   
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id  
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

具有多个计划的查询?Queries with multiple plans? 这些查询特别有趣,因为计划选择更改可能造成它们的性能回归。These queries are especially interesting because they are candidates for regressions due to plan choice change. 以下查询将这些查询和所有计划一同进行了标识:The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, 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  
)  
  
SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans 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;  

最近性能回归的查询(对比不同时间点)?Queries that recently regressed in performance (comparing different point in time)? 以下查询示例返回了其执行时间因计划选择更改而在过去 48 小时内翻倍的所有查询。The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. 并排查询所有的运行时统计信息时间间隔。Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
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 p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

如果你向查看所有性能回归(而不仅是计划选择更改造成的回归),只需从先前查询中删除条件 AND p1.plan_id <> p2.plan_idIf you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

等待时间最长的查询?Queries that are waiting the most? 此查询将返回等待最多的前 10 个查询。This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

最近性能回归的查询(对比近期执行和历史执行)?Queries that recently regressed in performance (comparing recent vs. history execution)? 下一查询会根据执行时间段来比较查询执行。The next query compares query execution based periods of execution. 在此特定示例中,查询对比了最近时期(1 小时)和历史时期(过去一天)中的执行,并标识了引入 additional_duration_workload的查询。In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. 此度量的计算方式是最近平均执行和历史平均执行之差,再乘以最近执行数量。This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. 它实际上表示相对于历史记录,引入了多少额外的持续时间最近执行:It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  
  
--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  
  
WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time <= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time <= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time <= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time <= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

维护查询性能稳定性Maintaining Query Performance Stability

对于执行多次的查询,你可能注意到 SQL ServerSQL Server 使用了会导致不同资源利用率和持续时间的不同计划。For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. 借助查询存储,可以检测到查询性能何时回归,并确定在感兴趣的时间段内的最优计划。With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. 然后你可以对未来的查询执行强制执行此最优计划。You can then force that optimal plan for future query execution.

你还可以使用参数(自动参数化或手动参数化)来标识某一查询内不一致的查询性能。You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). 你可以在不同计划中标识出对所有或大多数参数值而言足够快和最佳的计划,并强制执行此计划,为更大范围的用户方案保留可预测的性能。Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

强制执行查询计划(应用强制策略)Force a plan for a query (apply forcing policy)

当强制执行某一查询的计划时,SQL ServerSQL Server 尝试在优化器中强制执行该计划。When a plan is forced for a certain query, SQL ServerSQL Server tries to force the plan in the optimizer. 如果计划强制实施失败,将触发 XEvent,并指示优化器正常优化。If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

使用 sp_query_store_force_plan 时,你只可以强制执行查询存储记录为该查询计划的那些计划。When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. 换句话说,可用于查询的计划只有那些在查询存储处于活动状态时已用于执行该查询的计划。In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

计划强制支持快进和静态游标 Plan forcing support for fast forward and static cursors

SQL Server 2019(预览版)SQL Server 2019 preview CTP 2.3 查询存储支持为快进和静态 T-SQL 及 API 游标强制执行查询执行计划。CTP 2.3 Query Store supports the ability to force query execution plans for fast forward and static T-SQL and API cursors. 现在,通过 sp_query_store_force_plan 或通过 SQL Server Management Studio 查询存储报表支持强制执行。Forcing is now supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.

删除为查询强制执行的计划Remove plan forcing for a query

若要再次依靠 SQL ServerSQL Server 查询优化器来计算最佳查询计划,请使用 sp_query_store_unforce_plan 来取消强制执行为查询选定的计划。To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

另请参阅See Also

查询存储最佳实践 Best Practice with the Query Store
通过内存中 OLTP 使用查询存储 Using the Query Store with In-Memory OLTP
查询存储使用方案 Query Store Usage Scenarios
查询存储的数据收集方式 How Query Store Collects Data
查询存储存储过程 (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
查询存储目录视图 (Transact-SQL) Query Store Catalog Views (Transact-SQL)
监视和优化性能 Monitor and Tune for Performance
性能监视和优化工具 Performance Monitoring and Tuning Tools
打开活动监视器 (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
实时查询统计信息 Live Query Statistics
活动监视器 Activity Monitor
sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL)
在 Azure SQL 数据库中运行查询存储Operating the Query Store in Azure SQL Database