July 6, 2009 at 7:01 am
hello there
i am currently working on the trace below. This trace checks if there are any stored procedures that are slow (hits above 800000 reads) but that does not work for some stored procedures that are slow but have less reads. When i analize the database with a manueal trace some stored procedures take up tot 15/20 seconds. There for i want to change the script below and stop filterering on reads but on time.
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[sp_TracePromaetisUser] Script Date: 07/06/2009 11:42:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TracePromaetisUser]
@file_name nvarchar(155), -- NOTE: no file extension
@trace_id int output
AS
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5
/* Replace "InsertFileNameHere" with the passed-in file name. */
EXEC @rc = sp_trace_create @TraceID output, 0, @file_name, @maxfilesize, NULL
-- Get rid of the goto syntax.
IF (@rc != 0)
RAISERROR ('Error with the sp_trace_create', 16,1)
ELSE
BEGIN
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @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, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 10, @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, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- alleen trace in Promaetis (dbid = 9)
set @intfilter = 9
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
-- laat de Profiler erbuiten
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- only trace from Promaetisuser
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'Promaetisuser'
-- only actions from more than 800000 reads
set @bigintfilter = 800000
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter
-- Set the trace status to start.
EXEC sp_trace_setstatus @TraceID, 1
SET @trace_id = @traceID-- Return the ID of the trace.
END
July 6, 2009 at 8:03 am
You can use CPU time, but if you're looking for overall time, you need duration.
July 6, 2009 at 8:17 am
thank you for youre advise, can u tell me where to put this in de script?
July 6, 2009 at 2:13 pm
I assume you know how the script works and are not just running some random code on your server. There is a filter event for the duration, and you need to look that up in BOL to get the proper value for that item.
July 7, 2009 at 12:25 am
no its a script that allready works, but i would like to change it a little bit. 🙂
i found the following code in BOL i assume this is what you mean
TaskInfo.TaskDuration > [n]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply