Question 530
An Azure SQL operations team investigates: A DBA needs to review an automatically reverted tuning action after performance deteriorated. 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 review an automatically reverted tuning action after performance deteriorated.) is: The tuned database meets measured performance targets and automatic actions or reversions are recorded. It directly tests whether the remediation (Use supported automatic tuning or index and plan recommendations with review, telemetry and rollback checks.) 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 review an automatically reverted tuning action after performance deteriorated. The specific supported administrative response is: Use supported automatic tuning or index and plan recommendations with review, telemetry and rollback checks. The technical risk is: Applying optimizer recommendations without observing workload impact can introduce extra index overhead or unstable plans. Confirm the correction with: The tuned database meets measured performance targets and automatic actions or reversions are recorded.
Incorrect Answers
Statistics maintenance and cardinality accuracy (3.3) addresses another technical failure: Stale histograms can lead to poor row estimates and inefficient plan choices even with appropriate indexes. The case states: the targeted corrective action AND the evidence that verifies it. The most direct paired action and test is: The tuned database meets measured performance targets and automatic actions or reversions are recorded.
Deadlock detection and remediation (3.2) addresses another technical failure: A deadlock is a cyclic lock dependency, so increasing lock timeout alone does not remove the conflicting access pattern. This case instead tests Azure SQL automatic tuning governance (3.3); the choice measures or changes another part of the system.
Index design for selective oltp queries (3.2) would demonstrate or implement: A representative query shows lower logical reads or execution time without unacceptable write overhead. The paired-answer requirement concerns Azure SQL automatic tuning governance (3.3); this alternative does not provide the matching intervention and separate acceptance evidence.