Diagnosing SQL Server 2012 Using Extended Events - SQL Server 2012 Internals and Troubleshooting - page 362

For example, the wait_stat event reports every type of wait stat as
they occur, but you'll probably want to know which types of wait stats
occur the most often. If you were to use the event counter target, that
would just tell you the total number of wait stats that have occurred so far,
whereas the histogram will tell you how often each type of wait stat has
occurred.
At this point, however, you begin to see where the user-friendly
and quick-to-deploy side of Extended Events is countered by the program-
matic T-SQL and XML-heavy side of the feature. When the histogram
reports aggregated data about event activity, it uses internal identii ers
rather than the friendly names we're used to in its results, as shown in
Figure 13-4.
This indicates that wait stats 796 and 131 are occurring the most often,
but a DMV lookup is needed to know what they are:
FIGURE 13-4
select map_key, map_value from sys.dm_xe_map_values where name =
'wait_types' order by map_key
In this instance, you can manually look up the wait types and see they were DIRTY_PAGE_POLL and
LOGMGR_QUEUE . Ultimately, you would want to query the histogram using T-SQL so you can map the
values to the corresponding collected results.
Histograms are clearly useful when you're capturing data about “busy” events and need to know
more about the data they're returning, but if you want to use SSMS exclusively you'll have to
become familiar with some of the internal names Extended Events uses. Again, like event counters,
histograms are in-memory structures, so their content is never written to an event i le. Therefore, it
is lost when SQL Server shuts down.
Pair Matching
The pair matching target is another event data-handling, rather than storage, target that enables
you to discard two sets of related event data from your results. The example that's always used to
demonstrate how this works is the data captured for the lock acquired and lock released events. If a
lock is acquired but then subsequently released you're probably not interested in storing information
about that having happened, and the pair matching target will discard both of those events. What
you're more likely to be interested in are locks being acquired but not released by the same state-
ment. If so, then using this target is appropriate.
Event Tracing for Windows (ETW)
This is the last target that Extended Events in SQL Server 2012 has and it is at the very hardcore
end of the feature's capabilities. Using the ETW target enables SQL Server to integrate its event data
with external Windows debugging software so that a complete picture of a system's performance
can be captured. It's not something a SQL Server professional would typically do, but for those of
you interested in the technology there are some excellent articles about it on the MSDN website.