INF: Tracing to Network Drive May Reduce SQL Server Throughput (307786)

SUMMARY

SQL Server 2000 includes sp_trace_create, a system stored procedure that you can use to start a trace. By default, the sp_trace_create stored procedure creates a trace that is written by the server to a specified file.

If the specified file is on a remote drive (a UNC path), and the volume of information that is being written to the trace file is significant, SQL Server throughput may be reduced considerably as it waits for the network to complete writes to the file.

MORE INFORMATION

When you start a trace SQL Server allocates a fixed number of buffers in which the Profiler events are captured. As a buffer is filled, the events are asynchronously written to a specified file.

If the device that is holding the specified file is a slow device, or if the intermediate network is high latency or low bandwidth, it is possible for the events received by the server to be produced faster than they can be written to the trace file. If the events being received by the server are being produced faster than they can be written to the trace then all of the available buffers may be full and pending completion of the write to the file. When this happens any new request to the server that must produce a trace event must wait for one of the IOs to complete so that a buffer is available to hold the new event data.

Consider a situation where a trace is configured to capture the TextData column for the SQL:BatchStarting and SQL:BatchCompleted events. For such a trace going to a UNC path, the text of each query that is sent to SQL Server traverses the network three times:

Once as the client sends the initial query.
Once for the SQL:BatchStarting event.
Once for the SQL:BatchCompleted event.

This additional traffic may quickly saturate the available bandwidth of the network and reduce the overall throughput of the computer that is running SQL Server.

If a trace is to be captured on a busy system, be prepared to monitor the overhead of the trace. A suggested performance counter to watch is SQL Server:SQL Statistics\Batch Requests/second. If the overhead of tracing to a UNC path is too high, you may consider tracing to a high performance local drive instead.