数据库跟踪

跟踪

 

--1 注意事项：不要用SQL Server Profiler图形化跟踪；不要把跟踪数据写到数据库表；

不要把跟踪文件写到包含数据库文件的磁盘上；选择事件类和数据列，只跟踪需要的信息，

移除所有默认项和非必选项；列用筛选条件，如数据库ID；

 

 

--2 需要跟踪的事件类和字段

SP:Completed 

SP:StmtCompleted

RPC:Completed

SQL:StmtCompleted

 

TextData  Duration host application login

 

--3 用于创建跟踪的系统存储过程

--创建跟踪定义。新的跟踪将处于停止状态。

sp_trace_create

 

 

--创建用户定义事件

sp_trace_generateevent

 

--在跟踪中添加或删除事件或事件列。

sp_trace_setevent

 

--将筛选应用于跟踪。

sp_trace_setfilter

 

 

--修改指定跟踪的当前状态

--0 停止指定的跟踪。

--1 启动指定的跟踪。

--2 关闭指定的跟踪并从服务器中删除其定义。

sp_trace_setstatus

 

 

--以表格格式返回一或多个跟踪文件的内容

SELECT * INTO temp_trc

FROM fn_trace_gettable('c:/temp/my_trace.trc', default);

 

--4 跟踪存储过程

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

-- Creation script for the sp_perfworkload_trace_start stored procedure

CREATE PROC dbo.sp_perfworkload_trace_start

  @dbid      AS INT,

  @tracefile AS NVARCHAR(254),

  @traceid   AS INT OUTPUT

AS

-- Create a Queue

DECLARE @rc          AS INT;

DECLARE @maxfilesize AS BIGINT;

 

SET @maxfilesize = 5;

 

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL

IF (@rc != 0) GOTO error;

 

-- Client side File and Table cannot be scripted

 

-- Set the events

DECLARE @on AS BIT;

SET @on = 1;

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 3, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 43, 1, @on

exec sp_trace_setevent @TraceID, 43, 3, @on

exec sp_trace_setevent @TraceID, 43, 11, @on

exec sp_trace_setevent @TraceID, 43, 35, @on

exec sp_trace_setevent @TraceID, 43, 12, @on

exec sp_trace_setevent @TraceID, 43, 28, @on

exec sp_trace_setevent @TraceID, 43, 13, @on

exec sp_trace_setevent @TraceID, 45, 1, @on

exec sp_trace_setevent @TraceID, 45, 3, @on

exec sp_trace_setevent @TraceID, 45, 11, @on

exec sp_trace_setevent @TraceID, 45, 35, @on

exec sp_trace_setevent @TraceID, 45, 12, @on

exec sp_trace_setevent @TraceID, 45, 28, @on

exec sp_trace_setevent @TraceID, 45, 13, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 3, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 35, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 41, 1, @on

exec sp_trace_setevent @TraceID, 41, 3, @on

exec sp_trace_setevent @TraceID, 41, 11, @on

exec sp_trace_setevent @TraceID, 41, 35, @on

exec sp_trace_setevent @TraceID, 41, 12, @on

exec sp_trace_setevent @TraceID, 41, 13, @on

 

-- Set the Filters

DECLARE @intfilter AS INT;

DECLARE @bigintfilter AS BIGINT;

-- Application name filter

EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';

-- Database ID filter

EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

 

-- Set the trace status to start

EXEC sp_trace_setstatus @traceid, 1;

 

-- Print trace id and file name for future references

PRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))

  + ', Trace File: ''' + @tracefile + '''';

 

GOTO finish;

 

error:

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

 

finish:

GO

 

 

 --5 启动

DECLARE @dbid AS INT, @traceid AS INT;

SET @dbid = DB_ID('Performance');

 

EXEC dbo.sp_perfworkload_trace_start

  @dbid      = @dbid,

  @tracefile = 'D:/trc/Perfworkload20080828.trc',

  @traceid   = @traceid OUTPUT;

 

--6 关闭

EXEC sp_trace_setstatus 2, 0;

EXEC sp_trace_setstatus 2, 2;

 

--7 加载到表中

SELECT IDENTITY(int, 1, 1) AS RowNumber,

cast(TextData as varchar(MAX)) as tsql_code,Duration, StartTime,DatabaseID,DatabaseName,LoginName, SPID,  EndTime,ServerName, EventClass, ObjectType

INTO dbo.workload

FROM fn_trace_gettable('D:/trc/Perfworkload20080828.trc', null);

GO

 

select * from dbo.workload

 

--8 分析查找最需要优化的sql语句

 

--签名函数

dbo.fn_SQLSigTSQL

 

-- 添加函数模板的校验和

ALTER TABLE dbo.Workload ADD cs INT NOT NULL DEFAULT (0);

GO

UPDATE dbo.Workload

  SET cs = CHECKSUM(dbo.fn_SQLSigTSQL( tsql_code,4000));

 

CREATE CLUSTERED INDEX idx_cl_cs ON dbo.Workload(cs);

 

-- 创建临时表

IF OBJECT_ID('tempdb..#AggQueries') IS NOT NULL

  DROP TABLE #AggQueries;

GO

 

SELECT cs, SUM(duration) AS total_duration,

  100. * SUM(duration) / SUM(SUM(duration)) OVER() AS pct,

  ROW_NUMBER() OVER(ORDER BY SUM(duration) DESC) AS rn

INTO #AggQueries

FROM dbo.Workload

GROUP BY cs;

 

CREATE CLUSTERED INDEX idx_cl_cs ON #AggQueries(cs);

 

-- 查询

select total_duration,pct,rn,sig,tsql_code from #AggQueries as a cross apply

(

SELECT TOP(1) tsql_code, dbo.fn_SQLSigTSQL( tsql_code,4000) AS sig

     FROM dbo.Workload AS W

     WHERE W.cs = a.cs

) AS S

order by a.rn

 

-- 查询

WITH RunningTotals AS

(

  SELECT AQ1.cs,

    CAST(AQ1.total_duration / 1000.

      AS DECIMAL(12, 2)) AS total_s,

    CAST(SUM(AQ2.total_duration) / 1000.

      AS DECIMAL(12, 2)) AS running_total_s,

    CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct,

    CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct,

    AQ1.rn

  FROM #AggQueries AS AQ1

    JOIN #AggQueries AS AQ2

      ON AQ2.rn <= AQ1.rn

  GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn

  HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold

--  OR AQ1.rn <= 5

)

SELECT RT.rn, RT.pct,RT.cs,RT.total_s,RT.running_total_s,RT.run_pct , W.tsql_code

FROM RunningTotals AS RT

  JOIN dbo.Workload AS W

    ON W.cs = RT.cs

ORDER BY RT.rn;

 

 

-- 查询

WITH RunningTotals AS

(

  SELECT AQ1.cs,

    CAST(AQ1.total_duration / 1000.

      AS DECIMAL(12, 2)) AS total_s,

    CAST(SUM(AQ2.total_duration) / 1000.

      AS DECIMAL(12, 2)) AS running_total_s,

    CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct,

    CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct,

    AQ1.rn

  FROM #AggQueries AS AQ1

    JOIN #AggQueries AS AQ2

      ON AQ2.rn <= AQ1.rn

  GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn

  HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold

)

SELECT RT.rn, RT.pct, S.sig, S.tsql_code AS sample_query

FROM RunningTotals AS RT

  CROSS APPLY

    (SELECT TOP(1) tsql_code, dbo.fn_SQLSigTSQL( tsql_code,4000) AS sig

     FROM dbo.Workload AS W

     WHERE W.cs = RT.cs) AS S

ORDER BY RT.rn;