跟踪
--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;