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

浏览数：16 / 时间：2015年06月12日

什么？有个 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_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）锁信息收集脚本汇编