人人都是 DBA（XIV）存储过程信息收集脚本汇编

首页 > 代码库 > 人人都是 DBA（XIV）存储过程信息收集脚本汇编

人人都是 DBA（XIV）存储过程信息收集脚本汇编

2024-08-07 18:10:35 216人阅读

原文:人人都是 DBA（XIV）存储过程信息收集脚本汇编

什么？有个 SQL 执行了 8 秒！

哪里出了问题？臣妾不知道啊，得找 DBA 啊。

DBA 人呢？离职了！！擦！！！

程序员在无处寻求帮助时，就得想办法自救，努力让自己变成 "伪 DBA"。

索引

获取存储过程 SP 执行次数排名
查看哪个 SP 执行的平均时间最长
查看哪个 SP 执行的平均时间最不稳定
查看哪个 SP 耗费了最多的 CPU 时间
查看哪个 SP 执行的逻辑读最多
查看哪个 SP 执行的物理读最多
查看哪个 SP 执行的逻辑写最多

获取存储过程 SP 执行次数排名

SELECT TOP (100) p.[name] AS [SP Name]    ,qs.execution_count    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]    ,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]    ,qs.total_worker_time AS [TotalWorkerTime]    ,qs.total_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.execution_count DESCOPTION (RECOMPILE);

查看哪个 SP 执行的平均时间最长

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.total_elapsed_time    ,qs.execution_count    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]    ,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]    ,qs.total_worker_time AS [TotalWorkerTime]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY avg_elapsed_time DESCOPTION (RECOMPILE);

查看哪个 SP 执行的平均时间最不稳定

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.execution_count    ,qs.min_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.max_elapsed_time    ,qs.last_elapsed_time    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY avg_elapsed_time DESCOPTION (RECOMPILE);

查看哪个 SP 耗费了最多的 CPU 时间

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.total_worker_time AS [TotalWorkerTime]    ,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]    ,qs.execution_count    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]    ,qs.total_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_worker_time DESCOPTION (RECOMPILE);

查看哪个 SP 执行的逻辑读最多

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.total_logical_reads AS [TotalLogicalReads]    ,qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads]    ,qs.execution_count    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]    ,qs.total_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_logical_reads DESCOPTION (RECOMPILE);

逻辑读（Logical Read）主要是给 Memory 形成压力，可用于观察比较 Memory 运行情况。

查看哪个 SP 执行的物理读最多

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.total_physical_reads AS [TotalPhysicalReads]    ,qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads]    ,qs.execution_count    ,qs.total_logical_reads    ,qs.total_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()    AND qs.total_physical_reads > 0ORDER BY qs.total_physical_reads DESC    ,qs.total_logical_reads DESCOPTION (RECOMPILE);

物理读（Physical Read）主要是给磁盘 I/O 形成压力，可以用于观察比较 I/O 运行情况。

查看哪个 SP 执行的逻辑写最多

SELECT TOP (25) p.[name] AS [SP Name]    ,qs.total_logical_writes AS [TotalLogicalWrites]    ,qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites]    ,qs.execution_count    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]    ,qs.total_elapsed_time    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]    ,qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()    AND qs.total_logical_writes > 0ORDER BY qs.total_logical_writes DESCOPTION (RECOMPILE);

逻辑写（Logical Write）即与 Memory 相关，也与 Disk I/O 相关。通过数据可以判断出写 I/O 最昂贵的存储过程。

《人人都是 DBA》系列文章索引：

序号	名称
1	人人都是 DBA（I）SQL Server 体系结构
2	人人都是 DBA（II）SQL Server 元数据
3	人人都是 DBA（III）SQL Server 调度器
4	人人都是 DBA（IV）SQL Server 内存管理
5	人人都是 DBA（V）SQL Server 数据库文件
6	人人都是 DBA（VI）SQL Server 事务日志
7	人人都是 DBA（VII）B 树和 B+ 树
8	人人都是 DBA（VIII）SQL Server 页存储结构
9	人人都是 DBA（IX）服务器信息收集脚本汇编
10	人人都是 DBA（X）资源信息收集脚本汇编
11	人人都是 DBA（XI）I/O 信息收集脚本汇编
12	人人都是 DBA（XII）查询信息收集脚本汇编
13	人人都是 DBA（XIII）索引信息收集脚本汇编
14	人人都是 DBA（XIV）存储过程信息收集脚本汇编
15	人人都是 DBA（XV）锁信息收集脚本汇编

本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。

人人都是 DBA（XIV）存储过程信息收集脚本汇编

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 人人都是 DBA（XIV）存储过程信息收集脚本汇编