How can we improve Microsoft Azure SQL Data Warehouse ?

Resource class override

Having a fixed resource class assigned to a user is too rigid. Most queries can be executed efficiently with a small resource class, but certain operations (e.g. rebuild index) should be run with a larger RC. I need to switch between several user accounts just to be able to have appropriate resources for whatever operation or query I need to run. Users assigned to xlargerc can only execute a single query at a time.

Would be great to be able to set/change the resource class used for executing each statement. Alternatively, being able to execute as a different user could also be a solution, if it were supported.

40 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Brent shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Ben Jarvis commented  ·   ·  Flag as inappropriate

    This would be extremely useful in our use case where we are running some stored procedures to transform data that need the grunt of a large resource class, we then run some SELECT queries to extract the data that need the concurrency benefits of the small resource class.

    Currently we have to run these using 2 individual logins so it would be ideal if we could specify the resource class at query level.

  • AndrewK commented  ·   ·  Flag as inappropriate

    We have ETL stored procedures that need to run as large resource classes for index compression but also contain smaller metadata tracking functionality, that is also crucial for the operation of our DW. We are currently facing problems because after our metadata updates are being blocked by other long running ETL scripts.

    It would be nice if we could switch back and forth between resource classes for a single user

  • Gerhard Brueckl commented  ·   ·  Flag as inappropriate

    totally agree
    I could think of two options:
    - specify the resource class using OPTION / HINT keyword
    - specify the resource class in the connection string

  • Azure DBA commented  ·   ·  Flag as inappropriate

    I absolutely agree. When running at low DWU settings, I'm constantly seeing small queries being queued as a time-consuming query from a large/xlarge resource class is running. With only four resource classes, we shouldn't have to manage 4x the userIDs, but instead should be able to specify the default resource class and promote/demote as needed in any script run.

Feedback and Knowledge Base