Advanced Options

When you start a trace and the server is the defined source, Microsoft® SQL Server™ creates a queue that provides a temporary holding place for captured server events.

Each trace can have multiple producers. A producer collects events in a specific event category and sends the data to the queue. Events are read off the queue in the order in which they were placed. This reading method is called first-in/first-out (FIFO).

When a client reads from the trace queue, buffer space is freed so that producers can place more events on the queue. The size of the queue determines how many events can be held in the queue at any one time. This can be modified by updating the number of rows to buffer on the source server.

As the number of events in the queue increases, the priority of the thread that is reading the events to pass to the client increases. This allows the client to read events faster to meet the demand. Because the client is reading events faster, either the number of events in the queue decreases or the queue continues to fill, but at a slower rate. As the number of events in the queue decreases, the priority of the thread that is reading the events decreases, thereby reducing the effect on overall server performance.

Sometimes producers have too many events for the queue, and the queue fills up. If the queue fills up, producers cannot write any more events to the queue, and they enter a time-out period. As an event is removed from the queue, then at least one producer moves out of the time-out period. When enough events are removed from the queue, all paused producers can write events to the queue and the queue resumes normal processing.

If the time-out period did not expire, backed-up events are written to the queue and no events are lost. However, if the time-out period expires before any producer can write an event to the queue, then the trace enters an autopause state, which instructs producers to stop submitting events to the queue. As a producer enters the autopause state, an event is written to the queue indicating that the producer has been paused. Producers continue to collect event data, but cannot write to the queue, so any backed-up events or events collected during the autopause state are lost. When the queue opens because enough events have been read by the client, thereby partially emptying the queue, producers leave the autopause state and resume writing events to the queue.

If the queue is full but there are no new events, a time-out does not begin because there are no producers waiting to write events to the queue.

Configuring the Queue Settings

When you create a trace, you can modify the default queue settings for the server that is used as the source of the trace. Use these settings when trying to improve the performance of a trace and to prevent producers from entering the autopause state.

Queue setting	Description
Server timeout	Duration of the time-out period (in seconds) that the producer enters when the queue is full. If the time-out period is exceeded before events are removed from the queue, the producer enters the autopause state.
Number of rows to buffer	Maximum number of rows in the trace queue. A larger queue uses more memory, but it helps prevent the producers from entering the autopause state if there are too many events or producers.
Boost Priority	Point at which the priority of the thread reading the events from the queue is boosted to increase the rate at which events are read. The higher the percentage, the more the queue is allowed to fill before the thread priority is boosted.
Reduce Priority	Point at which the priority of the thread reading the events from the queue is reduced to decrease the rate at which events are read. The lower the percentage, the emptier the queue must be before the thread priority is reduced.

Considerations for setting the Boost Priority and Reduce Priority settings include:

The Boost Priority and Reduce Priority settings have no effect on SQL Server Profiler threads because they affect the server thread that reads the events in the queue for SQL Server Profiler. The server thread is part of the SQL Server engine.
By setting the Boost Priority and Reduce Priority settings to 0, the priority of the server thread reading the events from the queue is always boosted, thereby reducing the chances of the queue becoming full. This helps prevent the producers from entering the autopause state, which causes events to be lost. However, using a permanently boosted server thread can impair server performance.
By setting the Boost Priority and Reduce Priority settings to 100, the priority of the server thread reading the events from the queue is always reduced, thereby reducing the effect on the server. However, the queue is more likely to fill, thereby causing producers to enter the autopause state.
By setting the Boost Priority and Reduce Priority settings to low values, such as 2 and 1 respectively, the priority of the server thread reading the events from the queue is boosted during times of heavy traffic but reduced at other times, which reduces the effect on the server.

To modify server queue settings