Question 330 An OLTP system has many readers blocked by concurrent short writes at READ COMMITTED. The application cannot change its transaction isolation statements, and the team wants statement-level versioned reads after compatibility review. Which database setting is most appropriate? Keep transactions short and order resource access consistently Use deadlock graphs or Extended Events to identify cycle participants Use SNAPSHOT isolation for transaction-level version consistency where appropriate Use READ COMMITTED SNAPSHOT where supported and after compatibility review Explanation Correct Answer D:This choice reduces reader-writer blocking via row versions. Incorrect Answers A:This alternative reduces lock duration and deadlock likelihood. This does not meet the need for a method that reduces reader-writer blocking via row versions. B:This alternative diagnoses competing lock requests rather than guessing. This does not meet the need for a method that reduces reader-writer blocking via row versions. C:This choice provides a consistent snapshot across statements in a transaction. This does not meet the need for a method that reduces reader-writer blocking via row versions.
Question 331 An administrator proposes changing MAXDOP without measuring representative query behavior. Which implementation is the best fit? Create a workload baseline before changing performance settings Use query timeouts and retry policies only for transient failures Use a proper database service tier and compute sizing after measurement Investigate tempdb contention or space usage with live diagnostics Explanation Correct Answer A:Create a workload baseline before changing performance settings fits because it provides before-and-after evidence for a tuning decision. That addresses the stated technical constraint. Incorrect Answers B:That technique 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:This alternative 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. D:This alternative addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that provides before-and-after evidence for a tuning decision.
Question 332 Several concurrent reports spill heavily into tempdb and queries slow. Which implementation is the best fit? Investigate tempdb contention or space usage with live diagnostics Identify excessive memory grants in query execution statistics Use query timeouts and retry policies only for transient failures Use a proper database service tier and compute sizing after measurement Explanation Correct Answer A:This approach addresses scratch-work bottlenecks in an observed workload. Incorrect Answers B:This option 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. C:This option avoids masking deterministic inefficient SQL. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload. D:This choice aligns provisioned resources with observed demand. This does not meet the need for a method that addresses scratch-work bottlenecks in an observed workload.
Question 333 A list view fetches full LOB payloads but displays only IDs and statuses. What should the SQL developer implement? Replace non-sargable predicates with index-friendly comparisons 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:This implementation reduces transferred data and can enable narrower index coverage. Incorrect Answers A:This alternative 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 choice 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 option 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 334 A heavily updated index shows fragmentation but the table is tiny and unaffected. Which approach would you recommend? Identify excessive memory grants in query execution statistics 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 Explanation Correct Answer C:This approach avoids indiscriminate full rebuilds. Incorrect Answers A:This alternative investigates sort/hash spill and oversized workspace requirements. This does not meet the need for a method that avoids indiscriminate full rebuilds. B:That technique addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that avoids indiscriminate full rebuilds. D:This alternative provides before-and-after evidence for a tuning decision. This does not meet the need for a method that avoids indiscriminate full rebuilds.
Question 335 A production Azure SQL workload consistently saturates its allocated CPU tier. Which solution best satisfies the stated constraint? Use a proper database service tier and compute sizing after measurement Identify excessive memory grants in query execution statistics Investigate tempdb contention or space usage with live diagnostics Use query timeouts and retry policies only for transient failures Explanation Correct Answer A:This implementation fits because it aligns provisioned resources with observed demand. That addresses the stated technical constraint. Incorrect Answers B:This choice investigates sort/hash spill and oversized workspace requirements. This does not meet the need for a method that aligns provisioned resources with observed demand. C:This option addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that aligns provisioned resources with observed demand. D:This option avoids masking deterministic inefficient SQL. This does not meet the need for a method that aligns provisioned resources with observed demand.
Question 336 An Azure SQL team wants portal-level insight into expensive queries and resource trends. What is the most appropriate next design step? Use Query Store to compare historical query plans and regressions Use a targeted covering index after verifying query access patterns Inspect the actual execution plan and runtime metrics Use Query Performance Insight when supported on Azure SQL Explanation Correct Answer D:This implementation fits because it visualizes resource-heavy database query patterns. That addresses the stated technical constraint. Incorrect Answers A:That technique retains runtime/plan history across executions. This does not meet the need for a method that visualizes resource-heavy database query patterns. B:This alternative 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:This option distinguishes the real expensive operators from guesses. This does not meet the need for a method that visualizes resource-heavy database query patterns.
Question 337 A query's hash joins spill to tempdb under memory pressure. What should the SQL developer implement? Investigate tempdb contention or space usage with live diagnostics 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 C:Identify excessive memory grants in query execution statistics fits because it investigates sort/hash spill and oversized workspace requirements. That addresses the stated technical constraint. Incorrect Answers A:This option addresses scratch-work bottlenecks in an observed workload. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements. B:That technique provides before-and-after evidence for a tuning decision. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements. D:This option aligns provisioned resources with observed demand. This does not meet the need for a method that investigates sort/hash spill and oversized workspace requirements.
Question 338 One cached plan performs well for tiny tenants but poorly for large tenants. Which solution best satisfies the stated constraint? Use parameterized SQL and diagnose parameter-sensitive plans Avoid SELECT * when only narrow columns are needed Use set-based UPDATE rather than row-by-row cursor processing when suitable Use an appropriate index for equality filters and range predicates Explanation Correct Answer A:This implementation separates safe parameter handling from plan suitability. Incorrect Answers B:That technique 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. C:This option minimizes repetitive per-row SQL execution overhead. This does not meet the need for a method that separates safe parameter handling from plan suitability. D:This option 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.
Question 339 A report slows after a join change and the optimizer's chosen operators need inspection. Which solution best satisfies the stated constraint? Use Query Performance Insight when supported on Azure SQL Inspect the actual execution plan and runtime metrics Use Query Store to compare historical query plans and regressions Use sys.dm_exec_requests and relevant DMVs for active workload investigation Explanation Correct Answer B:This approach distinguishes the real expensive operators from guesses. Incorrect Answers A:This alternative visualizes resource-heavy database query patterns. 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 choice 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.
Question 340 An API returns a million tickets in a single response. What is the most appropriate design decision? Use parameterized SQL and diagnose parameter-sensitive plans Avoid SELECT * when only narrow columns are needed Use set-based UPDATE rather than row-by-row cursor processing when suitable Use bounded pagination with stable ordering Explanation Correct Answer D:This implementation fits because it limits page sizes while preventing nondeterministic repeated rows. That addresses the stated technical constraint. Incorrect Answers A:This alternative 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. B:This option 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. C: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.