Question 470
An Azure SQL operations team investigates: An index recommendation is accepted without checking whether an equivalent existing index already serves the query. Select TWO complementary answers: the targeted corrective action AND the evidence that verifies it.
Explanation
Correct Answers
The acceptance evidence for this incident (An index recommendation is accepted without checking whether an equivalent existing index already serves the query.) is: A representative query shows lower logical reads or execution time without unacceptable write overhead. It directly tests whether the remediation (Create or refine a supported rowstore index that matches frequent predicates, joins and coverage needs after evaluating writes and storage.) solves the specified issue, instead of proving an unrelated database control works.
The reported Azure SQL or SQL Server condition is: An index recommendation is accepted without checking whether an equivalent existing index already serves the query. The specific supported administrative response is: Create or refine a supported rowstore index that matches frequent predicates, joins and coverage needs after evaluating writes and storage. The technical risk is: Excessive or poorly ordered indexes can increase DML cost while failing to optimize the target filter. Confirm the correction with: A representative query shows lower logical reads or execution time without unacceptable write overhead.
Incorrect Answers
Execution plan scans and cardinality estimation (3.2) addresses another technical failure: Choosing an index solely from table size without seeing the predicate and plan can waste resources. This case instead tests Index design for selective oltp queries (3.2); the choice measures or changes another part of the system.
Database-scoped configuration controls (3.3) addresses another technical failure: Applying server-wide settings for one database's issue can unintentionally affect unrelated workloads. The required paired action and test concerns Index design for selective oltp queries (3.2); a passing result for this alternative could leave that requirement unresolved.
SQL workload performance baselining (3.1) would demonstrate or implement: Before-and-after metrics for a comparable workload window show a measured effect on the stated objective. The paired-answer requirement concerns Index design for selective oltp queries (3.2); this alternative does not provide the matching intervention and separate acceptance evidence.