使用Query Store监控查询性能

2023-10-08 22:19:16

Query Store是SQL Server 2016中引入的语句性能监控和调优工具，它不仅自动捕获查询、执行计划和运行时统计信息的历史记录，而且还可以识别出由于执行计划更改而导致的性能差异，简化了性能故障排除的流程。Query Store从字面意思上来解释，它是查询的仓库，它使用异步更新方式，把数据存储到硬盘（Disk）中。

一，启用Query Store

Query Store默认是关闭的，启用Query Store对查询性能还是有一定的影响的，

使用Query Store监控查询性能
ALTER DATABASE { database_name | CURRENT }
SET QUERY_STORE
{
        = OFF
        | = ON [ ( <query_store_option_list> [,...n] ) ] 
        | CLEAR [ ALL ]
}

<query_store_option_list> ::=
{
      OPERATION_MODE = { READ_WRITE | READ_ONLY }
    | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
    | DATA_FLUSH_INTERVAL_SECONDS = number
    | MAX_STORAGE_SIZE_MB = number
    | INTERVAL_LENGTH_MINUTES = number
    | SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }
    | QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }
    | MAX_PLANS_PER_QUERY = number
    | WAIT_STATS_CAPTURE_MODE = { ON | OFF }
    | QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )
}

<query_capture_policy_option_list> :: =
{
    STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
    | EXECUTION_COUNT = number
    | TOTAL_COMPILE_CPU_TIME_MS = number
    | TOTAL_EXECUTION_CPU_TIME_MS = number
}
View Code

参数注释：

  • CLEAR：清空Query Store的内存
  • OPERATION_MODE：READ_WRITE是指Query Store会持续收集和持久化查询计划（query plan）和运行时的执行统计信息，而READ_ONLY是指只能从Query Store读取信息，而不会更新Query Store。
  • CLEANUP_POLICY：定义数据留存的时间窗口，超过该窗口，过期的数据从Query Store中清理出去。
  • DATA_FLUSH_INTERVAL_SECONDS：定义数据持久化到硬盘的频率，默认值是900s（15min）。为了优化性能，Query Store收集的数据采用异步写方式，每隔一定的时间会把捕获的数据写到硬盘中。
  • MAX_STORAGE_SIZE_MB：设置Query Store的最大存储空间。如果Query Store达到最大存储空间限制，Query Store会把操作模式（OPERATION_MODE）更改为READ_ONLY
  • INTERVAL_LENGTH_MINUTES：每隔一定的时间窗口对运行时的执行统计数据进行聚合，然后把聚合值存储到Query Store中。
  • SIZE_BASED_CLEANUP_MODE ：基于Query Store占用的空间大小控制是否启动清理程序，清理程序会自动删除Query Store中过时的数据，以释放Query Store的空间
  • QUERY_CAPTURE_MODE ：定义捕获查询的捕获模式，默认值是ALL，表示捕获所有的查询，AUTO表示基于执行次数和资源消耗来捕获相关的查询。
  • MAX_PLANS_PER_QUERY：定义为每个查询维护的计划数量，默认值是200
  • WAIT_STATS_CAPTURE_MODE ：是否捕获等待统计（wait stats）
  • QUERY_CAPTURE_POLICY：定义捕获Query的策略，
    • STALE_CAPTURE_POLICY_THRESHOLD ：定义评估的时间间隔（evaluation interval period ），根据以下的选项来确定是否一个query应该被捕获，evaluation period的默认值是1day
    • EXECUTION_COUNT：默认值是30，在evaluation period内，如果一个query的执行次数超过指定的数值，那么捕获该query。
    • TOTAL_COMPILE_CPU_TIME_MS：默认值是1000ms，在evaluation period内，如果一个query的编译时间超过指定的时间，那么捕获该query。
    • TOTAL_EXECUTION_CPU_TIME_MS：默认值是100，在evaluation period内，如果一个query的执行时间超过指定的时间，那么捕获该query。

例如，使用默认值来启用Query Store：

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE = ON 
    (
      OPERATION_MODE = READ_WRITE, 
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000, 
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO, 
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100 
      )
    );

用户可以通过系统视图来查看各个选项的配置情况：

sys.database_query_store_options

用户也可以使用SSMS来对Query Store的各个选项进行配置：

使用Query Store监控查询性能

二，Query Store捕获的信息

