SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Questions about extended events


Questions about extended events

Author
Message
SQLUserC
SQLUserC
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

Group: General Forum Members
Points: 531 Visits: 328
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40298 Visits: 14413
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
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1818
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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Jayanth_Kurup
Jayanth_Kurup
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5723 Visits: 1351
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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1818
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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Jayanth_Kurup
Jayanth_Kurup
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5723 Visits: 1351
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
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1818
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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
SQLUserC
SQLUserC
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

Group: General Forum Members
Points: 531 Visits: 328
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
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1818
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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
nellimarlakiran
nellimarlakiran
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 124
What is the impact on server when we are using extended events.

CPU pressure ?

IO issues ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search