Question 350 Two checkout processes repeatedly become deadlock victims. What is the most appropriate next design step? Use deadlock graphs or Extended Events to identify cycle participants Use SERIALIZABLE isolation only for a justified range-protection requirement Use READ COMMITTED SNAPSHOT where supported and after compatibility review Use SNAPSHOT isolation for transaction-level version consistency where appropriate Explanation Correct Answer A:This implementation diagnoses competing lock requests rather than guessing. Incorrect Answers B:This option prevents phantom rows by stronger isolation at the cost of concurrency. This does not meet the need for a method that diagnoses competing lock requests rather than guessing. C:This choice reduces reader-writer blocking via row versions. This does not meet the need for a method that diagnoses competing lock requests rather than guessing. D:This option provides a consistent snapshot across statements in a transaction. This does not meet the need for a method that diagnoses competing lock requests rather than guessing.
Question 351 A reconciliation transaction must view the same committed row versions across multiple SELECTs. What is the most appropriate next design step? Keep transactions short and order resource access consistently Use SERIALIZABLE isolation only for a justified range-protection requirement Use SNAPSHOT isolation for transaction-level version consistency where appropriate Use rowversion or another optimistic concurrency token Explanation Correct Answer C:This choice provides a consistent snapshot across statements in a transaction. Incorrect Answers A:That technique reduces lock duration and deadlock likelihood. This does not meet the need for a method that provides a consistent snapshot across statements in a transaction. B:This alternative prevents phantom rows by stronger isolation at the cost of concurrency. This does not meet the need for a method that provides a consistent snapshot across statements in a transaction. D:This alternative detects conflicting updates before silently overwriting changes. This does not meet the need for a method that provides a consistent snapshot across statements in a transaction.
Question 352 A nightly report suddenly regresses after a deployment and the previous good plan is needed. What is the most appropriate next design step? Use a targeted covering index after verifying query access patterns Inspect the actual execution plan and runtime metrics Use Query Store to compare historical query plans and regressions Update statistics when estimates are stale after material data changes Explanation Correct Answer C:This implementation retains runtime/plan history across executions. Incorrect Answers A:This option can reduce repetitive lookup I/O for a selective predicate. This does not meet the need for a method that retains runtime/plan history across executions. B:This choice distinguishes the real expensive operators from guesses. This does not meet the need for a method that retains runtime/plan history across executions. D:That technique improves optimizer cardinality information for changed data distributions. This does not meet the need for a method that retains runtime/plan history across executions.
Question 353 A service wants consistent statement-level snapshots without ordinary reader-writer locking. What should the engineer choose? Use SERIALIZABLE isolation only for a justified range-protection requirement Use READ COMMITTED SNAPSHOT where supported and after compatibility review Use rowversion or another optimistic concurrency token Keep transactions short and order resource access consistently Explanation Correct Answer B:This implementation reduces reader-writer blocking via row versions. Incorrect Answers A:That technique prevents phantom rows by stronger isolation at the cost of concurrency. This does not meet the need for a method that reduces reader-writer blocking via row versions. C:That technique detects conflicting updates before silently overwriting changes. This does not meet the need for a method that reduces reader-writer blocking via row versions. D:This choice reduces lock duration and deadlock likelihood. This does not meet the need for a method that reduces reader-writer blocking via row versions.
Question 354 A customer list needs deterministic continuation after each ordered page. What is the most appropriate design decision? Use set-based UPDATE rather than row-by-row cursor processing when suitable Use parameterized SQL and diagnose parameter-sensitive plans Avoid SELECT * when only narrow columns are needed Use bounded pagination with stable ordering Explanation Correct Answer D:This choice limits page sizes while preventing nondeterministic repeated rows. Incorrect Answers A:That technique minimizes repetitive per-row SQL execution overhead. This does not meet the need for a method that limits page sizes while preventing nondeterministic repeated rows. B:This option separates safe parameter handling from plan suitability. This does not meet the need for a method that limits page sizes while preventing nondeterministic repeated rows. C:This choice reduces transferred data and can enable narrower index coverage. This does not meet the need for a method that limits page sizes while preventing nondeterministic repeated rows.
Question 355 A managed cloud database shows rising CPU and its frequently expensive queries need ranking. Which approach would you recommend? Use Query Performance Insight when supported on Azure SQL Use sys.dm_exec_requests and relevant DMVs for active workload investigation Inspect the actual execution plan and runtime metrics Use Query Store to compare historical query plans and regressions Explanation Correct Answer A:This implementation fits because it visualizes resource-heavy database query patterns. That addresses the stated technical constraint. Incorrect Answers B:This alternative reports live executing sessions, wait status and current requests. This does not meet the need for a method that visualizes resource-heavy database query patterns. C:This choice distinguishes the real expensive operators from guesses. This does not meet the need for a method that visualizes resource-heavy database query patterns. D:This option retains runtime/plan history across executions. This does not meet the need for a method that visualizes resource-heavy database query patterns.
Question 356 An API patch should fail when the row changed after the client fetched it. Which solution is most directly applicable? Use SERIALIZABLE isolation only for a justified range-protection requirement Use rowversion or another optimistic concurrency token Use SNAPSHOT isolation for transaction-level version consistency where appropriate Use deadlock graphs or Extended Events to identify cycle participants Explanation Correct Answer B:This choice detects conflicting updates before silently overwriting changes. Incorrect Answers A:This alternative prevents phantom rows by stronger isolation at the cost of concurrency. This does not meet the need for a method that detects conflicting updates before silently overwriting changes. C:This alternative provides a consistent snapshot across statements in a transaction. This does not meet the need for a method that detects conflicting updates before silently overwriting changes. D:This alternative diagnoses competing lock requests rather than guessing. This does not meet the need for a method that detects conflicting updates before silently overwriting changes.
Question 357 A team wants to claim an index improved performance without recorded baseline metrics. Which approach would you recommend? Use query timeouts and retry policies only for transient failures Create a workload baseline before changing performance settings Identify excessive memory grants in query execution statistics Use a proper database service tier and compute sizing after measurement Explanation Correct Answer B:This implementation provides before-and-after evidence for a tuning decision. Incorrect Answers A:This choice avoids masking deterministic inefficient SQL. This does not meet the need for a method that provides before-and-after evidence for a tuning decision. C:That technique investigates sort/hash spill and oversized workspace requirements. This does not meet the need for a method that provides before-and-after evidence for a tuning decision. D:This option aligns provisioned resources with observed demand. This does not meet the need for a method that provides before-and-after evidence for a tuning decision.
Question 358 A batch normalization procedure loops through thousands of independent records. What is the most appropriate design decision? Use an appropriate index for equality filters and range predicates Use parameterized SQL and diagnose parameter-sensitive plans Use set-based UPDATE rather than row-by-row cursor processing when suitable Replace non-sargable predicates with index-friendly comparisons Explanation Correct Answer C:This approach minimizes repetitive per-row SQL execution overhead. Incorrect Answers A:This alternative supports access paths aligned with the query's selective columns. This does not meet the need for a method that minimizes repetitive per-row SQL execution overhead. B:This option separates safe parameter handling from plan suitability. This does not meet the need for a method that minimizes repetitive per-row SQL execution overhead. D:This alternative allows supported ordered index access when expressions hide keys. This does not meet the need for a method that minimizes repetitive per-row SQL execution overhead.
Question 359 A production query is stalled and operations must see its present wait type. What should the SQL developer implement? Use Query Performance Insight when supported on Azure SQL Update statistics when estimates are stale after material data changes Use sys.dm_exec_requests and relevant DMVs for active workload investigation Inspect the actual execution plan and runtime metrics Explanation Correct Answer C:This choice reports live executing sessions, wait status and current requests. Incorrect Answers A:This alternative visualizes resource-heavy database query patterns. This does not meet the need for a method that reports live executing sessions, wait status and current requests. B:This choice improves optimizer cardinality information for changed data distributions. This does not meet the need for a method that reports live executing sessions, wait status and current requests. D:This option distinguishes the real expensive operators from guesses. This does not meet the need for a method that reports live executing sessions, wait status and current requests.
Question 360 An endpoint filters an indexed key after applying a function to every row. Which technical choice addresses this need? Use an appropriate index for equality filters and range predicates Use set-based UPDATE rather than row-by-row cursor processing when suitable Avoid SELECT * when only narrow columns are needed Replace non-sargable predicates with index-friendly comparisons Explanation Correct Answer D:Replace non-sargable predicates with index-friendly comparisons fits because it allows supported ordered index access when expressions hide keys. That addresses the stated technical constraint. Incorrect Answers A:This choice supports access paths aligned with the query's selective columns. This does not meet the need for a method that allows supported ordered index access when expressions hide keys. B:This alternative minimizes repetitive per-row SQL execution overhead. This does not meet the need for a method that allows supported ordered index access when expressions hide keys. C:This alternative reduces transferred data and can enable narrower index coverage. This does not meet the need for a method that allows supported ordered index access when expressions hide keys.