Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Greg Linwood — Thu, 02 Aug 2007 11:24:11 GMT

Good article Linchi

As you're aware already, I'm in the remote db admin business. Understanding the impact of SQLTrace is very important to my business because we need to keep traces running all the time if we are going to have any hope of answering customer questions about what was wrong with their servers in the past hour or so whenever they might call up complaining about performance. Obviously there's no point starting traces after the fact so we need them running all the time but we also don't want them impacting performance in the first place..

So we have undertaken many similar tests over the past 4 years and have consistently come up with similar results. Server-side traces have almost no impact on overall server performance but interactive Profiler sessions can have a noticably bigger impact. However, I've never seen *such* a significant difference as with your results, but then again I never run unfiltered Profiler sessions on live systems so there might be a major difference there. Your testing only has the default "no profiler activity" filter but is otherwise collecting all events. Perhaps a more useful test would be to put a simple filter such as Reads > 200 on just to fish out the lionshare of relatively meaningless information which most people wouldn't collect?

One very obvious problem with running SQL Profiler traces on the server which I don't think you mentioned is that it causes heavy CPU context switching if a lot of events are being captured (as the application's busy & taking CPU time away from SQL Server). Another interesting test would be to see how much difference occurred if you had SQL Server affinitised to 3 CPUs..

I'd also like to point out that the systems we do our trace impact evaluation on are real systems - we always collect various Perfmon counters but sometimes turn off SQLTracing to compare results. All of the servers are different (different customers in different industries running different systems etc) but the SQL Trace impact differential is always fairly consistent even though the servers are all very different..

One lesson we've learned is that there's a significant difference in overall server performance impact based on how many filters are configured for any given trace. Again, your trace only had the default filter which excludes Profiler events, but our tracing (& most routine tracing) always includes some sort of filter. We've found that too MANY filters (eg 7 or 8 on a single trace) can have a huge impact on server-side traces, even if only a few otherwise healthy events such as those in this trace are being collected.

It would be interesting to see how adding more filters affected your results.

Cheers,

Greg Linwood

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Adam Machanic — Thu, 02 Aug 2007 13:57:06 GMT

Greg,

Just to clarify, when you say that Profiler causes context switching, you mean only if Profiler itself is run on the same server running SQL Server, i.e. from the console or a RD connection, right?

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Greg Linwood — Thu, 02 Aug 2007 14:02:58 GMT

Adam, Yes - the overhead can be substantial because it's not just about screen refreshes. There's a lot of overhead associated with re-structing event rows from their constituent column fragments. The more events, the more CPU workload the Profiler generates.

Cheers,

Greg Linwood

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Jose Mariano Alvarez — Thu, 16 Apr 2009 02:54:27 GMT

Remember you can use server side traces with profiler if run locally in server.

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

davetiye — Tue, 03 Nov 2009 12:59:02 GMT

güzel davetiye sözleri ve davetiye modeloleri

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

davetiye — Tue, 03 Nov 2009 12:59:36 GMT

güzel davetiye sözleri ve davetiye modelleri

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

davetiye — Wed, 04 Nov 2009 07:48:22 GMT

güzel davetiye sözleri ve davetiye metinleri

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

rich — Tue, 22 Jun 2010 10:00:59 GMT

Pingback from http://sqlsolace.blogspot.com/2010/06/bookmark-performance-impact-of-profiler.html

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Mark Broadbent — Tue, 22 Jun 2010 11:04:04 GMT

Good post Linchi.

"the temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\lshea\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp."

..yes in fact the danger is (especially if on server) that you will fill out your C drive. You can minimize the risk somewhat when the GUI is absolutely necessary by creating a symbolic link to another less important drive for this temp directory. I've tried it in the past and it works fine.

Mark Broadbent aka retracement

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Niraj Dube — Thu, 18 Nov 2010 23:40:25 GMT

Very interesting thread, guys.

To Greg’s comment that there is “no point starting traces after the fact”: There are tools in the market that allow you to monitor SQL queries on an “always on” basis -- without imposing any performance overhead on the server.

Sort of "have your cake and eat it too".

Full disclosure: I work for one of the vendors who provide such tools. Vendors use various mechanisms to ensure that their performance monitoring is non-intrusive. Ours does not open a connection to the db nor poll it in any way; instead, we listen to network traffic to passively capture queries and calculate associated performance metrics (such as server reaction time, query response time, round-trip time, etc); and we have a loopback module to capture local traffic. We specialize in high-transaction / high-volume production database monitoring – where, to quote a blogger I saw elsewhere, “you cannot have the odometer slow down the car”. More about us at: http://www.exact-solutions.com/products/iwatch

A Brief History of Monitoring (A Month of Activity Monitoring, Part 1 of 30)

Adam Machanic — Wed, 20 Apr 2011 14:43:26 GMT

This post is part 1 of a 30-part series about the Who is Active stored procedure. A new post will run

re: Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

Phil — Thu, 22 Sep 2011 15:13:11 GMT

I have a problem with this article, which is that it states that the Default Trace was used for the test. Now, who in reality actually uses the default trace? That trace captures many events that are not only pretty useless, but which are unfilterable.

If you run a profiler trace without sensible filtering, then of course you will see performance issues. It's also perfectly possible to stuff up performance using a server side trace if you get it wrong, and because the server side trace is guaranteed lossless (it will not discard events if they are generated faster than it can write them to the file - SQL will be forced to wait whilst the writes take place) then a SS trace can actually have a worse impact on your server than a profiler trace.

I'd like to see these tests repeated using sensible, real-world filtering. At my workplace we are absolutely paranoid about millisecond performance (we run in excess of 20,000 transactions per second on our main server, so we have to be!), but we still run profiler traces throughout the day with no performance impact. That's because we put sensible filters on the traces.

Profiler CAN impact performance, it's true, but so can server-side traces. It's up to the DBA to implement both types sensibly, but don't be afraid to use either one. They each have their merits.

Avoid Causing Problems with Profiler | Dan Guzman's Blog

Avoid Causing Problems with Profiler | Dan Guzman's Blog — Mon, 29 Apr 2013 01:23:20 GMT

PingBack from http://www.dbdelta.com/avoid-causing-problems-with-profiler/