Category: Extended Events

Jess Pomfret goes looking for Extended Events relating to the transaction log:

I was troubleshooting an issue last week which led to me firing up extended events to look at records being written to the transaction log, I typed into the search bar ‘Transaction’ hoping to find something that would do the trick and didn’t quite find what I was looking for.

After a few more failed attempts I headed to the internet and found a post by Paul Randal describing exactly what I needed for this situation, using the [sqlserver].[transaction_log] event. Hold on, that’s exactly what I searched for.  I ran the T-SQL within his blog post, the event was successfully created and gave me the information I was looking for.

But, as Jess points out, you can still get to it from the GUI.  Read on to learn how.

Grant Fritchey notices something odd about execution plans grabbed from an Extended Events session:

Notice anything missing? Yeah, the first operator, the SELECT operator (technically, not really an operator, but they don’t have any name or designation in the official documentation, so I’m calling them operators). It’s not there. Why do I care?

Because it’s where all the information about the plan itself is stored. Stuff like, Cached Plan Size, Compile Time, Optimizer Statistics Usage, Reason for Early Termination, is all there, properties and details about the plan itself. Now, the weird thing is, if you look to the XML, as shown here, all that data is available:

Read on for Grant’s best guess as to the root cause of the problem.

Chrissy LeMaire solves a bunch of common problems with Extended Events:

So why do people keep using traces? We compiled a list of reasons from Erin Stellato’s Why do YOU avoid Extended Events. And this list is LONG!

• Traces are straightforward and less complex than Extended Events
  Totally seems that way!
• Traces provide a consistent interface for mixed environments
  Whether you use SQL Server 7 or SQL Server 2017, the interface is pretty much the same.
• Traces are faster to setup quick traces
  Just open up Profiler, connect to a server, click a few times and you’re set.
• “Extended Events are more efficient for the SQL Server engine, but not more efficient for the DBA”
  Love this quote.
• People already have a library of Profiler templates
  Including me
• Ignorance of XML / Querying all the generated XML is outrageous
  When I first saw what it takes to query Extended Events, I bailed immediately. I am not learning XPATH, ever.
• Templates work remotely across all instances
  This is also true for Extended Events, but the commenter did not know that.
• XEvents are persistent and must be stopped manually
  Once you close Profiler or restart SQL Server, all non-default traces will disappear. Extended Events will persist until you delete them.
• Ability to import PerfMon data and look at Trace and PerfMon counter data at the same time
  Most people that use Profiler don’t seem to know about this feature but those who do LOVE it. You can read more at Brad McGehee’s Correlating SQL Server Profiler with Performance Monitor.

  Microsoft reportedly has no plans to provide this functionality.

• Consistent user experience across SSAS and Database Engine
  Gotta take their word, I don’t use SSAS.
• It’s easy to train others to use Profiler
  Imagine – if it’s easier to learn Profiler, it’ll be far easier to teach.
• Traces can be easily replayed
  There are a number of Microsoft tools to replay traces, but none to replay Extended Events.
• MS Premier Support still asks for traces
  Likely because they also have tools that they want to work across all supported versions, which still includes SQL Server 2008 R2.
• xe_file_target_read_file is a CPU hog
  This wasn’t listed on Erin’s page but was told to me while I was performing my research.

Whewf! That’s a lot of compelling reasons not to make the switch. So let’s see how we can address each of them using PowerShell. All code listed here can be found at sqlps.io/xecode.

Chrissy & co can’t solve all of them, but they solve a majority.  Click through to see how.

Derik Hammer has a few useful extended events sessions that he’s sharing with us:

Extended Events are the all-around smart choice. They take a little bit of time to get used to, however. With thousands of new events and data points, it can be difficult to create an event session in a pinch. That is why it is important to have event sessions pre-scripted or pre-implemented on your SQL Server instances. A little bit of up-front work can save you a lot of time when you need information on the spot. Having them pre-scripted also prevents you from jumping back to Profiler, which has a much heavier footprint on your server.

When I create Extended Event sessions, I tend to use the SQL Server Management Studio wizard to find the events and actions (additional fields) that I want. Then, I will script it out and save it for later.

Below are five Extended Events sessions that I have found particularly useful and recommend you add to your toolbox.

Click through for all of those scripts, as well as queries to shred the resulting XML.

Gianluca Sartori introduces a new tool to help with event handling in SQL Server:

I am proud to introduce XESmartTarget: the easiest way to interact with extended events sessions without writing a single line of code.

XESmartTarget uses the Extended Events streaming API to connect to a session and execute some actions in response to each event. The actions you perform can be configured in a simple .json file, which controls the Response types and all their properties.

For instance, if you have a session to capture deadlocks, you may want to be notified whenever such an event is captured. All you need to do is configure XESmartTarget to send an email using the EmailResponse class. You can do that by creating a simple .json file with all the parameters needed

This looks quite interesting.