识别 SQL Server 2005 中最常用的存储过程变体的最快方法 [英] Quickest way to identify most used Stored Procedure variation in SQL Server 2005

问题描述

我想弄清楚是否有一种方法可以识别被调用次数最多的 SP 的版本".我有一个用一堆不同参数调用的 SP.我知道 SP 导致了一些问题并试图找出问题所在.除了捕获对 SP 的调用并手动筛选结果之外，是否可以使用分析器根据提供的参数对 SP 调用进行分组?

I am trying to figure out if there's a way to identify a "version" of a SP that gets called the most. I have an SP that gets called with a bunch of different parameters. I know that the SP is causing some issues and trying to pin point the problem. Besides capturing calls to the SP and manually sifting through the results, is it possible to use the profiler to group SP calls by supplied parameters?

我不是 DB(A/E)，只是网络开发人员，因此任何正确方向的提示/要点都会有所帮助.谢谢！

I'm not a DB(A/E), just a web dev, so any hints/points in the right direction will be helpful. Thanks!

重新编译 SP 没有多大帮助.

Recompiling the SP does not help much.

推荐答案

这将为您提供前 50 个最常用的 procs 和 procs 中的语句，从这里:显示 SQL Server 中最常用的 50 个存储过程

This will give you the top 50 most used procs and the statements in the procs, from here: Display the 50 most used stored procedures in SQL Server

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
and exists (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
and s.name = x.ProcName )
ORDER BY execution_count DESC

访问该链接以获取仅对 proc 名称的查询，但我认为这是一个更好的查询，因为它也为您提供了 proc 中的语句

Visit that link to grab the query for the proc name only, but I think this is a better query since it gives you the statements in the procs also

这篇关于识别 SQL Server 2005 中最常用的存储过程变体的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！