Question 370 A dashboard retrieves hundreds of unused columns from large fact rows. Which change most directly meets the requirement? Use parameterized SQL and diagnose parameter-sensitive plans Avoid SELECT * when only narrow columns are needed Use bounded pagination with stable ordering Use set-based UPDATE rather than row-by-row cursor processing when suitable Explanation Correct Answer B:Avoid SELECT * when only narrow columns are needed fits because it reduces transferred data and can enable narrower index coverage. That addresses the stated technical constraint. Incorrect Answers A:This choice separates safe parameter handling from plan suitability. This does not meet the need for a method that reduces transferred data and can enable narrower index coverage. C:This option limits page sizes while preventing nondeterministic repeated rows. This does not meet the need for a method that reduces transferred data and can enable narrower index coverage. D:This alternative minimizes repetitive per-row SQL execution overhead. This does not meet the need for a method that reduces transferred data and can enable narrower index coverage.
Question 371 An incident needs the exact objects and lock modes in a deadlock graph. Which solution is most directly applicable? Keep transactions short and order resource access consistently 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 D:This implementation diagnoses competing lock requests rather than guessing. Incorrect Answers A:That technique reduces lock duration and deadlock likelihood. This does not meet the need for a method that diagnoses competing lock requests rather than guessing. B:This choice detects conflicting updates before silently overwriting changes. This does not meet the need for a method that diagnoses competing lock requests rather than guessing. C: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 372 A table is bulk-updated and a formerly efficient plan estimates very few matching rows. Which solution best satisfies the stated constraint? Use Query Performance Insight when supported on Azure SQL Update statistics when estimates are stale after material data changes Inspect the actual execution plan and runtime metrics Use a targeted covering index after verifying query access patterns Explanation Correct Answer B:Update statistics when estimates are stale after material data changes is the appropriate choice: it improves optimizer cardinality information for changed data distributions. The deployment should be tested with representative data. Incorrect Answers A:That technique visualizes resource-heavy database query patterns. This does not meet the need for a method that improves optimizer cardinality information for changed data distributions. C:This option distinguishes the real expensive operators from guesses. This does not meet the need for a method that improves optimizer cardinality information for changed data distributions. D:This choice can reduce repetitive lookup I/O for a selective predicate. This does not meet the need for a method that improves optimizer cardinality information for changed data distributions.
Question 373 A large index experiences measured read degradation and needs evaluated maintenance. What is the most appropriate next design step? Investigate tempdb contention or space usage with live diagnostics Create a workload baseline before changing performance settings Use query timeouts and retry policies only for transient failures Use targeted index maintenance based on fragmentation and workload Explanation Correct Answer D:This approach avoids indiscriminate full rebuilds. Incorrect Answers A:This option addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that avoids indiscriminate full rebuilds. B:That technique provides before-and-after evidence for a tuning decision. This does not meet the need for a method that avoids indiscriminate full rebuilds. C:This alternative avoids masking deterministic inefficient SQL. This does not meet the need for a method that avoids indiscriminate full rebuilds.
Question 374 A stored procedure query has widely varying cardinalities across input customer IDs. Which implementation is the best fit? Use parameterized SQL and diagnose parameter-sensitive plans Use an appropriate index for equality filters and range predicates Replace non-sargable predicates with index-friendly comparisons Avoid SELECT * when only narrow columns are needed Explanation Correct Answer A:This implementation separates safe parameter handling from plan suitability. Incorrect Answers B:That technique supports access paths aligned with the query's selective columns. This does not meet the need for a method that separates safe parameter handling from plan suitability. C:This option allows supported ordered index access when expressions hide keys. This does not meet the need for a method that separates safe parameter handling from plan suitability. D:This option reduces transferred data and can enable narrower index coverage. This does not meet the need for a method that separates safe parameter handling from plan suitability.
Question 375 A workload saturates tempdb space during a large sort operation. What is the most appropriate design decision? Use targeted index maintenance based on fragmentation and workload Investigate tempdb contention or space usage with live diagnostics Use query timeouts and retry policies only for transient failures Create a workload baseline before changing performance settings Explanation Correct Answer B:This approach addresses scratch-work bottlenecks in an observed workload. Incorrect Answers A:That technique avoids indiscriminate full rebuilds. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload. C:This choice avoids masking deterministic inefficient SQL. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload. D:That technique provides before-and-after evidence for a tuning decision. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload.
Question 376 A customer endpoint reads a few projected columns thousands of times per minute. What should the SQL developer implement? Use a targeted covering index after verifying query access patterns Use Query Store to compare historical query plans and regressions Use sys.dm_exec_requests and relevant DMVs for active workload investigation Use Query Performance Insight when supported on Azure SQL Explanation Correct Answer A:This approach can reduce repetitive lookup I/O for a selective predicate. Incorrect Answers B:This choice retains runtime/plan history across executions. This does not meet the need for a method that can reduce repetitive lookup I/O for a selective predicate. C:This choice reports live executing sessions, wait status and current requests. This does not meet the need for a method that can reduce repetitive lookup I/O for a selective predicate. D:This alternative visualizes resource-heavy database query patterns. This does not meet the need for a method that can reduce repetitive lookup I/O for a selective predicate.
Question 377 A team needs evidence of which statements participate in a recurring deadlock cycle. Which change most directly meets the requirement? Use SERIALIZABLE isolation only for a justified range-protection requirement Use deadlock graphs or Extended Events to identify cycle participants Use READ COMMITTED SNAPSHOT where supported and after compatibility review Keep transactions short and order resource access consistently Explanation Correct Answer B:This implementation diagnoses competing lock requests rather than guessing. 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 diagnoses competing lock requests rather than guessing. C:That technique 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 choice reduces lock duration and deadlock likelihood. This does not meet the need for a method that diagnoses competing lock requests rather than guessing.
Question 378 A document table needs optimistic conflict checks without holding long user-session locks. Which approach would you recommend? Use SNAPSHOT isolation for transaction-level version consistency where appropriate Use deadlock graphs or Extended Events to identify cycle participants Keep transactions short and order resource access consistently Use rowversion or another optimistic concurrency token Explanation Correct Answer D:This choice detects conflicting updates before silently overwriting changes. Incorrect Answers A: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. B: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. C:This choice reduces lock duration and deadlock likelihood. This does not meet the need for a method that detects conflicting updates before silently overwriting changes.
Question 379 A cost review finds substantial idle headroom and wants evidence-based rightsizing. Which solution is most directly applicable? Use query timeouts and retry policies only for transient failures Create a workload baseline before changing performance settings Use a proper database service tier and compute sizing after measurement Identify excessive memory grants in query execution statistics Explanation Correct Answer C:This choice aligns provisioned resources with observed demand. Incorrect Answers A:This alternative avoids masking deterministic inefficient SQL. This does not meet the need for a method that aligns provisioned resources with observed demand. B:This option provides before-and-after evidence for a tuning decision. This does not meet the need for a method that aligns provisioned resources with observed demand. D:This alternative investigates sort/hash spill and oversized workspace requirements. This does not meet the need for a method that aligns provisioned resources with observed demand.
Question 380 A feed consumer complains that pages overlap because results lack a stable tie-breaker. What should the engineer choose? Replace non-sargable predicates with index-friendly comparisons Use bounded pagination with stable ordering Use an appropriate index for equality filters and range predicates Use set-based UPDATE rather than row-by-row cursor processing when suitable Explanation Correct Answer B:This choice limits page sizes while preventing nondeterministic repeated rows. Incorrect Answers A:This option allows supported ordered index access when expressions hide keys. This does not meet the need for a method that limits page sizes while preventing nondeterministic repeated rows. C:This alternative supports access paths aligned with the query's selective columns. This does not meet the need for a method that limits page sizes while preventing nondeterministic repeated rows. D:This choice 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.