A few days ago I posted a blog where i ran into an issue where i wanted to run multiple extended events on one database but couldn’t. You can read all about that here.
But, I was really wondering why this was happening. So i started to try things out. I changed databases (a Hyperscale 4 cores database) to eliminate something weird with the database. First thing i tried was running the first two events i found in SSMS. Not entirely random but still a starting point. And, both sessions started. No problem.
So, I went back to my original problem to check if I had encountered a weird glitch. But no, I still couldn’t start more than one trace on the original database.
I stayed on the problematic database to continue work. I created another session and tested that one. Just a random session and that one did play nice with the session that was looking for the deletes etc. But when i wanted to start the deadlock session, no succes. So i could run more than 1 session in the database, just not with the deadlock session.
So my current hypothesis is that the deadlock extended events session is one weird session.
The evening went on, disturbed by dinner and when i came back, Erin Stellato had replied to my twitter thread. This lead me to further investigation to what was happening. Why wouldn’t that session start. I took a good look at the script and saw something weird: ADD TARGET package0.ring_buffer(SET max_memory=(256000)). This max_memory setting. Where does it come from? Does it show up in other events sessions as well? Turns out it doesn’t.
So i created a new session to detect deadlocks to see if this setting deadlock related or not. The new deadlock session didn’t have this max memory option. So i must have set it. And that lead me to the solution.
You can set a value for the package ring buffer. You can use any value from 1 to many MB. But Erin pointed out that if it’s higher that 4 MB, it won’t materialize. You can read all about it in this blog by Jonathan Kehayias (T | B) And the top limit (undocumented as far as I’ve seen) is 78 MB. If you go over, the session will not start. With 78 it will start but won’t materialize. The fact that you can set a high value in this case really means that just because you can doesn’t mean you should.
Finally, we got there. It was fun to dig into this one. Many thanks to Erin (T | B), Grant (T | B) and William (T | B) for thinking along and keeping me on track. With your input we got there. I learned a lot about extended events. I’m a fan now, because you can do quite a lot with them. But if you’re not carefull, you can break stuff too. Just like any other feature on a database really.
Thanks for reading!
Reitse's blog 