Question 480
An Azure SQL operations team investigates: A DBA sees a full scan because an implicit type conversion affects the indexed column. Select TWO complementary answers: the targeted corrective action AND the evidence that verifies it.
Explanation
Correct Answers
The reported Azure SQL or SQL Server condition is: A DBA sees a full scan because an implicit type conversion affects the indexed column. The specific supported administrative response is: Rewrite supported SQL predicates to allow index seeks where possible instead of applying transformations to indexed columns in filters. The technical risk is: Non-sargable filters can force broad scans even when an apparently suitable index exists. Confirm the correction with: The revised query returns identical rows while its plan and logical reads reflect efficient predicate access.
The acceptance evidence for this incident (A DBA sees a full scan because an implicit type conversion affects the indexed column.) is: The revised query returns identical rows while its plan and logical reads reflect efficient predicate access. It directly tests whether the remediation (Rewrite supported SQL predicates to allow index seeks where possible instead of applying transformations to indexed columns in filters.) solves the specified issue, instead of proving an unrelated database control works.
Incorrect Answers
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 case states: A DBA sees a full scan because an implicit type conversion affects the indexed column. The most direct paired action and test is: Rewrite supported SQL predicates to allow index seeks where possible instead of applying transformations to indexed columns in filters.
Resource governor workload classification (3.3) addresses another technical failure: Applying an instance feature to an unsupported Azure SQL database tier or using incorrect classifiers can fail or throttle critical users. This case instead tests Query rewrite for sargable predicates (3.2); the choice measures or changes another part of the system.
Azure SQL compute and storage scaling (3.3) would demonstrate or implement: Before-and-after workload metrics, capacity headroom and connection behavior confirm that the change meets its objective. The paired-answer requirement concerns Query rewrite for sargable predicates (3.2); this alternative does not provide the matching intervention and separate acceptance evidence.