当单个过程在另一个存储过程中被多次调用时，SQL Server 代码优化

Question

我有这个AuditLogProc存储过程，它执行不同的存储过程，并通过每次调用另一个存储AuditLogProcDetails过程来记录不同存储过程执行StartTime和EndTime，如下面的代码所示：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dba].[AuditLogProc]
    @Id AS INT,
    @ProcessId AS INT
AS
BEGIN 
    TRY
        SELECT @StartTime = GETDATE();
        EXEC AppPopTimeInc @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppPopTimeInc', @StartTime, GETDATE()
        END
        SELECT @StartTime = GETDATE();
        EXEC AppPopTimeIncDetails @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppPopTimeIncDetails', @StartTime, GETDATE()
        END
        SELECT @StartTime = GETDATE();
        EXEC AppObsResultsAggInc @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppObsResultsAggInc', @StartTime, GETDATE()
        END
        SELECT @StartTime = GETDATE();
        EXEC AppPricedDetailsInc @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppPricedDetailsInc', @StartTime, GETDATE()
        END
        SELECT @StartTime = GETDATE();
        EXEC AppPricedDetailsIncDetails @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppPricedDetailsIncDetails', @StartTime, GETDATE()
        END
        SELECT @StartTime = GETDATE();
        EXEC AppLoggedData @Id, @ProcessId;
        BEGIN
            EXEC [dba].[AuditLogProcDetails] @Id, @ProcessId, 'Exiting AppLoggedData', @StartTime, GETDATE()
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorState INT;
        SELECT 
            @ErrorMessage = error_message(),
            @errorState = error_state();
        RAISERROR (@ErrorMessage, @ErrorState);
    END CATCH
    RETURN

我的问题是：

1. 这是每次调用不同存储过程(即包括BEGIN和END块(后调用同一过程AuditLogProcDetails的最佳方法吗？

2. 由于AuditLogProcDetails过程被多次调用，如何更好地重构它？

Answer 1

对我来说，您似乎想要跟踪执行时间统计信息。为此，我可以向您推荐一种更好的方法：如果使用sys.dm_exec_procedure_stats而不是它，则不仅会得到执行时间，还会得到有关数据库的其他统计信息的整个分支。使用此方法只有一个缺点：

它显示缓存中存储过程的统计信息。要消除该缺点，您可以创建一个一分钟的作业来存储该视图的当前结果。稍后，当您想要检查查询的运行时间时，可以从存储结果的表中进行选择。

这样，您不仅可以获得有关存储过程的一些统计信息，还可以获得显示哪个过程成为瓶颈的历史数据，并且您不仅可以检查执行时间，还可以检查IO统计信息和其他很酷的东西。