Or perhaps another way to phrase that is, Microsoft is up to something with parallelism. I don’t know how long it’s been this way – could even be since the launch of Azure SQL DB Serverless – but I just now noticed it while helping a client with a slow query.
When you view an actual execution plan for a parallel query in SQL Server 2022 & prior, and in regular Azure SQL DB, you can right-click on an operator to see how the work is broken up across threads:
This is useful when you’re troubleshooting problems with unbalanced parallelism. In Azure SQL DB Serverless, though, query plans no longer break up work by thread:
We just get one number for “All threads”. (Disregard the different number of reads & rows between screenshots – I’m working with different databases across the different versions.)
Similarly, in the boxed product, as the cool kids say, there’s a thread stats section for the plan overall:
It’s useful for seeing how many cores got involved, and how many branches of the query were able to execute simultaneously. Azure SQL DB Serverless? Not so much:
That’s it – I didn’t cut the screenshot off, that’s just where that window ends. (Same thing in the XML of the plan – there’s no additional details on parallelism.)
In a magical ideal world, I’d hope this lack of details is an indication that Microsoft is working on parallelism performance improvements. Perhaps, like our inability to set Cost Threshold for Parallelism in Azure SQL DB, it’s a sign that soon, things will Just Work™ without us having to configure anything or monitor anything. I would even love to see the number of cores change dynamically during query runtime, based on how much data was moving through the plan.
Back here in this world, the world where I somehow managed to fall into my pool, I don’t think we’re anywhere near that promised land. After all, I’m writing this post because a client was hitting parallelism performance issues in Azure SQL DB. In this client’s case, I used the same fixes I discuss in this Mastering Query Tuning module. We couldn’t use Azure’s execution plans to verify that work was better balanced across threads, but the dramatically reduced runtimes were more than enough proof.