Query Store包含四个Store，分别是query store、plan store、runtime stats store和wait stats store。query store 用于捕获查询的信息，plan store用于捕获执行计划的信息，runtime stats store用于捕获执行计划的变更记录和统计信息等，stats store 用于捕获等待统计信息。

1，计划强制（Plan Forcing）

由于一些不可预知的原因，例如统计信息更改，架构更改，索引的创建/删除等，SQL Server中任意一个查询的执行计划通常都会随着时间的推移而变化。由于内存压力，计划也会从计划缓存中逐出，这导致计划缓存只存储查询的最新的执行计划。由执行计划更改引起的查询性能的下降，就无迹可寻，解决起来很费时间。

由于Query Store为每个查询保留多个执行计划，因此，它可以指示查询处理器（Query Processor）对查询强制使用特定的执行计划，这称为计划强制（Plan Forcing）。Query Store中的计划强制（Plan Forcing）的工作类似于USE PLAN查询提示的机制，但是不需要在用户在应用程序中做任何更改，计划强制可以在很短的时间内解决由计划更改导致的查询性能下降的问题。

2，等待统计（Wait Stats）

等待统计信息是解决查询性能问题的另一信息来源，长期以来，等待统计信息仅在实例级别可用，这使得很难将等待回溯到特定查询语句上。 从SQL Server 2017（14.x）和 Azure SQL数据库开始，查询存储能够追踪特定语句的等待信息。

ALTER DATABASE AdventureWorks2012 
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

三，Query Store相关的视图

Query Store相关的视图分为四类：Query、Plan、Runtime Stats和Wait Stats。注意，Wait Stats 是从SQL Server 2017(14.x)开始支持的。

1，关于Query的信息

query的唯一标识字段是query_id 

select q.query_id
    ,t.query_text_id
    ,t.query_sql_text
    ,q.object_id as parent_object
    ,q.is_internal_query
    ,q.query_parameterization_type
    ,q.query_parameterization_type_desc
    ,q.count_compiles
    ,q.avg_compile_duration
    ,q.avg_bind_cpu_time
    ,q.avg_bind_duration
    ,q.avg_compile_memory_kb
    ,q.avg_optimize_cpu_time
    ,q.avg_optimize_duration
from sys.query_store_query as q   
inner join sys.query_store_query_text as t  
    on q.query_text_id = t.query_text_id ;

2，关于Plan的信息

plan关联的query，可以通过字段query_id来关联

select p.plan_id
    ,p.query_id
    ,t.query_sql_text
    ,p.query_plan
    ,p.is_parallel_plan
    ,p.is_forced_plan
    ,p.force_failure_count
    ,p.last_force_failure_reason
    ,p.last_force_failure_reason_desc
    ,p.count_compiles
    ,p.avg_compile_duration
from sys.query_store_plan p
inner join sys.query_store_query q
    on p.query_id=q.query_id
inner join sys.query_store_query_text t
    on q.query_text_id=t.query_text_id

3，关于Plan 的 Runtime 统计

runtime stats通过plan_id 关联到特定的query

select r.runtime_stats_id
    ,r.plan_id
    ,r.runtime_stats_interval_id
    ,i.start_time as interval_start_time
    ,i.end_time as interval_end_time
    ,r.execution_type
    ,r.execution_type_desc
    ,r.count_executions
    ,r.avg_duration
    ,r.avg_cpu_time
    ,r.avg_dop
    ,r.avg_logical_io_reads
    ,r.avg_logical_io_writes
    ,r.avg_physical_io_reads
    ,r.avg_query_max_used_memory
    ,r.avg_rowcount
from sys.query_store_runtime_stats r
inner join sys.query_store_runtime_stats_interval i
    on r.runtime_stats_interval_id=i.runtime_stats_interval_id

4，关于等待的统计信息

该视图统计的等待是跟单个执行计划有关的

select w.wait_stats_id
    ,w.plan_id
    ,w.runtime_stats_interval_id
    ,w.wait_category
    ,w.wait_category_desc
    ,w.execution_type
    ,w.execution_type_desc
    ,w.avg_query_wait_time_ms
    ,w.min_query_wait_time_ms
    ,w.max_query_wait_time_ms
from sys.query_store_wait_stats w
inner join sys.query_store_plan p
    on w.plan_id=p.plan_id

 

参考文档：

Monitoring performance by using the Query Store

Query Store Catalog Views (Transact-SQL)

上一篇：如何shutdown关停外置tomcat5.0?


下一篇：Elasticsearch Pipeline 聚合分析