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

Question

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

Answer 1

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

要确定缓存中的存储过程执行了多少次，需要使用几个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数。物理读取是实际对物理磁盘驱动器进行的读取数；其中as逻辑读取和写入是针对缓冲区高速缓存中的内存中的高速缓存数据页的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"排序。

参考