人人都是 DBA（XIV）存储过程信息收集脚本汇编-阿里云开发者社区

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

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

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

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

索引

获取存储过程 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_time
FROM 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 DESC
OPTION (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_time
FROM 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 DESC
OPTION (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_time
FROM 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 DESC
OPTION (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_time
FROM 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 DESC
OPTION (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_time
FROM 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 DESC
OPTION (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_time
FROM 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 > 0
ORDER BY qs.total_physical_reads DESC
    ,qs.total_logical_reads DESC
OPTION (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_time
FROM 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 > 0
ORDER BY qs.total_logical_writes DESC
OPTION (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）锁信息收集脚本汇编

本文转自匠心十年博客园博客，原文链接：http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sp_info_collection.html，如需转载请自行联系原作者