We have moved our EDW to Synapse Serverless SQL.
The data is in a delta lake, which is accessed by views, to make use of the partitioning on the tables.
However we are experiencing "blocking" behavior. Typically what happens is that a user starts a long running query, any following query then seems to "hang"
It might literally be a select 1 n query, which will hang for perhaps 2 minute before returning the value.
So it seems like there is some limit on ressources on serverless sql not related to the underlying storage account, since we experience it even if the source files are on separate storage accounts.
Strangely I cannot see any documentation as to how to avoid this issue. This means we are severely hampered in high traffic hours where the users will experience that all queries take a long time, no matter the size.
Is this ressource limitation documented anywhere, and are there any solutions, besides switching to dedicated pool, with the increased overhead associated with that?