DBA工具——DMV——通过sys.dm_exec_procedure_stats查看存储过程执行信息

本文介绍了一种查询SQL Server中存储过程性能的方法,包括执行次数、执行计划、平均读写次数及所需时间等关键指标,并提供了获取这些信息的具体SQL语句。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

对于DBA来说,经常要手机存储过程的某些信息:

  1. 执行了多少次
  2. 执行的执行计划如何
  3. 执行的平均读写如何
  4. 执行平均需要多少时间
列名数据类型说明

database_id

int

存储过程所在的数据库 ID。

object_id

int

存储过程的对象标识号。

type

char(2)

对象的类型:

P = SQL 存储过程

PC = 程序集 (CLR) 存储过程

X = 扩展存储过程

type_desc

nvarchar(60)

对对象类型的说明:

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

EXTENDED_STORED_PROCEDURE

sql_handle

varbinary(64)

可用于与 sys.dm_exec_query_stats 中从此存储过程中执行的查询关联。

plan_handle

varbinary(64)

内存中计划的标识符。该标识符是瞬态的,仅当计划保留在缓存中时,它才保持不变。该值可以与 sys.dm_exec_cached_plans 动态管理视图一起使用。

cached_time

datetime

存储过程添加到缓存的时间。

cached_time

datetime

存储过程添加到缓存的时间。

last_execution_time

datetime

上次执行存储过程的时间。

execution_count

bigint

存储过程自上次编译以来所执行的次数。

total_worker_time

bigint

此存储过程自编译以来执行所用的 CPU 时间总量(微秒)。

last_worker_time

bigint

上次执行存储过程所用的 CPU 时间(微秒)。

min_worker_time

bigint

此存储过程在单次执行期间曾占用的最大 CPU 时间(微秒)。

max_worker_time

bigint

此存储过程在单次执行期间曾占用的最大 CPU 时间(微秒)。

total_physical_reads

bigint

此存储过程自编译后在执行期间所执行的物理读取总次数。

last_physical_reads

bigint

上次执行存储过程时所执行的物理读取次数。

min_physical_reads

bigint

该存储过程在单次执行期间所执行的最少物理读取次数。

max_physical_reads

bigint

该存储过程在单次执行期间所执行的最大物理读取次数。

total_logical_writes

bigint

此存储过程自编译后在执行期间所执行的逻辑写入总次数。

last_logical_writes

bigint

上次执行存储过程时所执行的逻辑写入次数。

min_logical_writes

bigint

该存储过程在单次执行期间所执行的最少逻辑写入次数。

max_logical_writes

bigint

该存储过程在单次执行期间所执行的最大逻辑写入次数。

total_logical_reads

bigint

此存储过程自编译后在执行期间所执行的逻辑读取总次数。

last_logical_reads

bigint

上次执行存储过程时所执行的逻辑读取次数。

min_logical_reads

bigint

该存储过程在单次执行期间所执行的最少逻辑读取次数。

max_logical_reads

bigint

该存储过程在单次执行期间所执行的最大逻辑读取次数。

total_elapsed_time

bigint

完成此存储过程的执行所用的总时间(微秒)。

last_elapsed_time

bigint

最近完成此存储过程的执行所用的时间(微秒)。

min_elapsed_time

bigint

任意一次完成此存储过程的执行所用的最短时间(微秒)。

max_elapsed_time

bigint

任意一次完成此存储过程的执行所用的最长时间(微秒)。


下面语句返回前十句耗费时间最长的存储过程信息:

SELECT TOP 10 a.object_id , a.database_id , DB_NAME(ISNULL(a.database_id,'')) 'DatabaseName', OBJECT_NAME(object_id, database_id) 'proc name' , a.cached_time , a.last_execution_time , a.total_elapsed_time , a.total_elapsed_time / a.execution_count AS [avg_elapsed_time] , a.execution_count , a.total_physical_reads / a.execution_count avg_physical_reads , a.total_logical_writes , a.total_logical_writes / a.execution_count avg_logical_reads , a.last_elapsed_time , a.total_elapsed_time / a.execution_count avg_elapsed_time , b.text , c.query_plan FROM sys.dm_exec_procedure_stats AS a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c ORDER BY [total_worker_time] DESC ; GO

<think>我们使用多个DMV(动态管理视图)来联合查询,找出执行时间长、逻辑读/物理读高、CPU消耗大的查询。 主要步骤: 1. 使用sys.dm_exec_query_stats获取缓存的查询计划相关的统计信息(如总执行时间、总逻辑读、总物理读、总CPU时间等)。 2. 通过cross apply sys.dm_exec_sql_text(sql_handle)获取SQL文本。 3. 通过cross apply sys.dm_exec_query_plan(plan_handle)获取执行计划(可选,如果需要查看计划)。 4. 关联sys.dm_exec_requests可以获取当前正在执行的请求(可选,用于实时监控)。 5. 关联sys.dm_exec_sessions可以获取会话信息(如登录名、主机名等)。 注意:由于这些DMV包含大量数据,我们通常只关注最消耗资源的查询,因此按某个指标排序并取前N条。 以下是一个示例查询,用于查找总CPU消耗最高的前50个查询(包括批处理中的单个查询语句):</think>要使用 `sys.dm_exec_query_stats`、`sys.dm_exec_requests` 和 `sys.dm_exec_sessions` 等 DMV 识别高消耗查询,可通过以下 T-SQL 脚本实现。该脚本按 CPU、逻辑读和执行时间排序,定位资源消耗最高的查询: ```sql SELECT TOP 20 total_worker_time/1000 AS [CPU消耗(ms)], total_logical_reads AS [逻辑读次数], total_elapsed_time/1000 AS [总耗时(ms)], execution_count AS [执行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均CPU(ms)], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [问题SQL片段], DB_NAME(st.dbid) AS [数据库名], OBJECT_NAME(st.objectid, st.dbid) AS [对象名], qs.plan_handle, qs.sql_handle FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE execution_count > 0 -- 排除未执行的查询 ORDER BY total_worker_time DESC, -- CPU消耗排序 total_logical_reads DESC; -- 或按逻辑读排序 ``` **关键说明:** 1. **`sys.dm_exec_query_stats`**:核心视图,提供累计统计信息(`total_worker_time`=CPU时间,`total_logical_reads`=逻辑读) 2. **`CROSS APPLY sys.dm_exec_sql_text`**:解析 SQL 文本,获取具体查询内容 3. **`SUBSTRING` 函数**:精准提取高消耗的 SQL 语句片段 4. **排序字段**:按需求调整 `ORDER BY`(如 `total_elapsed_time` 排序执行时间长的查询) **实时监控当前运行查询:** ```sql SELECT r.session_id, s.login_name, r.cpu_time AS [当前CPU(ms)], r.logical_reads AS [当前逻辑读], r.wait_type, r.wait_time, t.text AS [完整SQL] FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.status = 'running' ORDER BY r.cpu_time DESC; ``` **优化建议:** 1. 定期执行(如每 30 分钟)捕获历史高消耗查询 2. 结合 `WHERE` 过滤特定数据库(如 `DB_NAME(st.dbid) = 'YourDB'`) 3. 关注 `平均CPU > 100ms` 或 `逻辑读 > 1000` 的查询 4. 使用 `qs.plan_handle` 通过 `sys.dm_exec_query_plan` 获取执行计划
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值