SQL Server Profiler or Server-Side Trace?

I’m sure you’re familiar with the built-in SQL Server Profiler tool and the wonderful information it can return and display for you. Most DBAs use the tool on a weekly—if not daily—basis to troubleshoot problems or to simply look for poorly performing queries. I’m not here to explain what the tool does but rather to answer an increasingly popular question that people seem to have about the tool. You see, there’s an ongoing debate—raging since the debut of SQL Server 7.0—about what kind of performance impact SQL Server Profiler has on overall performance of the database server when it’s actively tracing.

Whenever you click Run while using the tool against a production server, you probably wonder about the performance hit. I know there are also DBAs and developers among you who refuse to believe that SQL Server Profiler might adversely affect the server and therefore use it in all situations. Make no mistake: SQL Server Profiler can impose a performance penalty, and in some cases that penalty can be severe.

Profiler vs. Trace

The alternative to SQL Server Profiler is—and has always been—performing a server-side trace. Information about the trace procedure is readily available (see "9 Steps to an Automated Trace," for information about how to create and use a server-side trace), but suffice it to say, server-side traces bypass the SQL Server Profiler GUI and send the trace-event information directly to a file on the local hard disk. Then, you can later read the trace file for further processing or viewing, through SQL Server Profiler or the fn_trace_gettable function. Common wisdom is that server-side traces require significantly less overhead than SQL Server Profiler to process the same number of events. To see just how much less, I decided to put the two procedures to the test.

Keep in mind that many factors ultimately determine the amount of stress each of the two options will put on the server. For SQL Server Profiler, these factors include whether you run the tool locally or on a remote client; the network’s speed and overall efficiency; the client’s processors, available memory, and disk speed; and SQL Server Profiler options (e.g., normal trace, trace-to-file, trace-to-table).

For a server-side trace to a file, the only applicable option is where the trace file will be located. Tracing to a file on a network share isn’t recommended, but it’s an option. Tracing to a file located on the same disk array as the data or log file also isn’t recommended, because of the extra overhead it might incur on the trace. The preferred method is to trace to a file on a local drive (either SAN or direct-attached) that doesn’t have any other traffic that will impede the writing or interfere with other processes accessing that disk.

For the purposes of this test, I tried to keep everything as simple as possible by limiting the number of events and columns to that which would be typical of most traces. Therefore, I’m capturing only the Batch Completed and RPC Completed events, with the default columns. Keep in mind that, in both cases, the more events and columns you choose to trace, the larger the impact you’ll have on the server while tracing. To simulate a load on the SQL Server system, I used Quest Software’s Benchmark Factory to generate a series of consistent but high-volume calls to the server. Next, I traced the events by using both SQL Server Profiler and server-side traces—but never together. I divided the tests into four parts, two that used SQL Server Profiler and two that used serverside traces. Then, I ran two sets of tests for each group, using both a singleuser load and a two-user load. The key was to show the effect on the server when processor utilization is both midrange and nearly maxed out.

In all four of this article’s figures, the vertical green line indicates the start of the trace and the blue line indicates the end. Figures 1, and 3 show the results of running SQL Server Profiler, and Figures 2, and 4 show the results of using server-side traces. As you can clearly see, SQL Server Profiler has a significant performance impact and server-side traces have an almost negligible effect. You can also see that the impact gets much more pronounced when the number of batch requests per second and server CPU usage increase. In the first set of tests, the server was averaging about 4,000 batch requests per second without SQL Server Profiler running and about 3,000 with it running—a 25 percent decrease in overall system throughput. In the second set of tests, the results go from just over 6,000 requests per second to about 2,000 requests per second. That’s a 66 percent decrease in throughput while using SQL Server Profiler, whereas the impact of the server-side trace remained hardly noticeable.

Choose Wisely

My intention isn’t to convince you to stop using SQL Server Profiler altogether on a live server, but rather to give you some food for thought. Adding a proper filter to a server-side trace to limit the captured events can make a dramatic difference in overhead and thus make the server-side trace a more performance-conscious option.