Questions about extended events

Question

Post reply

Questions about extended events

SQLUserC

SSCrazy

Points: 2345
More actions
October 1, 2012 at 6:30 pm

#267557

Hey all,
I am trying to learn more about extended events. I have been using SQL Profiler to help me troubleshoot SQL performance issues and errors in my Visual Studio projects. I learned about extended events recently and would like to use them instead of SQL Profiler. Is this an appropriate use of extended events? So far, I've been having some trouble using it.
My understanding is that you can only view results while the extended events session is running, and that once the session is stopped, the results are discarded. How can I prevent this from happening? I would like to be able to view the events after the session is stopped.
Is there a way to do this? Otherwise, while the session is running, is there a way to export the results? Seeing as how the results will be in XML, I was thinking of using the Excel XML Source Pane to parse them. What do other people use? I would greatly appreciate any advice that anyone can provide. Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Mapping XEvents to Trace Events by Jonathan Kehayias

The mapping is more complete in SQL 2012.

Regarding the results being discarded, that's true for some targets like the ring buffer. You can target a file too.

Jonathan Kehayias has an XEvent a Day series that is a great way to get started because it starts at the beginning. It was written when 2012 was still in CTP so most content is for 2008 but I haven't seen a better intro anywhere.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

I recently recorded a five hour Introduction to Extended Events course on Pluralsight that answers every question that you ask here:

SQL Server:Introduction to Extended Events

If you need data to be persisted when the event session is stopped you have two options, use the file target, or read the data from the target into a table before stopping the session. If you are using an in-memory target, you can drop all of the events from the event session to stop data collection, and then query the target data as necessary from the DMVs.

Unless you are on 2012, using Extended Events for general troubleshooting doesn't make sense over using SQL Trace because it doesn't expose enough information in 2008. There are more advanced diagnostic and troubleshooting scenarios in 2008 where Extended Events can help with solving complex problems, but most scenarios Trace still makes the most sense in 2008.

If you are new to Extended Events, and on SQL Server 2008/2008R2, download the SSMS Addin for SQL Server 2008 that I wrote and it will help you with using the feature better. In SQL 2012, there is a built-in UI in SSMS already.

Beyond this, read the posts in my blog category for Extended Events, I've written over 300 pages of content the last four years on the topic, and the answers to your questions have been covered in that content as well.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

it would be a good idea to identify what events your trying to capture and then look up an appropriate DMV.

Else if you right click the extended event in sql 2012 you will get an optio for data storage which will allow you to store the data into a file.

Keep in mind that you could end up getting much more information than you actually need so its usually better to stick with a Server side trace or DMV if your only interested in troubleshooting or basic monitoring.

Jayanth Kurup[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Jayanth_Kurup (10/2/2012)
Keep in mind that you could end up getting much more information than you actually need so its usually better to stick with a Server side trace or DMV if your only interested in troubleshooting or basic monitoring.

For the exact same events in SQL Trace, you will get the same amount of events generated, so using a server-side Trace won't make a difference. This is why you have the ability to create complex predicates in Extended Events to filter the events at the firing point, and reduce the performance impact that diagnostic data collection has on the server, which is not possible for server-side trace, where the filter is applied at the Trace level across all of the events, and only after the event has fired and collected all of the events data.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

Thanks !! i wasnt aware the filters in a server side trace was applied only after the info was collected. I suggested the server side trace mainly to avoid the network overhead. On a related note , how about data collectors , do they work the same as a trace ( i guess they would coz you can create a template for a dc from a trace).

Jayanth Kurup[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Jayanth_Kurup (10/2/2012)
Thanks !! i wasnt aware the filters in a server side trace was applied only after the info was collected.

Yes, this is one of the fundamental architectural differences between Trace and Extended Events. Extended Events evaluate the predicate at the earliest point possible, and then collect additional data from actions after the event is guaranteed to fire and be consumed by an event session.

I suggested the server side trace mainly to avoid the network overhead.

There isn't a network overhead with Extended Events though, so I don't understand this statement. Even in 2012 where you can use the streaming target with the Live Data viewer in SSMS, the memory buffers are duplicated server side to prevent the live stream from impacting performance of the server workload. If the live stream gets behind, the engine will kill it, disconnecting the client. The entire implementation is designed to try and prevent negative impacts from default configurations with a few exceptions around expensive events.

On a related note , how about data collectors , do they work the same as a trace ( i guess they would coz you can create a template for a dc from a trace).

If it is a trace data collector yes, but you can have other data collectors as well which wouldn't function like trace.

SQLUserC SSCrazy Points: 2345 More actions · Answer 7

Thanks for all the great replies. I had heard of this mapping of Profiler events to extended events, Jonathan, so it's nice to have a link to it. 🙂

I will read through some of this stuff. I'm on SQL 2012 so I'd like to get familiar with the Extended Events as I'm hoping I can use them to tune the performance of my SQL applications.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

SQLUserC (10/2/2012)
Thanks for all the great replies. I had heard of this mapping of Profiler events to extended events, Jonathan, so it's nice to have a link to it. 🙂
I will read through some of this stuff. I'm on SQL 2012 so I'd like to get familiar with the Extended Events as I'm hoping I can use them to tune the performance of my SQL applications.

I would personally look at what you can do with DMVs and the plan cache for tuning before running an Extended Events session or Trace against a server. You can get A LOT of data by querying the DMVs and the plan cache. I very rarely, almost never, use Trace or Extended Events for performance tuning. If you have an error or suspected bug, or just want to see how something works, that's where I go to Trace or Extended Events for diagnostic data collection.

nellimarlakiran Valued Member Points: 67 More actions · Answer 9

What is the impact on server when we are using extended events.

CPU pressure ?

IO issues ?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

nellimarlakiran (4/19/2013)
What is the impact on server when we are using extended events.
CPU pressure ?
IO issues ?

It depends on the events being collected, the workload, and what targets are being used.

http://www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

http://www.sqlperformance.com/2013/03/sql-plan/showplan-impact