Use SQL Profiler with care on busy servers

Excerpts from a SQLMag article about running SQL Profiler on busy servers -
  • Profiler is a GUI front end that calls a series of functions and procedures collectively called SQL Trace.
  • In most cases, using Profiler to capture system information is more expensive than calling SQL Trace procedures directly. Consider using custom procedures to directly call SQL Trace procedures to stop, start,and control traces
  • Traces, whether you run them through Profiler or through direct calls to SQL Trace, rarely degrade performance unless they grow too big too quickly.
  • Always specify a maximum file size when tracing on a busy server. If a trace starts to grow uncontrollably large and surpasses the maximum file size limit is set, the trace will stop. 50MB is a safe maximum size. The proper maximum size will depend on the hardware and transaction volume.
  • Another way to control the trace file's size and reduce the potential for a trace-induced performance problem is to include only the events and data columns you need.
  • You can also control the trace size by specifying a minimum CPU duration for transactions you want to capture.
  • Most of your transactions will be fast, running in less than 20ms, so setting a minimum duration filter of 20ms would cut out a lot of the data you're writing to the trace file without losing many significant events.
  • Include trace filters to eliminate UDF activity as they create massive amounts of trace data.

Comments