Azure synapse resource class

Question

I have a login is in xlarge , and another one is smallrc. i have three query .

but seems the performance are similar . I am using DWU100. Any advice ?

Answer 1

There are a few things to look at beyond which resource class is involved that could slow down a query on a larger resource class:

1. If they are not the exact same query, then the first thing to do is to look at the query plans involved. Depending on how the DB is constructed, even similar queries might execute with different query plans resulting in different execution times, or similar times with different amounts of resources. Adjustments to the DB structure or queries could resolve those sorts of issues. Similarly, if the query does not benefit from extra memory, moving to a larger resource class won't speed up the query.
2. There are a few queries, mostly system-related that aren't governed at all by resource class. This is unlikely in your case, since the only SELECT statements included are on system tables, but it is a possible reason.
3. At DWU100 there are only 4 concurrency slots available. Smallrc takes one slot but xlarge takes two. If there are three or four smallrc queries already running then it would need to wait for one or two of them to complete before starting work. In this scenario, it could actually take longer for a xlarge query to complete than a smaller group as it needs more space to open up on the server. Adding additional resources or increasing the workload importance of the xlarge workload may help to mitigate this issue.