首页 > 代码库 > 人人都是 DBA(XIV)存储过程信息收集脚本汇编
人人都是 DBA(XIV)存储过程信息收集脚本汇编
原文:人人都是 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》系列文章索引:
本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园,未经作者本人同意禁止任何形式的转载,任何自动或人为的爬虫转载行为均为耍流氓。
人人都是 DBA(XIV)存储过程信息收集脚本汇编
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。