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.
We announced workload isolation via workload groups for public preview at Ignite in Nov., 2019. Workload groups allow you to create your own custom resource classes (among other things). Check out workload classification that allows you to assign requests by more than just login information too!
Ben Jarvis commented
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.
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
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
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.