---------------------------------------------------------------------
-- Trace Performance Workload 建立追踪记录
---------------------------------------------------------------------
-- Trace Performance Workload 建立追踪记录
---------------------------------------------------------------------
SET NOCOUNT ON;
USE master;
GO
USE master;
GO
IF OBJECT_ID('dbo.PerfworkloadTraceStart', 'P') IS NOT NULL
DROP PROC dbo.PerfworkloadTraceStart;
GO
DROP PROC dbo.PerfworkloadTraceStart;
GO
CREATE PROC dbo.PerfworkloadTraceStart
@dbid AS INT,
@tracefile AS NVARCHAR(245),
@traceid AS INT OUTPUT
AS
@dbid AS INT,
@tracefile AS NVARCHAR(245),
@traceid AS INT OUTPUT
AS
-- Create a Queue
DECLARE @rc AS INT;
DECLARE @maxfilesize AS BIGINT;
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;
IF (@rc != 0) GOTO error;
-- Set the events
DECLARE @on AS BIT;
SET @on = 1;
DECLARE @on AS BIT;
SET @on = 1;
-- RPC:Completed
exec sp_trace_setevent @traceid, 10, 15, @on;
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, 6, @on;
exec sp_trace_setevent @traceid, 10, 14, @on;
exec sp_trace_setevent @traceid, 10, 15, @on;
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, 6, @on;
exec sp_trace_setevent @traceid, 10, 14, @on;
-- SP:Completed
exec sp_trace_setevent @traceid, 43, 15, @on;
exec sp_trace_setevent @traceid, 43, 8, @on;
exec sp_trace_setevent @traceid, 43, 48, @on;
exec sp_trace_setevent @traceid, 43, 1, @on;
exec sp_trace_setevent @traceid, 43, 10, @on;
exec sp_trace_setevent @traceid, 43, 11, @on;
exec sp_trace_setevent @traceid, 43, 12, @on;
exec sp_trace_setevent @traceid, 43, 13, @on;
exec sp_trace_setevent @traceid, 43, 6, @on;
exec sp_trace_setevent @traceid, 43, 14, @on;
exec sp_trace_setevent @traceid, 43, 15, @on;
exec sp_trace_setevent @traceid, 43, 8, @on;
exec sp_trace_setevent @traceid, 43, 48, @on;
exec sp_trace_setevent @traceid, 43, 1, @on;
exec sp_trace_setevent @traceid, 43, 10, @on;
exec sp_trace_setevent @traceid, 43, 11, @on;
exec sp_trace_setevent @traceid, 43, 12, @on;
exec sp_trace_setevent @traceid, 43, 13, @on;
exec sp_trace_setevent @traceid, 43, 6, @on;
exec sp_trace_setevent @traceid, 43, 14, @on;
-- SP:StmtCompleted
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, 6, @on;
exec sp_trace_setevent @traceid, 45, 14, @on;
exec sp_trace_setevent @traceid, 45, 15, @on;
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, 6, @on;
exec sp_trace_setevent @traceid, 45, 14, @on;
exec sp_trace_setevent @traceid, 45, 15, @on;
-- SQL:BatchCompleted
exec sp_trace_setevent @traceid, 12, 15, @on;
exec sp_trace_setevent @traceid, 12, 8, @on;
exec sp_trace_setevent @traceid, 12, 16, @on;
exec sp_trace_setevent @traceid, 12, 48, @on;
exec sp_trace_setevent @traceid, 12, 1, @on;
exec sp_trace_setevent @traceid, 12, 17, @on;
exec sp_trace_setevent @traceid, 12, 6, @on;
exec sp_trace_setevent @traceid, 12, 10, @on;
exec sp_trace_setevent @traceid, 12, 14, @on;
exec sp_trace_setevent @traceid, 12, 18, @on;
exec sp_trace_setevent @traceid, 12, 11, @on;
exec sp_trace_setevent @traceid, 12, 12, @on;
exec sp_trace_setevent @traceid, 12, 13, @on;
exec sp_trace_setevent @traceid, 12, 15, @on;
exec sp_trace_setevent @traceid, 12, 8, @on;
exec sp_trace_setevent @traceid, 12, 16, @on;
exec sp_trace_setevent @traceid, 12, 48, @on;
exec sp_trace_setevent @traceid, 12, 1, @on;
exec sp_trace_setevent @traceid, 12, 17, @on;
exec sp_trace_setevent @traceid, 12, 6, @on;
exec sp_trace_setevent @traceid, 12, 10, @on;
exec sp_trace_setevent @traceid, 12, 14, @on;
exec sp_trace_setevent @traceid, 12, 18, @on;
exec sp_trace_setevent @traceid, 12, 11, @on;
exec sp_trace_setevent @traceid, 12, 12, @on;
exec sp_trace_setevent @traceid, 12, 13, @on;
-- SQL:StmtCompleted
exec sp_trace_setevent @traceid, 41, 15, @on;
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, 6, @on;
exec sp_trace_setevent @traceid, 41, 14, @on;
exec sp_trace_setevent @traceid, 41, 15, @on;
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, 6, @on;
exec sp_trace_setevent @traceid, 41, 14, @on;
-- Set the Filters
-- 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;
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;
EXEC sp_trace_setstatus @traceid, 1;
-- Print trace id and file name for future references
PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))
+ ', Trace File: ''' + @tracefile + '.trc''';
PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))
+ ', Trace File: ''' + @tracefile + '.trc''';
GOTO finish;
error:
PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));
PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));
finish:
GO
GO
-- Start the trace 开始追踪
DECLARE @dbid AS INT, @traceid AS INT;
SET @dbid = DB_ID('Performance');
DECLARE @dbid AS INT, @traceid AS INT;
SET @dbid = DB_ID('Performance');
EXEC master.dbo.PerfworkloadTraceStart
@dbid = @dbid,
@tracefile = 'c:\temp\Perfworkload 20130515',
@traceid = @traceid OUTPUT;
GO
@dbid = @dbid,
@tracefile = 'c:\temp\Perfworkload 20130515',
@traceid = @traceid OUTPUT;
GO
-- Stop the trace (assuming trace id was 2)停止追踪
EXEC sp_trace_setstatus 2, 0;
EXEC sp_trace_setstatus 2, 2;
GO
EXEC sp_trace_setstatus 2, 0;
EXEC sp_trace_setstatus 2, 2;
GO
---------------------------------------------------------------------
-- Analyze Trace Data
---------------------------------------------------------------------
-- Analyze Trace Data
---------------------------------------------------------------------
-- Load trace data to table 分析追踪日志
SET NOCOUNT ON;
USE Performance;
IF OBJECT_ID('dbo.Workload', 'U') IS NOT NULL DROP TABLE dbo.Workload;
GO
SET NOCOUNT ON;
USE Performance;
IF OBJECT_ID('dbo.Workload', 'U') IS NOT NULL DROP TABLE dbo.Workload;
GO
SELECT CAST(TextData AS NVARCHAR(MAX)) AS tsql_code,
Duration AS duration
INTO dbo.Workload
FROM sys.fn_trace_gettable('c:\Perfworkload 20130515.trc.trc', NULL) AS T
WHERE Duration > 0
AND EventClass IN(41, 45);
GO
Duration AS duration
INTO dbo.Workload
FROM sys.fn_trace_gettable('c:\Perfworkload 20130515.trc.trc', NULL) AS T
WHERE Duration > 0
AND EventClass IN(41, 45);
GO
-- Aggregate trace data by query 追踪日志查询
SELECT
tsql_code,
SUM(duration) AS total_duration,COUNT(tsql_code) 次数
FROM dbo.Workload
GROUP BY tsql_code;
SELECT
tsql_code,
SUM(duration) AS total_duration,COUNT(tsql_code) 次数
FROM dbo.Workload
GROUP BY tsql_code;
-- Aggregate trace data by query prefix追踪日志分析
SELECT
SUBSTRING(tsql_code, 1, 100) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 100);
SELECT
SUBSTRING(tsql_code, 1, 100) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 100);
-- Adjust substring length
SELECT
SUBSTRING(tsql_code, 1, 94) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 94);
SELECT
SUBSTRING(tsql_code, 1, 94) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 94);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20630340/viewspace-761873/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20630340/viewspace-761873/