During overnight 311 enrichment batches, operators need to see which wait types dominate. Which diagnostic approach should they use?

Querying DMVs that expose wait statistics and related session activity during the batch window is the diagnostic approach operators should use when overnight 311 enrichment batches need to know which wait types dominate. Views such as wait-stats DMVs, session/request DMVs, and related Query Store views reveal whether batches are bottlenecked on IO, CPU, locking, logging, or external calls. That wait-centric diagnosis guides the next fix—indexes, file throughput, isolation, or batch sizing—rather than blind restarts.

Ignoring waits and only counting rows in the enrichment output table fails by concept: throughput counts do not identify the bottleneck type. Disabling all indexes overnight without measuring waits can destroy plans and makes diagnosis harder. Restarting the SQL service hourly instead of collecting wait diagnostics causes outages, clears useful in-memory artifacts, and still fails to explain which waits dominated before the restart.

Exam caveat: Cumulative wait stats need interval deltas during the batch window; a single lifetime snapshot can be skewed by historical noise. Pair waits with the specific session/request running enrichment. Azure SQL may emphasize certain DMVs and Query Store over some on-prem server-level tools, but the wait-analysis concept remains exam-relevant.

Operational check: Before the overnight 311 batch, snapshot wait stats; during and after the batch, compute deltas and identify top waits. Correlate with sys.dm_exec_requests/sys.dm_exec_session_wait_stats (or platform equivalents) for enrichment sessions. Capture Query Store runtime for the batch procedures. Only then change indexes or configuration. Prohibit hourly service restarts as a “tuning” ritual, and store wait delta results with each night’s operational log.