SQL SERVER 存储过程执行日志记录方法（有案例）

查询历史执行总体情况

1. SELECT TOP 100 db_name(d.database_id) as DBName,
2. s.name as 存储名称,
3. s.type_desc as 存储类型,
4. d.cached_time as SP添加到缓存的时间,
5. d.last_execution_time as 上次执行SP的时间,
6. d.last_elapsed_time as [上次执行SP所用的时间（μs）],
7. d.total_elapsed_time as [完成此SP的执行所用的总时间（μs）],
8. d.total_elapsed_time/d.execution_count as [平均执行时间（μs）],
9. d.execution_count as 自上次编译以来所执行的次数
10. FROM SYS.procedures S JOIN SYS.dm_exec_procedure_stats D
11. ON S.object_id=D.object_id
12. WHERE S.NAME='SP_Name'
13. ORDER BY D.total_elapsed_time/D.execution_count DESC

μs/1000000=S

上面总统执行情况只能反馈一个整体状态，如果某个时间段执行较慢，被平均后则体现不到波动，所以需求用到下面的方法，记录到每次的执行情况。

流程概述

整个流程可以分为以下几个步骤：

1. 创建一个存储过程执行日志表。
2. 在需要记录日志的存储过程中添加日志记录代码。
3. 在存储过程执行前插入一条开始执行日志。
4. 在存储过程执行完成后更新执行日志的状态和结束时间。
5. 查询日志表以查看执行日志。

下面我们将逐步实现这些步骤。

步骤1：创建存储过程执行日志表

首先，我们需要创建一个用于记录存储过程执行日志的表。可以使用以下代码来创建一个简单的日志表：

CREATE TABLE dbo.StoredProcedureLog
(
    LogId INT IDENTITY(1,1) PRIMARY KEY,
    ProcedureName NVARCHAR(100),
    StartTime DATETIME,
    EndTime DATETIME,
    
    Status INT
)

这个日志表包含了以下几个字段：

• LogId: 一个自增的唯一标识符，用于标识每个日志记录。
• ProcedureName: 存储过程的名称。
• StartTime: 存储过程执行的开始时间。
• EndTime: 存储过程执行的结束时间。
• Status: 存储过程执行的状态，可以自定义状态码来表示成功或失败等。

步骤2：添加日志记录代码

在需要记录日志的存储过程中，我们需要添加一些代码来记录执行日志。可以使用以下代码来在存储过程中添加日志记录代码：

DECLARE @LogId INT

-- 在存储过程开始执行前插入一条开始执行的日志记录
INSERT INTO dbo.StoredProcedureLog (ProcedureName, StartTime, Status)
VALUES ('YourProcedureName', GETDATE(), 0)

-- 获取刚插入的日志记录的LogId
SELECT @LogId = SCOPE_IDENTITY()

-- 执行存储过程的代码
-- ...

-- 在存储过程执行完成后更新日志记录的状态和结束时间
UPDATE dbo.StoredProcedureLog
SET EndTime = GETDATE(),
    Status = 1
WHERE LogId = @LogId

在上面的代码中，我们首先通过插入一条开始执行的日志记录来记录存储过程的开始时间和状态。然后，我们获取刚插入的日志记录的LogId，以便在执行完成后更新日志记录的状态和结束时间。

步骤3：查询存储过程执行日志

在存储过程执行完毕后，我们可以查询存储过程执行日志表来查看执行日志。可以使用以下代码来查询存储过程执行日志：

SELECT *
FROM dbo.StoredProcedureLog

这个查询将返回存储过程执行日志表中的所有记录，你可以根据需要添加查询条件来过滤结果。

示例：