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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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 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, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.
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.
Fact is, Profiler has been listed for deprecation:
Extended Events is the future.