Databases Reference
In-Depth Information
The objective of this chapter is to introduce you to the Extended Events feature in SQL Server 2012
using its new graphical user interface. After reviewing how Extended Events work and the termi-
nology it uses, you'll see how easy it is to start capturing diagnostic information, and how to start
doing things that previous generations of tools like Proi ler and SQLTrace both did and didn't let
you do. Finally, you'll look at how to analyze the data that Extended Events captures.
GETTING FAMILIAR WITH EXTENDED EVENTS
Before looking at Extended Events, it's worth thinking about why you might want to read this chap-
ter. After all, if you've survived this long without using Extended Events, then you might be tempted
to skip this chapter and read about something you're more familiar with. The next few paragraphs
should both introduce you to the feature and demonstrate some of the reasons why reading the
remainder of the chapter would be useful. Note that the purpose of this chapter is to provide a high-
level overview of getting started with Extended Events, rather than an in-depth examination of its
internals or advanced features, for which you can i nd excellent community contributions online.
Extended Events is a database engine feature that captures troubleshooting information about
events you're interested in i nding out more about. However, unlike previous generations of tools, it
has very little overhead on the system it's monitoring, not capturing diagnostic data until an event is
triggered by a very specii c set of coni gured database engine events. This differs from tools, such as
Proi ler or SQLTrace for which, even with clever i lters, you typically need to capture large volumes
of event-related data, and then manually match your troublesome event's occurrence to other
sources of data you capture around that time.
In fact, it's almost certain that if you've previously done any diagnostics work in SQL Server you'll
have used the SQLTrace or Proi ler tools, and it's these technologies specii cally that Microsoft is
aiming to replace with Extended Events (eventually).
Unlike SQLTrace or Proi ler, the “sit and wait” approach of Extended Events enables you to capture
only the data that's of interest to you, reducing both the volume of data that needs reviewing and
the load you place on SQL Server. Of course, you can still use Extended Events to capture large
amounts of data for a very broad range of events if that's what you want — in fact, its l exibility is
another of its benei ts.
Another benei t just briel y mentioned is that unlike previous generations of debugging tools, using
Extended Events adds very little overhead to the system being monitored, both because of how it
does the actual monitoring and how it then records the data it subsequently captures. Indeed, from
its very inception, Extended Events was designed to be lightweight and therefore something you can
use on servers with even the heaviest of workloads; that's not something you could say about previ-
ous tools like Proi ler.
In addition to the more traditional use of Extended Events by DBAs and developers for trouble-
shooting performance, security, and functionality issues, Windows system administrators can also
hook into Extended Events as a source of debugging data about what SQL Server is doing. In fact,
Extended Events are very closely related to a hardcore debugging system called Event Tracing for
Windows, which will be familiar to people who debug Windows at the kernel level. Therefore, if
you're in any of these categories, you'll likely i nd this chapter interesting and useful.
Search WWH ::
Custom Search