SQLSERVER 如何跟踪执行的各项事务 语句跟踪

--创建存储过程
SET NOCOUNT ON;
USE master;
go

IF OBJECT_ID('dbo.sp_perfworkload_trace_start') IS NOT NULL
    DROP PROC dbo.sp_perfworkload_trace_start;
GO

CREATE PROC dbo.sp_perfworkload_trace_start
 @dbid      AS INT
,@tracefile AS NVARCHAR(254)
,@traceid   AS INT OUTPUT
AS
--创建一个队列
DECLARE @rc             AS INT;
DECLARE @maxfilesize    AS BIGINT;

SET @maxfilesize = 5120;   --MB

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL
IF (@rc != 0) GOTO ERROR;

--设置要跟踪的事件
DECLARE @on AS BIT;
SET @on=1;
--RPC:Completed     在完成了远程过程调用 (RPC) 时发生
--Columns:HostName, Reads(次), RowCounts, TextData, Writes(次), ApplicationName, CPU(ms), LoginName, SPID, Duration(ms), StartTime, EndTime
EXEC sp_trace_setevent @traceid, 10, 8, @on;
EXEC sp_trace_setevent @traceid, 10, 16, @on;
EXEC sp_trace_setevent @traceid, 10, 48, @on;
EXEC sp_trace_setevent @traceid, 10, 1, @on;
EXEC sp_trace_setevent @traceid, 10, 17, @on;
EXEC sp_trace_setevent @traceid, 10, 10, @on;
EXEC sp_trace_setevent @traceid, 10, 18, @on;
EXEC sp_trace_setevent @traceid, 10, 11, @on;
EXEC sp_trace_setevent @traceid, 10, 12, @on;
EXEC sp_trace_setevent @traceid, 10, 13, @on;
EXEC sp_trace_setevent @traceid, 10, 14, @on;
EXEC sp_trace_setevent @traceid, 10, 15, @on;
--SP:StmtCompleted     指示存储过程中的 Transact-SQL 语句已执行完毕
--Columns:HostName, Reads(次), RowCounts, TextData, Writes(次), ApplicationName, CPU(ms), LoginName, SPID, Duration(ms), StartTime, EndTime
EXEC sp_trace_setevent @traceid, 45, 8, @on;
EXEC sp_trace_setevent @traceid, 45, 16, @on;
EXEC sp_trace_setevent @traceid, 45, 48, @on;
EXEC sp_trace_setevent @traceid, 45, 1, @on;
EXEC sp_trace_setevent @traceid, 45, 17, @on;
EXEC sp_trace_setevent @traceid, 45, 10, @on;
EXEC sp_trace_setevent @traceid, 45, 18, @on;
EXEC sp_trace_setevent @traceid, 45, 11, @on;
EXEC sp_trace_setevent @traceid, 45, 12, @on;
EXEC sp_trace_setevent @traceid, 45, 13, @on;
EXEC sp_trace_setevent @traceid, 45, 14, @on;
EXEC sp_trace_setevent @traceid, 45, 15, @on;
--SQL:StmtCompleted     在完成了 Transact-SQL 语句时发生
--Columns:HostName, Reads(次), RowCounts, TextData, Writes(次), ApplicationName, CPU(ms), LoginName, SPID, Duration(ms), StartTime, EndTime
EXEC sp_trace_setevent @traceid, 41, 8, @on;
EXEC sp_trace_setevent @traceid, 41, 16, @on;
EXEC sp_trace_setevent @traceid, 41, 48, @on;
EXEC sp_trace_setevent @traceid, 41, 1, @on;
EXEC sp_trace_setevent @traceid, 41, 17, @on;
EXEC sp_trace_setevent @traceid, 41, 10, @on;
EXEC sp_trace_setevent @traceid, 41, 18, @on;
EXEC sp_trace_setevent @traceid, 41, 11, @on;
EXEC sp_trace_setevent @traceid, 41, 12, @on;
EXEC sp_trace_setevent @traceid, 41, 13, @on;
EXEC sp_trace_setevent @traceid, 41, 14, @on;
EXEC sp_trace_setevent @traceid, 41, 15, @on;

--设置筛选器
DECLARE @intfilter AS INT;
DECLARE @bigintfilter AS BIGINT;
--应用程序名称筛选器
EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';    --第10列 ApplicationName NOT LIKE
--数据库ID筛选器
EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;   --第3列 DatabaseID  等于 @dbid

--启动跟踪
EXEC sp_trace_setstatus @traceid, 1;

--打印跟踪ID和文件名称供以后引用
PRINT 'Trace ID:' + CAST(@traceid AS VARCHAR(10))
    + ', Trace File：''' + @tracefile + '''';
   
GOTO finish;

ERROR:
PRINT 'Error Code:' + CAST(@rc AS VARCHAR(10));

finish:
GO