Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a confession to make: I don't use extended events nearly as much as I've been led to believe I should. Any time I see a blog post on them or read about them in documentation, I say "that's neat, but seems contrived". So I'm wondering what real-world problems have you solved with extended events? In order for it to be useful to everyone, detail the problem and the events session that you set up in as much detail as possible.

share|improve this question
3  
Seems like that this question cannot possibly have a single correct answer and will "likely solicit debate, arguments, polling, or extended discussion". Useful for Capturing wait stats for a single operation as the other not very good alternative is polling sys.dm_os_waiting_tasks in a loop. – Martin Smith Sep 21 '12 at 16:40
I agree with your assessment and that there will likely not be a best (i.e. accepted) answer. However, I don't think that it will likely "solicit debate, arguments, polling, or extended discussion". That is, for each person's solution, it's something that they actually did and unlikely to be debated. And it's not a popularity contest (though I plan to use all the votes I can in upvoting answers here). – Ben Thul Sep 21 '12 at 17:48
4  
But it will be a poll question, and regardless of the quality of the poll, such questions are off-topic here. I suggest reading some blogs for real-world use cases for extended events. Start with Jonathan Kehayias: sqlskills.com/blogs/jonathan/category/Extended-Events.aspx – Aaron Bertrand Sep 21 '12 at 18:19

closed as not constructive by Jon Seigel, Aaron Bertrand, Thomas Stringer, Martin Smith, Mark Storey-Smith Sep 23 '12 at 22:48

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or specific expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, see the FAQ for guidance.

2 Answers

up vote 3 down vote

There are TONS of theoritical ones but I'll stick to 1 that I used by using extended events, then another that a tool I demo'd found with extended events:

Had to build a system that met the SISR and PCI tier 1 requirements for a tier 1 telco. They required us to log every SA log in. Using extended events was a lot more lightweight and easy to manage than having to worry about profiler always being on.

2 -TempDB Contention. A query would run 1 second, half a second, 30 seconds, it was all over the place. The tool used extended events to break down the wait types and I saw PAGELATCH_SH was really high. Turns out tempdb did not have enough data files to optimize all the CPUS (32 cores) that the server had running and we had tempdb contention. Using the wait stats per query really helped figure that issue out fast.

-Was migrating a back office SQL cluster to a different data center, trying to keep downtime to 1 minute or less. While testing the new data center I would get major timeouts. Timeouts were usually caused by sql server however looking at the wait type of NETWORK_IO we were able to trace it to a firewall between the DCs that was pegged at 100% CPU dropping packets faster than monica lewinsky. Yea that's right, my last example had Monica Lewinski lol.

share|improve this answer
up vote 0 down vote

Fact is, Profiler has been listed for deprecation:

http://msdn.microsoft.com/en-us/library/ms181091.aspx

Extended Events is the future.

share|improve this answer
Yes... I get that that is (and has been) the party line since its introduction in SQL 2008. But for something that is supposedly the future, they didn't really focus enough on usability so as to engender adoption. Just my opinion. – Ben Thul Sep 24 '12 at 9:56
How is it not usable? You get full UI support and all of the events from Trace in 2012 and the amount of content around it has exploded as a result. If you are on 2008/2008R2, there are limitations but you can download the SSMS Addin I wrote from Codeplex and get into Extended Events with little effort. (extendedeventmanager.codeplex.com) – Jonathan Kehayias Sep 26 '12 at 1:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.