10046 Trace Overhead

From: Matt <mccmx_at_hotmail.com>
Date: 20 Jul 2004 08:31:46 -0700
Message-ID: <cfee5bcf.0407200731.6c91c007@posting.google.com>

I encountered some mysterious behaviour today when trying to diagnose a performance problem with an online screen in one of our applications.

This specific user action takes 3.5 seconds without tracing enabled. In order to determine where this time was being spent I enabled a 10046 trace (level 8) to catch the wait events.

The elapsed time jumped up to 17 seconds with the trace enabled.

At first I couldn't believe it because its my understanding that the overhead of extended SQL trace should be mimimal.

So I did some more investigation and found that this overhead increased or decreased depending on the level of SQL trace (I tried level 1, 8 and 12).

Level 1 tracing caused the elapsed time to be 5 seconds, and level 12 caused a 24 second total elapsed time

Has anyone encountered behaviour like this before...?

Any feedback would be greatly appreciated...

Matt Received on Tue Jul 20 2004 - 10:31:46 CDT