March 13, 2013 at 10:03 am
I only recently discovered Extended Events. Why? I don't know!
I jumped in and learned how to use it and then I let the rest of our sql team in on the discovery. One of the team members quickly stated that it's worthless and is too difficult to use and read, and about the only thing it might be good for is locating long running queries. I thought it was easy to create scripts using 2012 and also easy to use ssms to read the xel in table-format.
Am I just dealing with a case of Debbie Downer here or am I just too excited with the shinny-toy? Perfmon seems to be the tool of choice for, Debbie.
Anyone else think that this is a great tool?
What types of things do you prefer to use it for?
Thanks!!
March 13, 2013 at 10:32 am
It is for :
collect event driven information about sql server systems.
Replace the existing SQL profiler concept that most of the DBA depends.
Regards,
Kumar
March 13, 2013 at 12:59 pm
Extended events are the future. Trace events and Profiler are on the deprecation path. Just on that alone, you're doing the right thing. But the fact is, extended events operate with the system at a lower level within the SQL Server operating system than do trace events, so it causes less of an impact than trace. It's also more programatic with better flexibility. There are more events in ex events than in trace. It's just better. Period.
There are currently two drawbacks. The GUI only exists in 2012. A little bit of an issue, but you can get a GUI for 2008 from CodePlex. The Distributed Replay tool that comes with 2012 is still using trace events.
Other than those two issues, there's every reason to use extended events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2013 at 1:38 pm
You are dealing with a Debbie Downer.
XE is far more than what Grant said and obviously far more than Debbie said. XE can get you information about spinlocks, latches, filegrowths, tempdb contention, cpu cycles, deadlocks (including the rare multi-victim deadlock that can only be captured via XE), and the list goes on and on and on.
I have built out some XE sessions that can be captured and stored in a database for longterm analysis as well. There is a lot of power in XE. To top it off, XE is built to interact more efficiently with the Windows OS, keeps getting better, and can be created very easily using powershell scripts.
The underlying issue with Debbie may really be that it is a new technology and takes personal time to learn it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 1:49 pm
Thank you all for the replies! Very much appreciated.
I ran the following script against a database that's having performance problems. The script returns about 400 deadlock records dating from today back to 3/4/2013. A more SR. DBA says that it's ok and typical to have these deadlocks. Many, if not all are ExchangeEvent deadlocks, which indicate parallel deadlocks. Would you agree that this many deadlocks is typical? Just doesn't seem right to me.
select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s
on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
March 13, 2013 at 1:57 pm
It might be typical for your environment. But that doesn't mean it has to be acceptable.
That many parallelism deadlocks can mean that you have a lot of poor performing code, poor performing indexes, or a lack of indexes.
From Bart Duncan on the topic (referenced here):
Workaround #1: Add an index or improve the query to eliminate the need for parallelism. In most cases, the use of parallelism in a query indicates that you have a very large scan, sort, or join that isn't supported by proper indexes. If you tune the query, you will often find that you end up with a much quicker and more efficient plan that doesn't use parallelism, and therefore isn't subject to this type of problem. Of course, in some queries (DSS/OLAP-type queries, in particular) it may be difficult to eliminate all large scans.
Just some food for thought on that one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 2:09 pm
Great!! Thanks All!!
March 13, 2013 at 2:11 pm
We just started using XE to replace our daily traces and gather more performance information. I have one set up to write to an asynchronous file target any time data file or log file grows. Every night, I load the data from the files into a table. I also read hourly from the system_health session and load deadlocks into a table. We are also using them for on-demand troubleshooting and will be building plenty more sessions for our historical monitoring.
Also, we too have lots of deadlocks daily that are a result of years of different business rules being added and having to exist together. However, if the blocker is a process that is not one of the identified one... We log it. The application handles all of the known ones and retries.
Jared
CE - Microsoft
March 13, 2013 at 2:24 pm
That is exactly what I would like to accomplish....performance data into a DBA database table.
I've established a fantastic DBA database using Powershell and the performance tables is the one thing missing. This is critical to establish baselines for troubleshooting performance issues.
March 13, 2013 at 5:33 pm
SQLRNNR (3/13/2013)
You are dealing with a Debbie Downer.XE is far more than what Grant said and obviously far more than Debbie said. XE can get you information about spinlocks, latches, filegrowths, tempdb contention, cpu cycles, deadlocks (including the rare multi-victim deadlock that can only be captured via XE), and the list goes on and on and on.
I have built out some XE sessions that can be captured and stored in a database for longterm analysis as well. There is a lot of power in XE. To top it off, XE is built to interact more efficiently with the Windows OS, keeps getting better, and can be created very easily using powershell scripts.
The underlying issue with Debbie may really be that it is a new technology and takes personal time to learn it.
Thanks for volunteering. I'll have you tech edit Jonathan's book instead of me. Ha!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2013 at 5:37 pm
SQLRNNR (3/13/2013)
It might be typical for your environment. But that doesn't mean it has to be acceptable.That many parallelism deadlocks can mean that you have a lot of poor performing code, poor performing indexes, or a lack of indexes.
From Bart Duncan on the topic (referenced here):
Workaround #1: Add an index or improve the query to eliminate the need for parallelism. In most cases, the use of parallelism in a query indicates that you have a very large scan, sort, or join that isn't supported by proper indexes. If you tune the query, you will often find that you end up with a much quicker and more efficient plan that doesn't use parallelism, and therefore isn't subject to this type of problem. Of course, in some queries (DSS/OLAP-type queries, in particular) it may be difficult to eliminate all large scans.
Just some food for thought on that one.
Minor nit. Parallelism in and of itself is not a bad thing. In fact, when it's running in the right places, the right ways, for the right reasons, it's actually magnificent. I'd reference the recent work in this space from Adam Machanic. It's just that people get it in bad places, for bad reasons and it causes problems then.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2013 at 5:47 pm
When is the book coming out Grant? I am going to be one of the first buyers...
Jared
CE - Microsoft
March 13, 2013 at 5:49 pm
SQLKnowItAll (3/13/2013)
When is the book coming out Grant? I am going to be one of the first buyers...
Ha! Don't hold your breath. I still haven't received the first chapter.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2013 at 5:55 pm
Grant Fritchey (3/13/2013)
SQLRNNR (3/13/2013)
You are dealing with a Debbie Downer.XE is far more than what Grant said and obviously far more than Debbie said. XE can get you information about spinlocks, latches, filegrowths, tempdb contention, cpu cycles, deadlocks (including the rare multi-victim deadlock that can only be captured via XE), and the list goes on and on and on.
I have built out some XE sessions that can be captured and stored in a database for longterm analysis as well. There is a lot of power in XE. To top it off, XE is built to interact more efficiently with the Windows OS, keeps getting better, and can be created very easily using powershell scripts.
The underlying issue with Debbie may really be that it is a new technology and takes personal time to learn it.
Thanks for volunteering. I'll have you tech edit Jonathan's book instead of me. Ha!
YES, please. I was going to ask him to techedit a book on XE for me that I am working on. CRUD, if he is doing one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 5:57 pm
Grant Fritchey (3/13/2013)
SQLRNNR (3/13/2013)
It might be typical for your environment. But that doesn't mean it has to be acceptable.That many parallelism deadlocks can mean that you have a lot of poor performing code, poor performing indexes, or a lack of indexes.
From Bart Duncan on the topic (referenced here):
Workaround #1: Add an index or improve the query to eliminate the need for parallelism. In most cases, the use of parallelism in a query indicates that you have a very large scan, sort, or join that isn't supported by proper indexes. If you tune the query, you will often find that you end up with a much quicker and more efficient plan that doesn't use parallelism, and therefore isn't subject to this type of problem. Of course, in some queries (DSS/OLAP-type queries, in particular) it may be difficult to eliminate all large scans.
Just some food for thought on that one.
Minor nit. Parallelism in and of itself is not a bad thing. In fact, when it's running in the right places, the right ways, for the right reasons, it's actually magnificent. I'd reference the recent work in this space from Adam Machanic. It's just that people get it in bad places, for bad reasons and it causes problems then.
Agreed - parallelism isn't always bad.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply