大多数执行的存储过程？

本文介绍了大多数执行的存储过程？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

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

解决方案

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

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