Transcript SQL Server Extended Events

SQL Server Extended Events
SQL Zaterdag, Utrecht
Norman Gillis, Premier Field Engineering
17-November-2012
Table of Contents
Why extended events?
What is it?
The Extended Event Object Model
Extended Event Objects
Building a Session
XEvent MetaData
Anatomy of an Event Session
Demo
Why did Microsoft create extended events?
Eliminate
breakpoint/dump
technique
SQL Trace not
performant
Reduce need for
debugger
XEvent can trace
Lock:Acquired and
Lock:Released with little
degradation
Integrate with
Windows
3
Build the foundation
for a common
instrumentation
system across our
SQL products
What is it?
Requires
SQLOS
API
System
Tables
An object
model and
extensible
infrastructure
Packages
4
Engine
The Extended Event Object Model
Event
• A name associated with interesting point in the code.
Events have properties called fields.
Action
• Do something or add data orthogonal to the event. Always
synchronous call
Target
• Consume the event data and do with it what you want.
Comes in two flavors async and sync
Predicate
Map
Package
• Some expression that establishes criteria for when to pass
event data to targets. Can be a pred_source or field
• Group of logical names that map to values. One of the
features of maps is provide keywords and channels
• A named collection of the above objects
5
Extended Event Objects
Events
Actions Targets
package0
Thread_attached
Sqlserver
SQLOS
Callstack
ETW
Sql_text
File
Dump All
Threads
Pair_Matching
Wait_info
File Growth
SecAudit is
for All
Actions
Audited
StmtCompleted
6
Building Your Session
Pick your
events
Pick your
actions
(optional)
Pick your
predicates
(optional)
Create the
session
Provide any
session options
(optional)
Pick your
targets
(optional)
Start the
Session
The result of this is the event session
stored in a system table but it is not
running yet
Your session is now running
7
Xevent Metadata
Package Metadata
• dm_xe_packages
• dm_xe_objects – events, actions, targets, pred_source, map
• dm_xe_object_columns – fields including keywords and channels
• dm_xe_map_values - maps
Created Sessions
• server_event_sessions – session options
• server_event_session_events – predicates are here
• server_event_session_actions
• server_event_session_targets
• server_event_session_fields – customizable fields
Started Sessions
• dm_xe_sessions – session statistics
• dm_xe_session_events – predicates are here
• dm_xe_session_event_actions
• dm_xe_session_targets – this is where you get your data for memory based
targets
• dm_xe_session_object_columns - customizable fields
8
Anatomy of an Event Session
create event session myfirstsession on server
add event sqlserver.error_reported
(
action
(
Can mix objects
package0.callstack,
from different
packages in a
sqlserver.session_id,
single “add
sqlserver.sql_text
event”
)
where sqlserver.error_reported.error = 208
and package0.counter <= 5
)
add target package0.ring_buffer
You can have
multiple targets
for a session
9
Can add multiple
events across
multiple packages
for a single session
Example of
property of
event
Special
property of a
package that
keeps state
Life of an Event
Pre-Collect
Collection
Post-Collect
IsEnabled
check
Event data
collected
Predicate
evaluation
Publish
Actions
executed
Synchronous
targets served
10
data buffered
asynch targets
Demo for SQL Server 2008 R2 / 2012
Demo
11
References
About Event Tracing:
http://msdn.microsoft.com/en-us/windows/hardware/gg487334.aspx
Troubleshooting Performance Problems in SQL Server
2008:
http://msdn.microsoft.com/en-us/library/dd672789(SQL.100).aspx
Technet SQL Server 2008 R2 Extended Events:
http://technet.microsoft.com/en-us/library/bb630282.aspx
MSDN SQL Server 2012 Extended Events:
http://msdn.microsoft.com/en-us/library/bb630282(SQL.110).aspx
12
Questions?