Transcript EOH: A Microsoft Partner INTRODUCTION TO EXTENDED EVENTS What is XE  Extended Events: – Replacement for SQL Profiler (Deprecated) – XE features on.

EOH: A Microsoft Partner
INTRODUCTION TO EXTENDED EVENTS
What is XE
 Extended Events:
– Replacement for SQL Profiler (Deprecated)
– XE features on all editions
 Event handling framework for systems
 Used for diagnostics, tracing and auditing
 XE combines information from SQL Server and OS
www.eoh.co.za
XE Architecture – Courtesy BOL
www.eoh.co.za
XE Components
 Packages
• Parent container contained attributes of events within
the package
• Three Types
–
–
–
–
Package0
SQLServer
SQLOS
1 Internal
www.eoh.co.za
XE Components
 Events
• Two Categories
– 4 Channels (Grouping of Events)
– Keyword (Specific)
• Defined point in task
www.eoh.co.za
XE Components
 Targets
• Consume events & output event data
• Six target types
–
–
–
–
–
–
File
Histogram
Counter
Pairing
ETW
Ring Buffer
www.eoh.co.za
XE Components
 Actions
• Response\s to an event.
• All actions are SYNC.
• Collects object details from EVENT.
– TSQL capture\ Plan handle\ Numa ID etc.
• Certain events can have unique actions.
www.eoh.co.za
XE Components
 Predicates
• Boolean expressions that have a failure on first false evaluation.
• Applied per event not per session.
• Order of predicates can affect performance.
• Rich evaluation and filtering contexts
www.eoh.co.za
Metadata – System Views
GLOBAL EVENT SESSION DATA
 sys.server_event_sessions
 sys.server_event_session_target
 sys.server_event_session_fields
 sys.server_event_session_actions
 sys.server_event_session_events
www.eoh.co.za
Metadata - DMVs
EVENT SYSTEM DATA
 sys.dm_xe_packages
 sys.dm_xe_objects
 sys.dm_xe_object_columns
 sys.dm_xe_map_values
www.eoh.co.za
Meta Data - DMVs
ACTIVE SESSION DATA
 sys.dm_xe_sessions
 sys.dm_xe_session_targets
 sys.dm_xe_events
 sys.dm_xe_event_actions
www.eoh.co.za
DEMO
Querying XE system Tables And DMVs
www.eoh.co.za
XE DDL




CREATE EVENT SESSION…ON SERVER
ALTER EVENT SESSION…ON SERVER
DROP EVENT SESSION…ON SERVER
COMMON OPTIONS:
–
–
–
–
MAX_DISPATCH_LATENCY
MAX_MEMORY
STARTUP_STATE
EVENT_RETENTION_MODE
• SINGLE
• MULTIPLE
• NONE
www.eoh.co.za
DEMO
XE DDL
www.eoh.co.za
SQL Trace vs XE






Real-time amendment of events and predicates.
SSMS GUI interface.
Different stores for output.
Event correlation available.
Profiler Deprecated (Repeat )
Less Overhead.
www.eoh.co.za
Migrating Profiler to XE
 sys.trace_xe_event_map
 sys.trace_xe_action_map
 sp_BlitzTrace
www.eoh.co.za
Use Case Scenarios







Deadlocks
Page splits – fill factor
Auditing – Internal Use only
CPU and IO tracking
Wait stats
Queries by duration
General troubleshooting
www.eoh.co.za
DEMO
USE CASES
www.eoh.co.za
Enhancements in SQL 2012\4
 Additional XE objects added.
 Live data view.
 GUI interface.
•
•
•
•
•
•
•
Aggregation and filtering in UI
New session wizard
Create new session
Templates
View Target Data
Customizing View
Live Data watch
www.eoh.co.za
DEMO
SSMS XE GUI
www.eoh.co.za
Best Practices & Considerations
 Pair Matching considerations.
 Partitioning nodes.
 Predicates.
 Use the MAX option to control overhead.
 Use sampling to minimize result sets.
www.eoh.co.za
Useful Resources
 Idera XE Profiler
• https://www.idera.com/productssolutions/freetools
 sp_BlitzTrace – Brent Ozar
• http://www.brentozar.com/extendedevents
 Jonathan Kehayias – SQLSkills
• XE SSMS 2008 Add-In (codeplex)
• Deep Dive Blogs (msdn & sqlskills.com)
 Mike Wachel – MSFT
• XE Engine developer
• http://blogs.msdn.com/b/extended_events/
 XTrace (WinSDK)
• Tools for OS\SQL\APP debug
www.eoh.co.za
Questions & Answers

 Donovan White
Email: [email protected]
 Twitter:@SQLSoup
www.eoh.co.za