SQLServer统计监控SQL执行计划突变的方法

sqlserver,sql · 浏览次数 : 15

小编点评

## Using Dynamic Management Views (DMVs) to Detect SQL Query Plan Mutations This document provides an overview of DMVs and their queries for detecting SQL query plan mutations. **DMVs for Query Execution Statistics:** * `sys.dm_exec_query_stats`: Provides comprehensive statistics on query execution, including CPU time, total execution time, execution count, and more. * `sys.dm_exec_sql_text`: Allows retrieval of SQL text associated with a specific `sql_handle`. **Combined Queries for Plan Details:** * `sys.dm_exec_query_stats` and `sys.dm_exec_sql_text`: Provides complete information about the query, including SQL handle, statement text, and execution details. * `sys.dm_exec_cached_plans`: Offers statistics on cached plans, including size, execution counts, and creation times. * `sys.dm_exec_query_plan`: Combines information from `sys.dm_exec_cached_plans` and `sys.dm_exec_sql_text` for specific query plans. **Monitoring Plan Mutations:** * Regularly run the queries to track changes in `total_worker_time` or `total_elapsed_time`. * Store the results in a persistent table for historical analysis. * Compare current and previous results to detect significant changes, indicating potential plan mutations. * Consider using additional DMVs like `sys.dm_exec_query_stats` and `sys.dm_exec_sql_text` for richer information. **Alerting on Plan Changes:** * Set alerts for changes in specific metrics like `total_worker_time` or `total_elapsed_time`. * These alerts can trigger notifications or take other necessary actions to track down the cause of the plan change. **Additional Notes:** * Ensure that DMVs are enabled for the relevant SQL Server version. * Use appropriate filters and projections to narrow down the results and focus on specific aspects of plan changes. * Combine the information from DMVs with other system tables and alerts for comprehensive monitoring and detection of plan mutations.

正文

使用动态管理视图（DMVs）来检测SQL执行计划的突变，你需要关注那些能够提供查询执行统计和计划信息的视图。以下是一些可以用于此目的的DMVs以及相应的查询示例：

sys.dm_exec_query_stats：这个视图提供了关于SQL Server中查询执行的统计信息，包括CPU时间、总工作时间、执行次数等。

SELECT sql_handle, statement_start_offset, statement_end_offset, creation_time, last_execution_time, execution_count, total_worker_time, total_elapsed_time FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;

sys.dm_exec_sql_text：结合sys.dm_exec_query_stats使用，可以获取与sql_handle对应的SQL文本。

SELECT qs.sql_handle, st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;

sys.dm_exec_cached_plans：这个视图包含了缓存中的执行计划的统计信息，如计划的大小、执行次数等。

SELECT cacheobjtype, usecounts, cacheobjtype, objtype, size_in_bytes, creation_time, last_use_time FROM sys.dm_exec_cached_plans ORDER BY usecounts DESC;

sys.dm_exec_query_plan：这个视图与sys.dm_exec_sql_text结合使用，可以获取特定查询的执行计划。

SELECT qp.query_plan, st.text FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

要检测执行计划的突变，可以定期运行上述查询，并将结果存储在表中，或者与之前的结果进行比较。

例如，可以比较两个不同时间点的total_worker_time或total_elapsed_time，以查看是否有显著变化，这可能表明执行计划已经改变。

由于sys.dm_exec_query_stats中的信息可能会被SQL Server周期性地重置，因此最好的做法是将这些信息记录到一个持久化表中，这样可以基于历史数据来检测突变。

此外，执行计划的突变可能需要结合多个DMVs的信息来综合判断。