时间戳

首页 > 解决方案 > 获取数据库在过去 24 小时内执行的所有存储过程的计数

sql-server - 获取数据库在过去 24 小时内执行的所有存储过程的计数

问题描述

我想知道 SQL Server 在过去 24 小时内执行了多少个存储过程以及执行了多少时间来获取数据？

标签： sql-serverdatabasestored-procedures

解决方案

如何获取存储过程的执行次数？

要确定缓存中的存储过程执行了多少次，您需要使用几个 DMV 和一个动态管理函数 (DMF)。缓存计划的 plan_handle 用于将 DMV 连接在一起并检索 DMF 的记录。要获取每个缓存 SP 的执行计数，您可以运行以下代码：

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by max(cp.usecounts)

这里我使用了“sys.dm_exec_cached_plans”DMV的plan_handle来获取对象类型。我使用对象类型来识别存储过程缓存计划。“sys.dm_exec_cached_plans”DMV 的“usecounts”列标识了自上次编译 SP 以来每个带有 cached_plan（或 SP）的语句的执行次数，我称之为“Execution_count”。我将 plan_handle 与 CROSS APPLY 运算符结合使用，以使用表值 DMF“sys.dm_exec_sql_text”返回对象信息（DBName、SchemaName 和 ObjectName）。此 SELECT 语句的输出按“Execution_count”排序，因此执行次数最多的 SP 将首先显示。

确定哪个 SP 使用最多的 CPU、I/O 或具有最长的持续时间。

了解哪些 SP 经常执行是有用的信息，尽管从性能的角度来看，您可能想知道哪个 SP 消耗的 CPU 资源最多。或者您可能对哪个 SP 运行时间最长，或者哪个 SP 执行最多的物理 I/O 操作感兴趣？通过修改上面的命令，我们可以很容易地回答这些问题中的每一个。

如果要显示消耗 CPU 资源最多的 SP，可以运行以下 TSQL 命令：

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
      ,sum(qs.total_worker_time) total_cpu_time
      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time
 
 FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_worker_time) desc

“sys.dm_exec_query_stats”视图包含“total_worker_time”列，它是给定缓存查询计划已执行的总微秒数。请记住，缓存的计划有时会从内存中删除并替换为较新的计划。因此，SP 消耗最多 CPU 的统计信息仅考虑运行此 T-SQL 时缓存中的那些计划的统计信息。

要确定哪个 SP 执行了最多的 I/O 请求，您可以运行以下 TSQL 代码：

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure
      ,max(cp.usecounts) execution_count
      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO
      ,sum(qs.total_physical_reads) total_physical_reads
      ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read    
      ,sum(qs.total_logical_reads) total_logical_reads
      ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read  
      ,sum(qs.total_logical_writes) total_logical_writes
      ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes  
 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

这里我展示了总的物理和逻辑读 I/O，加上逻辑写 I/O。此外，我计算了每个 SP 每次执行的平均 I/O 数。物理读取是对物理磁盘驱动器实际进行的读取次数；其中逻辑读取和写入是针对缓冲区高速缓存中内存中高速缓存数据页的 I/O 数量。因此，通过将物理和逻辑 I/O 相加，我能够计算出每个 SP 的总 I/O。

要确定哪个 SP 的执行时间最长，我可以使用以下 TSQL 代码：

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure
      ,max(cp.usecounts) execution_count
      ,sum(qs.total_elapsed_time) total_elapsed_time
      ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time
 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_elapsed_time) desc

在这个 TSQL 中，我通过按数据库、模式和对象名称汇总“sys.dm_exec_sql_query_stats”DMV 中的“total_elapsed_time”来计算每个 SP 的持续时间。我还在计算每个 SP 每次执行的平均经过时间。我对输出进行排序，以便首先显示总持续时间最长的 SP。如果您有兴趣确定平均持续时间最长的 SP，您只需更改“ORDER BY”子句以按“avg_elapsed_time”排序。

参考

推荐阅读