Dynamic Role Processing in V9


Our customer is currently using 6,5k Dynamic Roles in their solution, v8.1.2, which is taking roughly 6 hours to complete. They have since upgraded to v8.2.1.

Question, My understanding is that V9 Job Servers can now process DBQueue tasks, my assumption is that processing Dynamic Roles would be more efficient through horizontal scaling of Job Servers:

-Is this correct to assume?

-What would be the required settings to allow V9 Job servers to handle specifically DBQueue Dynamic Roles processing? would this be a recommended approach?

  • Your assumption is not correct. The DB Agent Service replaces the DBQueue management that was done by the SQL Agent task, not the workload itself. But it reduces the load on the SQL Server produced by the queue management and the SQL Service Broker and removes the requirement for having the SQL Server Agents available (Think Azure SQL). This is especially true for Azure SQL managed instances.

    Since 8.2 you have fine-grained control over which dynamic roles should be calculated immediately and which column changes should trigger the re-calculation. My suggestion is to check if some of your dynamic roles can be switched to the immediate calculation mode instead of the re-calculation by a schedule. This should give you the option to reduce your re-calculation time.