大多数执行的存储过程？ [英] Most Executed Stored Procedure?

问题描述

我们在我们的应用程序中创建了这么多低效的存储过程，我们总是推迟使它更高效，直到我们对数据库性能有严重的问题。

想要通过最常执行的存储过程逐个修复它。

最好的办法是找出哪个存储过程是最执行的？

解决方案

是否有脚本可以显示哪个存储过程执行得最多？ >使用：

  SELECT TOP 10 
 qt.TEXT AS'SP Name'，
 SUBSTRING .text，qs.statement_start_offset / 2，CASE WHEN（qs.statement_end_offset = -1）THEN LEN（qt.text）ELSE（qs.statement_end_offset  -  qs.statement_start_offset）/ 2 END）AS actual_query，
 qs.execution_count AS'Execution Count'，
 qs.total_worker_time / qs.execution_count AS'AvgWorkerTime'，
 qs.total_worker_time AS'TotalWorkerTime'，
 qs.total_physical_reads AS'PhysicalReads'，
 qs.creation_time'CreationTime'，
 qs.execution_count / DATEDIFF（Second，qs.creation_time，GETDATE（））AS'Calls / Second'
 FROM sys.dm_exec_query_stats AS qs 
 CROSS APPLY sys .dm_exec_sql_text（qs.sql_handle）AS qt 
 WHERE qt.dbid =（SELECT dbid 
 FROM sys.sysdatabases 
 WHERE name ='[您的数据库名称]'）
 ORDER BY qs.total_physical_reads DESC 
  

参考： SQL SERVER - 2005 - 查找最高/最常用的存储过程

We created so many inefficient stored procedure in our application, we always postpone to make it more efficient until we have serious problem with database performance.

Now, I am thinking to fix it one by one order by most often executed stored procedure.

What is the best way to figure out which stored procedure is the most executed?

Is there a script that can show which stored procedure is the most executed?

解决方案

Use:

SELECT TOP 10 
       qt.TEXT AS 'SP Name',
       SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset - qs.statement_start_offset)/2 END) AS actual_query,
       qs.execution_count AS 'Execution Count',
       qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
       qs.total_worker_time AS 'TotalWorkerTime',
       qs.total_physical_reads AS 'PhysicalReads',
       qs.creation_time 'CreationTime',
       qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
  FROM sys.dm_exec_query_stats AS qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 WHERE qt.dbid = (SELECT dbid
                    FROM sys.sysdatabases
                   WHERE name = '[your database name]')
ORDER BY qs.total_physical_reads DESC

Reference: SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure

这篇关于大多数执行的存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！