SQL Server (2012+) 已执行过程统计信息

Question

我需要执行的存储过程统计信息：逻辑读取、物理读取、持续时间、写入、行计数。或者，也许我们可以简化这一点。拥有相同的会话数据就足够了。类似于将统计信息IO设置为开/关，但是，我需要将结果保存到表中。因此，设置统计信息IO不是选项。这里有什么知识吗？

我期待这样的东西：

EXEC [my_proc];
INSERT INTO [sp_exec_stats]
([lreads], [preads], ..., [rcount])
VALUES(?, ?, ..., ?);

Answer 1

最后，我自己找到了解决方案。请使用这种方法(整个测试脚本，"选择"部分是重点(：

CREATE PROCEDURE [my_dummy_sp]
    @rowcount INT   OUTPUT
AS
BEGIN
    SELECT TOP 10 * FROM [sys].[objects];
    SET @rowcount = @@ROWCOUNT;
    RETURN 0; 
END;    
GO

DECLARE @sp_exec_stats TABLE
(
     [rec_id]           BIGINT  IDENTITY(1, 1)
    ,[sp_name]          NVARCHAR(256)
    ,[db_name]          NVARCHAR(256)
    ,[sys_dt]           DATETIMEOFFSET(7)
    ,[logical_reads]    INT
    ,[physical_reads]   INT
    ,[duration]         INT
    ,[writes]           INT
    ,[rowcount]         INT
    ,[cpu_time]         INT
);
DECLARE @rowcount   INT;
EXECUTE [my_dummy_sp]
     @rowcount = @rowcount OUTPUT;
INSERT INTO @sp_exec_stats
(
     [sp_name]  
    ,[db_name]          
    ,[sys_dt]           
    ,[logical_reads]    
    ,[physical_reads]   
    ,[duration]         
    ,[writes]           
    ,[rowcount] 
    ,[cpu_time]     
)
SELECT TOP 1
     [sp_name]          = 'my_dummy_sp'
    ,[db_name]          = DB_NAME()     
    ,[sys_dt]           = [last_execution_time] 
    ,[logical_reads]    = [last_logical_reads]  
    ,[physical_reads]   = [last_physical_reads] 
    ,[duration]         = [last_elapsed_time]   
    ,[writes]           = [last_logical_writes] 
    ,[rowcount]         = @rowcount
    ,[cpu_time]         = [last_worker_time]    
FROM
    [sys].[dm_exec_procedure_stats]
WHERE
        [object_id]     =   OBJECT_ID('my_dummy_sp')    
ORDER BY 
     [last_execution_time] DESC;

DROP PROCEDURE [my_dummy_sp];
SELECT * FROM @sp_exec_stats;