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.
Question 381 An API endpoint pays unnecessary network costs because it returns every table column. What should the SQL developer implement? Use bounded pagination with stable ordering Replace non-sargable predicates with index-friendly comparisons Use parameterized SQL and diagnose parameter-sensitive plans Avoid SELECT * when only narrow columns are needed Explanation Correct Answer D:This approach reduces transferred data and can enable narrower index coverage. Incorrect Answers A:This alternative 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. B:That technique allows supported ordered index access when expressions hide keys. This does not meet the need for a method that reduces transferred data and can enable narrower index coverage. C:This option 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.
Question 382 An application sees fast and slow executions of the same parameterized statement due to skew. Which solution is most directly applicable? Use bounded pagination with stable ordering Use parameterized SQL and diagnose parameter-sensitive plans Use an appropriate index for equality filters and range predicates Avoid SELECT * when only narrow columns are needed Explanation Correct Answer B:This implementation fits because it separates safe parameter handling from plan suitability. That addresses the stated technical constraint. Incorrect Answers A:That technique limits page sizes while preventing nondeterministic repeated rows. This does not meet the need for a method that separates safe parameter handling from plan suitability. C:This choice 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. 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 383 An incident responder must identify a currently long-running SQL request without guessing from an old report. What should the SQL developer implement? Use a targeted covering index after verifying query access patterns Use sys.dm_exec_requests and relevant DMVs for active workload investigation Use Query Store to compare historical query plans and regressions Use Query Performance Insight when supported on Azure SQL Explanation Correct Answer B:This approach reports live executing sessions, wait status and current requests. Incorrect Answers A:This choice can reduce repetitive lookup I/O for a selective predicate. This does not meet the need for a method that reports live executing sessions, wait status and current requests. C:This choice retains runtime/plan history across executions. This does not meet the need for a method that reports live executing sessions, wait status and current requests. D:This option 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.
Question 384 A service must distinguish recoverable transient errors from repeatable query design defects. What should the engineer choose? Investigate tempdb contention or space usage with live diagnostics Use targeted index maintenance based on fragmentation and workload Create a workload baseline before changing performance settings Use query timeouts and retry policies only for transient failures Explanation Correct Answer D:This implementation fits because it avoids masking deterministic inefficient SQL. That addresses the stated technical constraint. Incorrect Answers A:This choice addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that avoids masking deterministic inefficient SQL. B:This option avoids indiscriminate full rebuilds. This does not meet the need for a method that avoids masking deterministic inefficient SQL. C:That technique provides before-and-after evidence for a tuning decision. This does not meet the need for a method that avoids masking deterministic inefficient SQL.
Question 385 A bulk transformation produces temporary objects causing allocation contention. Which technical choice addresses this need? Investigate tempdb contention or space usage with live diagnostics Use a proper database service tier and compute sizing after measurement Identify excessive memory grants in query execution statistics Use targeted index maintenance based on fragmentation and workload Explanation Correct Answer A:Investigate tempdb contention or space usage with live diagnostics is the appropriate choice: it addresses scratch-work bottlenecks in an observed workload. The deployment should be tested with representative data. Incorrect Answers B:That technique aligns provisioned resources with observed demand. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload. C:That technique investigates sort/hash spill and oversized workspace requirements. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload. D:This choice avoids indiscriminate full rebuilds. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload.
Question 386 Administrators prefer Azure SQL's built-in query-performance view to a manual trace. What is the most appropriate design decision? Use Query Performance Insight when supported on Azure SQL Use a targeted covering index after verifying query access patterns Inspect the actual execution plan and runtime metrics Use sys.dm_exec_requests and relevant DMVs for active workload investigation Explanation Correct Answer A:This implementation visualizes resource-heavy database query patterns. Incorrect Answers B:This choice can reduce repetitive lookup I/O for a selective predicate. This does not meet the need for a method that visualizes resource-heavy database query patterns. C:That technique distinguishes the real expensive operators from guesses. This does not meet the need for a method that visualizes resource-heavy database query patterns. D:That technique 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.
Question 387 A tuning investigation must compare actual row counts with cardinality estimates. Which solution best satisfies the stated constraint? Inspect the actual execution plan and runtime metrics Use sys.dm_exec_requests and relevant DMVs for active workload investigation Use Query Store to compare historical query plans and regressions Use Query Performance Insight when supported on Azure SQL Explanation Correct Answer A:Inspect the actual execution plan and runtime metrics fits because it distinguishes the real expensive operators from guesses. 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 distinguishes the real expensive operators from guesses. C:This choice retains runtime/plan history across executions. This does not meet the need for a method that distinguishes the real expensive operators from guesses. D:This option visualizes resource-heavy database query patterns. This does not meet the need for a method that distinguishes the real expensive operators from guesses.
Question 388 An import uses a cursor for a transformation that can be expressed as one checked UPDATE. Which technical choice addresses this need? Use set-based UPDATE rather than row-by-row cursor processing when suitable Use bounded pagination with stable ordering Use an appropriate index for equality filters and range predicates Use parameterized SQL and diagnose parameter-sensitive plans Explanation Correct Answer A:This approach minimizes repetitive per-row SQL execution overhead. Incorrect Answers B:This alternative limits page sizes while preventing nondeterministic repeated rows. This does not meet the need for a method that minimizes repetitive per-row SQL execution overhead. C: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. D: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.
Question 389 A plan uses large grants that differ dramatically from expected input rows. What is the most appropriate next design step? Identify excessive memory grants in query execution statistics Use targeted index maintenance based on fragmentation and workload Use a proper database service tier and compute sizing after measurement Use query timeouts and retry policies only for transient failures Explanation Correct Answer A:Identify excessive memory grants in query execution statistics is the appropriate choice: it investigates sort/hash spill and oversized workspace requirements. The deployment should be tested with representative data. Incorrect Answers B:This alternative avoids indiscriminate full rebuilds. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements. C:This choice aligns provisioned resources with observed demand. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements. D:That technique avoids masking deterministic inefficient SQL. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements.
Question 390 A date-window query uses column-side expressions that block useful seeks. What should the engineer choose? 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 Replace non-sargable predicates with index-friendly comparisons Explanation Correct Answer D:Replace non-sargable predicates with index-friendly comparisons is the appropriate choice: it allows supported ordered index access when expressions hide keys. The deployment should be tested with representative data. Incorrect Answers A:That technique limits page sizes while preventing nondeterministic repeated rows. This does not meet the need for a method that allows supported ordered index access when expressions hide keys. B:That technique 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. C:This option 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.