Extended Events - Pro SQL Server Internals - page 555

SQL Server flushes the data to asynchronous targets when the buffers are full and/or based on a time interval
specified by the max_dispatch_latency setting, which is 30 seconds by default.
The startup_state option controls whether an event session should start automatically on SQL Server startup.
Finally, the track_causality option allows you to track the sequence of events and see how different events lead
to each other. An example of such a scenario is the SQL statement that triggers a file read event, which in turn triggers
a wait event with PAGELATCHIO wait and so forth. When this option is enabled, SQL Server adds a unique activity ID
that is a combination of the GUID value, which remains the same for the task, and the event sequence number.
After an event session is created, you can start or stop it with the ALTER EVENT SESSION command, or drop it with
the DROP EVENT SESSION command, as shown in Listing 28-12.

Listing 28-12. Working with an event session

-- Starting Event Session
alter event session [TempDB Spills]
on server
state=start;

-- Stopping Event Session
alter event session [TempDB Spills]
on server
state=stop;

-- Dropping Event Session
drop event session [TempDB Spills]
on server;

Working with Event Data
Management Studio 2012/2014 provides you with a UI to monitor a live stream of event data or to examine data
already collected in the targets. This UI is very convenient and flexible, and it allows you to customize the layout of a
grid that shows events, letting you group and aggregate event data and export it into the database table, event, or
CSV files.
You should be careful, however, when connecting to a live stream of event data because event sessions can
generate events faster than Management Studio can consume them. When this happens, Management Studio
disconnects from the live stream of data to avoid a negative impact on server performance.

■ Using a local instance of Management studio installed on sQL server adds overhead to the server.
always connect to sQL server remotely if possible.

Important

In this section, I will not discuss how to work with the Management Studio UI, but rather will focus on T-SQL
implementation. I would encourage you, however, to experiment with Management Studio. Even though Extended
Events management UI has some limitations, it is more than sufficient in a large number of cases.