Question 500
An Azure SQL operations team investigates: A DBA wants to compare query plans before and after refreshing stale column statistics. 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 wants to compare query plans before and after refreshing stale column statistics.) is: A representative query shows improved estimate accuracy and query metrics after a safe statistics update. It directly tests whether the remediation (Maintain optimizer statistics with appropriate auto-update settings or targeted updates when actual data distribution changes warrant it.) solves the specified issue, instead of proving an unrelated database control works.
The reported Azure SQL or SQL Server condition is: A DBA wants to compare query plans before and after refreshing stale column statistics. The specific supported administrative response is: Maintain optimizer statistics with appropriate auto-update settings or targeted updates when actual data distribution changes warrant it. The technical risk is: Stale histograms can lead to poor row estimates and inefficient plan choices even with appropriate indexes. Confirm the correction with: A representative query shows improved estimate accuracy and query metrics after a safe statistics update.
Incorrect Answers
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. This choice can be useful for that problem, but it does not establish the Statistics maintenance and cardinality accuracy (3.3) control being assessed here.
Blocking chains and lock analysis (3.2) addresses another technical failure: Killing random sessions or adding indexes blindly can worsen data consistency and prolong lock contention. The required paired action and test concerns Statistics maintenance and cardinality accuracy (3.3); a passing result for this alternative could leave that requirement unresolved.
Wait statistics and resource bottleneck interpretation (3.1) would demonstrate or implement: The selected remediation measurably reduces the relevant observed wait or bottleneck under comparable load. The paired-answer requirement concerns Statistics maintenance and cardinality accuracy (3.3); this alternative does not provide the matching intervention and separate acceptance evidence.