Consolidating Data Capture with SQLdiag - SQL Server 2012 Internals and Troubleshooting - page 315

Capturing Extended Events
The XEvents Waits custom collector enables you to coni gure Extended Events (XEvents) data col-
lection on the target SQL Server instance. By default, the collector has three XEvent sessions for
capturing information for page latch waits experienced on the target instance. You can view coni g-
uration details of the collector by right-clicking on the XEvent Waits custom collector and clicking
Details as shown in Figure 11-10.

FIGURE 11-10

For example, assume there is a need to capture wait information for all PAGEIOLATCH waits.
PAGEIOLATCH_* waits are encountered when the SQL Server database engine experiences an I/O
sub-system related wait condition. Most commonly high wait-times experienced by these wait types
signify storage sub-system related latencies. Figure 11-11 shows what the i nal coni guration would
look like. This would require the addition of two custom events:
➤

A startup event that will execute a T-SQL script to create and start the XEvent session that
will capture the wait information for all PAGEIOLATCH waits

➤

A utility-type collector that will execute a T-SQL script when the SQLdiag utility is shut
down to collect the data captured by the XEvent session into an XML i le

FIGURE 11-11

One of the new additions to SQL Server 2012 Management Studio is
the UI for Extended Events. You can use the UI to coni gure your cus-
tom Extended Events session. Once the session is coni gured, it will be
available under the Sessions folder, as shown in Figure 11-12.

This new addition to Management Studio enables you to extend your
customization options by adding an Extended Event collection to

FIGURE 11-12