Databases Reference
In-Depth Information
This is where you can use the Maximum Dispatch Latency setting to specify the maximum amount
of time event data can stay in the memory buffer before it is l ushed to the target. The default is 30
seconds, which for a session with a properly sized memory buffer should be more than adequate;
there's no need to change any of the default settings unless necessary. However, if you want to be
cautious you can lower the maximum dispatch latency value; the minimum accepted value is 1
second.
Ring Buf er
Having looked at some of the options for sessions, we return now to some of the other targets that
SQL Server allows a session to use. The ring buffer behaves much like the event i le target in that
it's an asynchronous target for events to be written to, and it stores them in a FIFO list. A big differ-
ence, however, is that SQL Server Management Studio doesn't provide a way to translate its XML
contents into human-readable data like it does for the event i le. Instead, a T-SQL query needs to be
used to extract the i elds you want out of the ring buffer; and while this is relatively straightforward
(and demonstrated later in the chapter), it's a step back from the simplicity that the graphical user
interface provides to newcomers of the feature.
Another difference of the ring buffer is that it doesn't use any physical storage — it's purely an in-
memory structure, much like the memory buffer itself. Obviously, this means that data in it is lost
not only when the ring buffer i lls up and space has to be made for new events, but also whenever
SQL Server itself is stopped. This doesn't make it suitable for reactive monitoring of historic system
issues; but because you can have a target with a very low write latency that doesn't need housekeep-
ing afterward, it is suitable for either high-level real-time monitoring of production systems or low-
level controlled testing on development systems.
The ring buffer has two size parameters you can coni gure: either the total amount of memory it will
use, or the amount of memory per event it will use. Until you understand more about how you can
use the ring buffer, the default settings are more than adequate.
Event Counter
The event counter is one of three targets that don't store any of the event data sent to them but
instead perform a task with it. The event counter target does what its name suggests; it counts the
number of times the event occurs. Unlike some other targets, however, it doesn't require any event
data or actions to be sent to it; the fact that the event has occurred is all it requires to increment the
counter value it's maintaining. In order to provide an accurate event count, access to update it is
serialized, so it has to be used as a synchronous target. This shouldn't cause you any performance
issues though, as it's a very lightweight target that requires very little data to be sent to it.
Event counters, like the ring buffer, are in-memory structures. Therefore, their content is never writ-
ten to an event i le and is lost when SQL Server shuts down.
Histogram
The histogram could well be the most useful target in your early, and perhaps later, stages of
troubleshooting — if only to begin identifying trends in your data that enable you to focus on
a particular type of event's details.
Search WWH ::
Custom Search