Extended Events vs SQL Trace
– or why SQL Trace & Profiler are just a thing of the past 🙂
As a reminder: Extended Events have been integrated in SQL Server since SQL Server 2008. And since SQL Server 2012 SP1, all events existing in SQL Trace have been available. In addition, Extended Events have also been available for Analysis Services since SQL Server 2012 Tracing Analysis Services (SSAS) with Extended Events – Yes it works and this is how).
For those of you who are still working with the old tool SQL Server Profiler (Profiler is the frontend for SQL Trace, started with sp_trace_create) and have not quite been able to decide for the new technology, here is some decision guidance.
What Extended Events (XEvents) do better than SQL Trace:
- The only possibility of Tracing new features like FileStream, FileTable, AlwaysOn, ColumnStore, Hekaton/XTP etc.
- Many more Events traceable, even for older releases (see *1, *2 below)
- Significantly less Observer-Overhead, also see: Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load
- Performance/Overhead can be configured and tuned
- Events and Filters can be adapted live – meaning during active session
- Official 2 µs/Event vs. 4ms/Event in SQLTrace
- Event-loss is configurable
- Efficient filtering through architecture
- Complex predicates such as Counter or last_error, less_than_min_datatype oder greater_than_max_datatype
- Correlation of Events possible
- Possibility of following Events from Client into the Database
- Easy Automation
- Directly integrated in Management Studio
- Many analysis directly inside the GUI possible (in order to improve them, please vote at Microsoft Connect: Extended Events UI Export Display Settings: include grouping)
- Query_hash for identification of identical queries available
- No 10 clicks to set up a simple session including filter
- Choice of different destinations for storing EventData (6)
- “Goal-oriented” destinations such as Counter and Histogram
- Multiple destinations can be combined for “On the fly – top-down analyses”
- Possibilities for entirely new insights into internal matters of database engine (Latching, Spinlocks, Multi-victim-Deadlock, Wait_Infos per session/query, Caching-processes, Ghost-cleanup, analysis of Page Splits, Page-Compression processes, to name just a few)
- Stack Tracing of a single process possible – instead of a complete Server Dump
- Definition with standard DDL-Statements
- API for integration into one’s own tools available
- PowerShell support
- Last but not least: Finally a good reason to learn XML & XQuery? 😉
*1
Extended Events vs. SQL trace Events per Version
*2
As an example:
For Service Broker there are: 15 Events in SQLTrace vs. 44 Events in XEvents (SQL Server 2012 SP1)
I hope this helps some of you to unlearn the old habit and accept the little learning phase.
A list of all Extended Events in SQL Server 2012 SP1 including its counter piece in SQL Trace that can be used for migration purposes (SQLTrace-> XEvent Trace) is available on this page. (Due to size restriction, it didn’t fit in here anymore.)
P.S. For 2014, the next round of training in this topic is being developed in conjunction with SQL Server Master-Classes on the topic “Tracing with Extended Events” 🙂
Leave a Reply
Want to join the discussion?Feel free to contribute!