Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Questions about extended events Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 6:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:36 AM
Points: 103, 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.
Post #1366844
Posted Tuesday, October 2, 2012 2:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1366898
Posted Tuesday, October 2, 2012 7:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1367045
Posted Tuesday, October 2, 2012 7:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
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
Post #1367051
Posted Tuesday, October 2, 2012 8:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1367057
Posted Tuesday, October 2, 2012 9:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
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
Post #1367106
Posted Tuesday, October 2, 2012 9:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1367121
Posted Tuesday, October 2, 2012 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:36 AM
Points: 103, 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.
Post #1367202
Posted Tuesday, October 2, 2012 12:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1367226
Posted Friday, April 19, 2013 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 12:29 AM
Points: 1, Visits: 105
What is the impact on server when we are using extended events.

CPU pressure ?

IO issues ?
Post #1444283
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse