Role Nesting Triples the Nightly Refresh

Hypothesis, in order of likelihood:

1. Role propagation cost grows superlinearly with nesting depth. A change high in the hierarchy re-evaluates every descendant; three levels over 1,150 business roles multiplies evaluations. Compare the per-phase task timings before and after the role model change - the slowdown should sit in role assignment/propagation, not in account aggregation.
2. The refresh is recomputing roles it does not need to. Check whether the refresh is running full rather than delta, and whether role assignment and identity refresh are being run as one pass when they can be separated.
3. Query plans degraded on the role-assignment and entitlement joins because the row counts and cardinality changed. Get the explain plan for the long-running statements.

First fix: flatten the model to two levels, or cap hierarchy depth, and run role assignment as its own task ahead of the identity refresh. That addresses the dominant cost without touching code.

Guardrail before shipping: reducing nesting changes effective access. Diff the computed entitlement sets per identity before and after, and route the difference through review - a performance fix that silently removes access is an incident.

No - pool exhaustion is a symptom, and sizing the pool up would make it worse.

• Long-running queries hold connections for longer. When every connection is occupied by a slow query, new requests queue, threads block waiting for a connection, and the thread pool fills with threads that are doing nothing. Increasing the pool lets more slow queries run concurrently against the same database and lengthens the queue.

Sequence:

1. Fix or index the long-running queries first, then re-measure.
2. Size the connection pool to what the database can serve (check its max connections and CPU headroom), not to the number of application threads. The pool is a throttle; treat it as one.
3. Add statement timeouts so one pathological query cannot occupy a connection indefinitely.
4. On the 30-second workflow timeout: it is client-side, so the server keeps working after the caller gives up. Do not simply raise it. Confirm the request is idempotent (retrying must not create a second access request) before extending the timeout, otherwise the fix converts timeouts into duplicate requests.

Validate on a restored copy, with the same data volume, and measure per phase.

• Restore a recent production backup into a staging environment sized comparably. Running against a small sample will not reproduce a cost that scales with role hierarchy depth and identity count.
• Run the refresh with per-phase timing instrumentation and compare against the production baseline: role assignment, identity refresh, and provisioning. The fix is confirmed only if the phase you targeted actually shrank.
• Recompute effective access for a sample of identities heavy in the hierarchy and diff entitlement sets before and after the flattening. Report the diff by entitlement and by identity count - a small diff still needs a named owner to accept it.
• Prepare a reversible deployment: keep the three-level model exportable and know the time to restore it, because "the business does not want rollback" is a preference, not a guarantee.
• Only then schedule the change with a monitored next run and an alert on refresh duration exceeding the pre-change baseline.