sql-server - 如何获取所有存储过程执行时间，杯子时间等

Question

 SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure
      ,max(cp.usecounts) execution_count
      ,sum(qs.total_elapsed_time) total_elapsed_time
      ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time
 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
 where  cp.objtype = 'proc'
 group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_elapsed_time) desc

我正在运行上面的查询，但无法找到在数据库中创建的所有 SP 列表详细信息。它只显示较少的 SP。

请帮帮我。等待您宝贵的回复。

Answer 1

你可以尝试这样的事情，它要复杂得多，但它绕过了这样一个事实，即并非每个存储过程都有统计信息:

CREATE TABLE #x(
    database_id INT, 
    DatabaseName SYSNAME, 
    SchemaName SYSNAME, 
    ProcedureName SYSNAME, 
    [object_id] INT);
DECLARE @sql NVARCHAR(MAX) = '';
SELECT 
    @sql = @sql + N'INSERT INTO #x SELECT ' + CONVERT(VARCHAR(50), d.database_id) + ', ''' + name + ''', s.name, p.name, p.[object_id]
    FROM ' + QUOTENAME(d.name) + '.sys.schemas AS s
    INNER JOIN ' + QUOTENAME(d.name) + '.sys.procedures AS p ON p.schema_id = s.schema_id;' FROM sys.databases d WHERE d.database_id > 4;
EXEC sp_executesql @sql;

WITH PlanData AS (
SELECT
    st.[dbid] AS database_id,
    st.objectid AS [object_id],
    DB_NAME(st.[dbid]) AS DBName,
    OBJECT_SCHEMA_NAME(st.objectid, st.[dbid]) AS SchemaName,
    OBJECT_NAME(st.objectid, st.[dbid]) AS StoredProcedure,
    MAX(cp.usecounts) AS execution_count,
    SUM(qs.total_elapsed_time) AS total_elapsed_time,
    SUM(qs.total_elapsed_time) / MAX(cp.usecounts) AS avg_elapsed_time
 FROM
    sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    LEFT JOIN sys.dm_exec_cached_plans cp on cp.plan_handle = qs.plan_handle
WHERE
    cp.objtype = 'PROC'
GROUP BY
    st.[dbid],
    st.objectid,
    DB_NAME(st.[dbid]),
    OBJECT_SCHEMA_NAME(st.objectid, st.[dbid]), 
    OBJECT_NAME(st.objectid, st.[dbid]))
SELECT 
    x.DatabaseName,
    x.SchemaName,
    x.ProcedureName,
    pd.execution_count,
    pd.total_elapsed_time,
    pd.avg_elapsed_time
FROM 
    #x x
    LEFT JOIN PlanData pd ON pd.database_id = x.database_id AND pd.[object_id] = x.[object_id];
DROP TABLE #x;

来自有关 sys.dm_exec_query_stats 的 MSDN，“ View 在缓存计划中的每个查询语句包含一行，并且行的生命周期与计划本身相关联。当计划从缓存中删除时，相应的行是从这个 View 中删除。”