County clerk cashiers report freezes when the end-of-day close job runs while receipt inserts continue. Which performance issue should the SQL AI developer diagnose first?
Select an answer to reveal the explanation.
Short Explanation
When close-out locks the cash drawer while clerks keep ringing receipts, that’s classic blocking—someone’s holding the door while the line keeps trying to walk through.
Full Explanation
County clerk cashiers freezing while the end-of-day close job runs alongside continuing receipt inserts is a classic concurrency symptom: the close session acquires and holds locks (often exclusive locks on aggregates, status rows, or ranges) that concurrent insert sessions need, producing blocking chains and client freezes. The SQL AI developer should diagnose blocking first—who holds what, which resource, wait type, and duration—because fixing lock duration, isolation, indexing, or job scheduling directly addresses the freeze pattern described. Missing Always Encrypted column keys can cause client or parameter failures on encrypted columns; they do not produce the lock-wait freezes between a close job and receipt inserts. Dynamic Data Masking affects result presentation for unauthorized viewers and is not an insert throttle that explains end-of-day lock contention. Schema drift between a Database Project and a read-only replica is a deployment/consistency concern; it does not explain session-level lock waits on the OLTP database where cashiers insert receipts. Exam caveat: blocking is not the same as deadlock. Blocking is a waiter queue behind a holder; deadlocks are cycles that end with a victim abort. Also, READ COMMITTED SNAPSHOT or shorter transactions may reduce reader/writer pain, but you still start by proving the close job is the blocker before changing isolation globally. Snapshotting without measuring can hide symptoms without fixing long exclusive work. Operational check: during a freeze, query sys.dm_exec_requests / sys.dm_tran_locks (or Azure SQL equivalent blocking reports) to identify the close-job session as head blocker; note locked objects and wait types; review close-job transaction length and whether it can run after insert quiet hours or use narrower updates; validate with a controlled rehearsal that receipt inserts no longer queue behind close.