| Executive Summary: What kind of performance impact does SQL Server Profiler have on overall performance of the database server when it’s actively tracing? We think you’ll be surprised. After you see the results of our tests, you’ll probably want to switch to the more lightweight option of using server-side traces. |
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,” InstantDoc ID
43014, 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.
End of Article
