Databases Reference
In-Depth Information
XeDkPkg Extended events for SQLDK binary
sqlserver Extended events for Microsoft SQL Server
SecAudit Security Audit Events
ucs Extended events for Unified Communications Stack
sqlclr Extended events for SQL CLR
filestream Extended events for SQL Server FILESTREAM and FileTable
sqlserver Extended events for Microsoft SQL Server
For those of you familiar with SQL Server's other features, you'll likely have spotted a package
called
SecAudit
and associated it with the auditing feature within SQL Server. The auditing feature
uses Extended Events itself as the mechanism to capture and log SQL Server event activity requested
by security administrators. However, this package has extra security protection around it, so you
can't view or use any of its content yourself.
The events themselves, which you're able to monitor for using Extended Events, are also visible
through a DMV. They are stored with other Extended Events data so a
where
clause is needed:
select name, description from sys.dm_xe_objects where object_type = 'event'
order by name
If you're familiar with Proi ler or SQL Trace, then you are already familiar with some of the event
names that the preceding query returns. If not, many of the event names are self-explanatory, such
as
sql_batch_completed
,
database
_
created
and
login
. As you begin to browse through the long
list, however, you'll begin to see some of the newer monitoring capabilities that Extended Events
offers, including events such as
page
_
compression
_
attempt
_
failed
,
availability
_
replica
_
state
_
change
, and
wait
_
info
.
For those of you who want to migrate away from using Proi ler, it's possible to look up the name
of the Proi ler events you're familiar with and i nd their equivalent in Extended Events through the
mapping query shown here:
select t.trace_event_id as 'Trace Event ID', t.name as 'Trace Event Name',
x.xe_event_name as 'XE Event Name'
from sys.trace_events t
join sys.trace_xe_event_map x
on t.trace_event_id = x.trace_event_id
Event Fields
Once you understand what an event is, the concept of an event i eld is easily understood. Whereas
an event is something that occurs, an event i eld is a piece of data about that event. For example,
some of the event i elds for the
sql_statement_starting
event are
line
_
number
,
offset
,
offset
_
end
, and
statement
, all pieces of data that could be useful in diagnosing an expensive statement.
You can query the list of event i elds for an event from the Extended Event DMVs, as shown in the
following example query for the event i elds just mentioned:
select c.name, c.description
from sys.dm_xe_object_columns c
join sys.dm_xe_objects o on o.name= c.object_name
where o.name = 'sql_statement_starting'
Search WWH ::
Custom Search