执行次数最多的存储过程?

2021-12-20 00:00:00 sql database tsql sql-server stored-procedures

我们在应用程序中创建了这么多低效的存储过程，我们总是推迟以提高效率，直到我们的数据库性能出现严重问题.

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?

推荐答案

使用:

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

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

相关文章