Question 450
An Azure SQL operations team investigates: An application leaves a transaction open after an exception and other sessions pile up behind it. Select TWO complementary answers: the targeted corrective action AND the evidence that verifies it.
Explanation
Correct Answers
The reported Azure SQL or SQL Server condition is: An application leaves a transaction open after an exception and other sessions pile up behind it. The specific supported administrative response is: Inspect waiting sessions, lock modes and head blockers, then address the owning long transaction or isolation design safely. The technical risk is: Killing random sessions or adding indexes blindly can worsen data consistency and prolong lock contention. Confirm the correction with: A controlled concurrent test completes within the expected time without recurring head-blocker waits.
The acceptance evidence for this incident (An application leaves a transaction open after an exception and other sessions pile up behind it.) is: A controlled concurrent test completes within the expected time without recurring head-blocker waits. It directly tests whether the remediation (Inspect waiting sessions, lock modes and head blockers, then address the owning long transaction or isolation design safely.) solves the specified issue, instead of proving an unrelated database control works.
Incorrect Answers
Query rewrite for sargable predicates (3.2) addresses another technical failure: Non-sargable filters can force broad scans even when an apparently suitable index exists. The case states: An application leaves a transaction open after an exception and other sessions pile up behind it. The most direct paired action and test is: Inspect waiting sessions, lock modes and head blockers, then address the owning long transaction or isolation design safely.
Extended events performance capture (3.1) would demonstrate or implement: A controlled workload produces only the expected events and capture overhead stays within approved thresholds. This choice can be useful for that problem, but it does not establish the Blocking chains and lock analysis (3.2) control being assessed here.
Database watcher monitoring and preview status (3.1) addresses another technical failure: Assuming a preview monitoring feature is generally available in every region or requires no permissions can break operational rollout. This case instead tests Blocking chains and lock analysis (3.2); the choice measures or changes another part of the system.