存储过程 xyz 或表值函数的使用频率 - SQL Server 2008 R2
我们有许多视图、存储过程、表值和标量函数.
We have a number of Views, Stored Procs, Table Values and Scalar functions.
我们如何查看从 SQL Server 中调用它们的频率甚至多少次?
How can we see how often or even how many times these have been called from within SQL Server?
我们是否需要编辑每一个以在每次调用时更新一个表以获得此信息，或者 SQL Server 是否将此信息保存在某处?
Do we need to edit each one to update a table on each call to get this, or does SQL server keep this information somewhere?
推荐答案
这是 Glenn Berry 的 DMV 查询之一.它计算缓存存储过程执行了多少次(由当前数据库过滤):
This is one of Glenn Berry's DMV queries. It counts how many times a cached stored procedure has been executed (filtered by the current database):
SELECT TOP(25) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
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
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
注意:SQL Server 仅保留上次服务重启后的信息，并且在内存压力下也可能丢弃信息.
Note: SQL Server keeps info only since last service restart and may also discard info under memory pressure.
还请注意，仅执行计数并不能说明全部情况.识别成本最高的查询并改进它们通常会更好.我通常从最高的逻辑读取开始(这是 Glenn 的另一个):
Also note that execution count alone does not tell the whole picture. It's often better to identify the most costly queries and improve them. I usually start with highest logical reads (this is another of Glenn's):
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(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
[另外，请注意:您可能有定期安排的流程(例如每月一次).因此，并非 100% 可以识别出使用这些未调用的存储过程.]
[Also, note: you might have processes that are periodically scheduled (say once per month). So, it's not 100% possible to identify stored procedures that are not called using these.]
相关文章