‘Everyone’ knows that it is much more efficient to run a 'server side trace' than a 'GUI trace'. I have always been sceptical of this statement, as I prefer to be able to take a ‘peek’ at the GUI every hour or so, which you cannot do with a server side trace, and did not believe there could be that much overhead in running a GUI. However, I was proved wrong, but not for the reasons I originally thought.

I was recently running profiler on my laptop (ie single disk for OS, application db’s, tempdb, logs, temp directory etc etc) in GUI mode – the performance reduction on SQL write operations was incredible. So I started to think...
What setup would give me the least profiler overhead?
1) Local GUI trace
2) Remote GUI trace (using profiler on another machine collecting data from my laptop over the network)
3) Local server side trace

The local server side trace was by far the most efficient. It was so far ahead I could not believe it.
An insert operation loop (ie lots of writes) that took 25 seconds without profiler took 35 seconds with a server side trace but a massive 141 seconds with a local GUI trace. A remote trace took 56 seconds.

What was the cause of this massive performance difference?

The answer was simple – I monitored the disk IO and found that GUI mode profiler makes lots of small writes, but a server side trace makes much smaller numbers of larger disk writes – it’s as simple as that!
If you choose to save the GUI mode to a specific file location (as opposed to just looking at screen monitoring), the GUI makes 2 disk write operations, one write to the temp directory and one write to the 'save to' location dropping the performance even more – the insert operation above taking an incredible 206 seconds. If you want to save to a file and use the GUI it may be better to save the trace manually after it has completed.

Disk IO continues to become more of a bottleneck on single disk computers, where disk IO intensive systems such as databases show a startling performance drop off when there is a lot of (presumably) non sequential disk writes.

Here is a chart of the time taken - drop me a line if you need help understanding the detail

Parameter	No Profiler	Local GUI - no save to file	Local GUI + save to file	Local GUI - temp dir on USB	Server Side Trace	Remote Trace
Profiling Local or remote	Local	Local	Local	Local	Local	Remote
Profiler GUI ON	n/a	Yes	Yes	Yes	NO = 'server side trace'	n/a
Temp Directory	n/a	c:\...(local)	c:\...(local)	d:\...(Ext US	c:\...(local)	n/a
Profiler 'Save to file'	n/a	n/a	c:\...(local)	No	c:\...(local)	n/a
Network bandwidth (gigabit)	n/a	n/a	n/a	n/a	n/a	6%
Time seconds	25	141	206	124	35	56

This image shows this disk IO activity in the worst case scenario of selecting a specific file location for the trace, as well as running the GUI, where profiler is writing the same data twice, dragging down performance even more.



Happy profiling – server side of course!