Performance Trace Helpers

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 4, 2011 at 5:11 am

  #244707

  I have created these SPs as helpers for me when performance tuning/ measuring.

  Sample usage:

  EXEC helpers.dbo.StartPerfTrace;

  GO

  DECLARE @bb int;

  SELECT top 100000 @bb=ROW_NUMBER() OVER(ORDER BY @@SPID)

  FROM syscolumns,syscolumns a

  GO 10

  EXEC helpers.dbo.StopPerfTrace;

  Sample output:

  

  I find these very useful, but wondered if anyone had any good suggestions on other useful information I could include or just general critique of the techniques used.

  If you can find time to take a look and maybe have a play I would appreciate it, thanks

  USE [helpers]

  GO

  /****** Object: StoredProcedure [dbo].[StartPerfTrace] Script Date: 02/04/2011 11:59:33 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROC [dbo].[StartPerfTrace]

  AS

  DECLARE @TraceID int

  DECLARE @rc int, @on BIT

  DECLARE @Name nvarchar(245)

  SELECT

  @Name = 'D:\Dump\' + REPLACE (REPLACE ('PerfTrace' + convert (VARCHAR(10), @@spid) + ' ' + convert (VARCHAR(30), getdate (), 6) + ' ' + convert (VARCHAR(30), getdate (), 8), ' ', ''), ':', '')

  EXEC @rc = sp_trace_create @TraceID output, 0, @Name

  -- Save the trace ID for later - used in the StopPerfTrace Proc.

  IF OBJECT_ID('helpers.dbo.traces') IS NULL

  EXEC('USE helpers; CREATE TABLE traces (spid int not null,traceid int not null);');

  INSERT

  helpers.dbo.traces (spid,

  traceid)

  VALUES

  (@@SPID,

  @TraceID);

  -- Set the events and data columns you need to capture.

  SELECT

  @on = 1;

  -- 41 is SQL:StmtCompleted event. 1 is TextData column.

  EXEC sp_trace_setevent @TraceID, 41, 1, @on; -- TextData

  EXEC sp_trace_setevent @TraceID, 41, 13, @on; -- Duration

  EXEC sp_trace_setevent @TraceID, 41, 16, @on; -- Reads

  EXEC sp_trace_setevent @TraceID, 41, 17, @on; -- Writes

  EXEC sp_trace_setevent @TraceID, 41, 18, @on; -- Cpu

  EXEC sp_trace_setevent @TraceID, 162, 1, @on; -- User Errors - Can then use RAISERROR to put messages in the trace

  DECLARE @pid INT = CONVERT(INT,@@SPID);

  -- Set filter to include the current SPID

  EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @pid;

  -- Set filter to exclude RAISERROR statements

  -- I use these to add messages to the trace so don't want to see the command being called

  EXEC sp_trace_setfilter @TraceID, 1, 0, 7, N'RAISERROR%';

  -- Start Trace (status 1 = start)

  EXEC @rc = sp_trace_setstatus @TraceID, 1;

  USE [helpers]

  GO

  /****** Object: StoredProcedure [dbo].[StopPerfTrace] Script Date: 02/04/2011 12:07:06 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROC [dbo].[StopPerfTrace]

  AS

  DECLARE @TraceID int;

  SELECT

  @TraceID = traceid

  FROM

  helpers.dbo.traces

  WHERE

  spid = @@SPID;

  DELETE helpers.dbo.traces

  WHERE

  spid = @@SPID;

  DECLARE @file nvarchar(247)

  -- Populate a variable with the trace_id of the current trace

  SELECT

  @file = [path]

  FROM

  sys.traces

  WHERE

  id = @TraceID

  -- First stop the trace.

  EXEC sp_trace_setstatus @TraceID, 0

  -- Close and then delete its definition from SQL Server.

  EXEC sp_trace_setstatus @TraceID, 2

  -- Save to #tracestats table

  SELECT

  row_number () OVER (ORDER BY @@spid) AS RowNumber,

  TextData,

  convert (NUMERIC(14, 3), Duration / 1000) [Duration(millisec)],

  Cpu,

  Reads,

  Writes,

  avg (convert (NUMERIC(14, 3), Duration / 1000)) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Duration_Avg,

  avg (Cpu) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Cpu_Avg,

  avg (Reads) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Reads_Avg,

  avg (Writes) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Writes_Avg

  FROM

  fn_trace_gettable (@file, DEFAULT)

  WHERE

  TextData IS NOT NULL;

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]