Question 440
An Azure SQL operations team investigates: An optimizer plan changes after parameter values vary and the team needs to inspect actual operators. Select TWO complementary answers: the targeted corrective action AND the evidence that verifies it.
Explanation
Correct Answers
The acceptance evidence for this incident (An optimizer plan changes after parameter values vary and the team needs to inspect actual operators.) is: The revised plan and measured reads show the intended operator change with correct query results. It directly tests whether the remediation (Review actual or estimated execution plans and row-estimate differences to identify avoidable scans or expensive operators.) solves the specified issue, instead of proving an unrelated database control works.
The reported Azure SQL or SQL Server condition is: An optimizer plan changes after parameter values vary and the team needs to inspect actual operators. The specific supported administrative response is: Review actual or estimated execution plans and row-estimate differences to identify avoidable scans or expensive operators. The technical risk is: Choosing an index solely from table size without seeing the predicate and plan can waste resources. Confirm the correction with: The revised plan and measured reads show the intended operator change with correct query results.
Incorrect Answers
Azure monitor metrics and diagnostic settings (3.1) addresses another technical failure: Platform metrics alone may miss a query-level root cause while unconfigured diagnostics leave gaps in incident history. This choice can be useful for that problem, but it does not establish the Execution plan scans and cardinality estimation (3.2) control being assessed here.
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 case instead tests Execution plan scans and cardinality estimation (3.2); the choice measures or changes another part of the system.
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. The required paired action and test concerns Execution plan scans and cardinality estimation (3.2); a passing result for this alternative could leave that requirement unresolved.