This might turn into a series of blogposts, maybe just one. We’ll see how this develops.
To get started. I’m impersonating a knowing DBA for an Azure Hyperscale instance. There’s some ETL going on and the tooling that’s used seems to do some weird stuff. Now, i can try and run Adam Machanic’s sp_WhoIsActive every second, but that gives me way more info than i need, plus the chance that i’m missing the things I’m looking for.
On to Extended Events. I’ve already got an event running to capture deadlocks, because these things happen and I want to understand the root cause. Because a deadlock when trying to list the tables in the database is weird. Because schema locks only appear when modifying the schema, and listing tables in SSMS isn’t a modification. I hope.
Now I want to create a second Extended Event. One that captures the creation of an index, the creation of an object and the deletion of an object. Not to much weird stuff I’d think. Standard settings for an Extended Event and then this:
The Hyperscale database is a 12 core Gen5 “thing”, that should have plenty of memory.
To check if i was collection too much information I stripped the session down. Just index creation? Same error. Just object deletion? Same error. Just object creation? Same error. Ok, something weird. I changed the data logging from file in a blob storage to ring buffer. Same error. I changed the memory usage. Same error.
I stopped the session catching deadlocks and started the second session. And it ran. So, I can only have one session active at any time?
Weird? Weird!
I switched over to Azure Data Studio because I found an interesting blog by Erin Stellato. She shows how to use Extended Events (wrongly named Profiler) in ADS. If I start the profiler, I can only see or run one session. This supports the hypothesis that it’s only one session you can run at any time.
Microsoft has a few lines in the documentation on the number of sessions and mostly telling us that this warning should point you to running less sessions. But just one seems a bit low.
I’m going to experiment some more with different events and settings, but if you have any information, links or experiences to share, let me know in the comments!
You can also repond to my question on Twitter, where a few kind and very smart people have added their thoughts and hints to get to the bottom of this issue.
Thanks for reading!
Reitse's blog 