Question 460
An Azure SQL operations team investigates: A DBA needs to compare resource locks in a captured deadlock graph before changing indexes. Select TWO complementary answers: the targeted corrective action AND the evidence that verifies it.
Explanation
Correct Answers
The acceptance evidence for this incident (A DBA needs to compare resource locks in a captured deadlock graph before changing indexes.) is: A concurrency test no longer produces the captured deadlock cycle and database correctness is preserved. It directly tests whether the remediation (Capture SQL deadlock graphs and adjust transaction order, indexing or transaction scope based on the actual cycle.) solves the specified issue, instead of proving an unrelated database control works.
The reported Azure SQL or SQL Server condition is: A DBA needs to compare resource locks in a captured deadlock graph before changing indexes. The specific supported administrative response is: Capture SQL deadlock graphs and adjust transaction order, indexing or transaction scope based on the actual cycle. The technical risk is: A deadlock is a cyclic lock dependency, so increasing lock timeout alone does not remove the conflicting access pattern. Confirm the correction with: A concurrency test no longer produces the captured deadlock cycle and database correctness is preserved.
Incorrect Answers
Index maintenance and fragmentation evidence (3.3) would demonstrate or implement: Maintenance completes within the window and index health and real query performance meet measured expectations. The case states: the targeted corrective action AND the evidence that verifies it. The most direct paired action and test is: A concurrency test no longer produces the captured deadlock cycle and database correctness is preserved.
Azure SQL automatic tuning governance (3.3) addresses another technical failure: Applying optimizer recommendations without observing workload impact can introduce extra index overhead or unstable plans. The required paired action and test concerns Deadlock detection and remediation (3.2); a passing result for this alternative could leave that requirement unresolved.
Intelligent insights for SQL performance diagnosis (3.2) addresses another technical failure: Automatically generated diagnosis may identify a symptom but cannot replace verification of the actual workload and cause. The paired-answer requirement concerns Deadlock detection and remediation (3.2); this alternative does not provide the matching intervention and separate acceptance evidence.