October 18, 2007 at 10:15 am
Hi All,
I'm going to end up in the loony bin if I don't get this right! Please see the SST below:
DECLARE
@TraceID INT,
@Options INT,
@TraceFile NVARCHAR(245),
@MaxFileSize BIGINT,
@StopTime DATETIME,
@Value BIGINT,
@On Bit
SET @Options = 2
SET @TraceFile = N'C:\MSSQL\Trace\Trace_File'
SET @MaxFileSize = 512
SET @StopTime = DATEADD(DAY, 2, GETDATE())
SET @Value = 1000
SET @On = 1
EXEC sp_trace_create @TraceID OUTPUT, @Options, @TraceFile, @MaxFileSize, @StopTime
EXEC sp_trace_setevent @TraceID, 10, 1, @On-- TextData
EXEC sp_trace_setevent @TraceID, 10, 6, @On-- NTUserName
EXEC sp_trace_setevent @TraceID, 10, 10, @On-- ApplicationName
EXEC sp_trace_setevent @TraceID, 10, 11, @On-- LoginName
EXEC sp_trace_setevent @TraceID, 10, 12, @On-- SPID
EXEC sp_trace_setevent @TraceID, 10, 13, @On-- Duration*******
EXEC sp_trace_setevent @TraceID, 10, 14, @On-- StartTime
EXEC sp_trace_setevent @TraceID, 10, 15, @On-- EndTime
EXEC sp_trace_setevent @TraceID, 10, 16, @On-- Reads
EXEC sp_trace_setevent @TraceID, 10, 17, @On-- Writes
EXEC sp_trace_setevent @TraceID, 10, 40, @On-- DBUserName
EXEC sp_trace_setevent @TraceID, 12, 1, @On-- TextData
EXEC sp_trace_setevent @TraceID, 12, 6, @On-- NTUserName
EXEC sp_trace_setevent @TraceID, 12, 10, @On-- ApplicationName
EXEC sp_trace_setevent @TraceID, 12, 11, @On-- LoginName
EXEC sp_trace_setevent @TraceID, 12, 12, @On-- SPID
EXEC sp_trace_setevent @TraceID, 12, 13, @On-- Duration*******
EXEC sp_trace_setevent @TraceID, 12, 14, @On-- StartTime
EXEC sp_trace_setevent @TraceID, 12, 15, @On-- EndTime
EXEC sp_trace_setevent @TraceID, 12, 16, @On-- Reads
EXEC sp_trace_setevent @TraceID, 12, 17, @On-- Writes
EXEC sp_trace_setevent @TraceID, 12, 40, @On-- DBUserName
EXEC sp_trace_setfilter @TraceID, NULL
EXEC sp_trace_setfilter @TraceID, 13, 0, 2, @Value
EXEC sp_trace_setstatus @TraceID, 1
SELECT 'Trace ID : ' + CAST(@TraceID AS NCHAR(4))
As you can see I'm trying to filter on duration where duration > 1000
WHAT AM I DOING WRONG?
October 18, 2007 at 10:40 am
Swartkruit (10/18/2007)
Hi All,As you can see I'm trying to filter on duration where duration > 1000
WHAT AM I DOING WRONG?
I don't know. What are you seeing that you don't think you should be seeing? What are the two events you're capturing?
Oh, just remember that in SQL 2005 profiler, duration is measured in microseconds, not milliseconds as it was in 2000. Your filter is for queries taking longer than 1000 microseconds, ie 1 millisecond.
If you're doing what I think your doing, perhaps
SET @Value = 1000000 -- 1 million microseconds. (1 second)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2007 at 11:35 am
Thank you GilaMonster,
A piece of valuable info. Unfortunately the fact that the filter is not applied is getting to me. The result set in Profiler with a duration filter of 1000000:
RPC:Completed569
RPC:Completed1218
RPC:Completed13892
RPC:Completed14500
RPC:Completed479
RPC:Completed406
SQL:BatchCompleted56583
SQL:BatchCompleted564
SQL:BatchCompleted397
SQL:BatchCompleted65999
SQL:BatchCompleted127
SQL:BatchCompleted55841
Now, that means that 569 = 0.569 sec and this means that the filter is not applied. Or should I take a course in converting 101?
Any advise guys... Please!
Regards,
S
October 19, 2007 at 12:43 am
Very strange. I scripted a trace from profiler which does filter and it looks almost exact. The only difference is that the trace I scripted out is that I don't have the line EXEC sp_trace_setfilter @TraceID, NULL
Since the trace has just been created, there's no need to clear filters.
If you remove that line, do you get any success?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2007 at 1:42 am
Hey GM,
Thanks for the advise. With reference to EXEC sp_trace_setfilter @TraceID, NULL...
It is a habit. I always initialize. You're not going to believe this. The trace was tested on my local machine. I decide to go for LIVE irrespective. And guess what...
Strange!
Once again thank you! Must admit, I cannot figure why MS changed the duration to micro from milli.
Anywhay, thanks again!
Regards,
S
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply