Блог

  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.

Пожалуйста, помогите мне. Жду вашего ценного ответа.

Вы могли бы попробовать что-то подобное, намного сложнее, но это связано с тем, что не каждая хранимая процедура будет иметь статистику:

 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;
 

Из MSDN относительно sys.dm_exec_query_stats: «Представление содержит одну строку для каждого оператора запроса в кэшированном плане, а время жизни строк привязано к самому плану. Когда план удаляется из кэша, соответствующие строки удаляются из этого представления. »