When you have a question such as, “What queries are running in SQL Server right now that’s making it so slow?”, or “What is causing all the deadlocks in SQL Server?”, what tool do you reach for? Is it Profiler? That’s a natural reaction! Profiler has been integrated with SQL Server since 7.0.
However, Microsoft is moving in a new direction for tracing events and collecting information about them. The new framework, Extended Events (XE), is more comprehensive, less resource-intensive, and similar enough to Profiler to learn quickly. In my video, Moving from Profiler to Extended Events, I talk about why you want to learn it, show you a direct comparison between setting up a Profiler session and an XE session, and teach you how to convert existing Profiler traces to XE.
After I give this talk, I get a lot questions. Here are a few of the most common, and my answers.
What versions of SQL Server can (and should) I use XE for?
XE became available in SQL Server 2008. However, a GUI, and a significant number of new events to track, weren’t available until 2012. I use XE on SQL Server 2012 + instances.
Can I create an XE session in 2012 and run it on 2008 or 2008R2?
Sometimes. You have to check if the events you chose in 2012 existed in 2008/R2. There are 618 events in 2012, but fewer than 300 in previous versions. There are minor syntax differences, too. Generally, if I need to trace information and events in 2008/R2, I will run a trace.
How do I view the information after I collect it?
While running the session, you can use the View Live Data option (not recommended) or the View Target Data option for the target (file or buffer). After the session is over, you have two choices. The information is stored as XML in an .xel file. You can learn XQuery and parse the data (not for the faint of heart, but I was able to learn it), or double-click the .xel and it will open in SSMS – just like a Profiler .trc file!
Will XE slow down the server significantly, like Profiler and traces could?
If you write an XE session that’s capturing dozens of events, with multiple filters, and you’re writing it to multiple targets, yes, there’s a good chance it will. But if you carefully craft a session to get only the information you need, you can run it with minimal performance impact. The secret lies in XE’s pre-processing of predicates!
Have more questions? Please feel free to contact me. I want everyone using XE because it’s so much better!