100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 560
A service has intermittent timeout and unknown query cause. A test instead finds: No historical plans exist for the incident window. Which underlying issue best explains this finding?
- Plan forcing conflicts with changed query or schema shape
- Query performance history was not captured
- Available compute or service tier remains undersized
- Relevant filtered cardinality information is absent
Explanation
Correct Answer
B:Query performance history was not captured. It accounts for the specific observed failure: no historical plans exist for the incident window. The intended solution is enable query store and capture representative waits with the required setup.
Incorrect Answers
A:Plan forcing conflicts with changed query or schema shape. This concerns forced plan fails to apply after schema change. Instead, query performance history was not captured.
C:Available compute or service tier remains undersized. This concerns cpu remains pinned after index tuning. Instead, query performance history was not captured.
D:Relevant filtered cardinality information is absent. This concerns optimizer assumes uniform distribution for rare status. Instead, query performance history was not captured.
Question 561
A plan uses a hash join and spills to tempdb. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Review memory grant estimation and statistics
- Update relevant statistics after evidence of drift
- Correct join cardinality and relationship predicates
- Monitor tempdb space and version-store consumption
- Refresh stats or tune cardinality before forcing hints
Explanation
Correct Answers
A:Review memory grant estimation and statistics. The implementation is refresh stats or tune cardinality before forcing hints; success can be checked by inspect spill warnings and granted vs used memory. The method addresses the stated data and workload constraint.
E:This is the concrete configuration of the specified design: refresh stats or tune cardinality before forcing hints. The choice makes the implementation testable in the given workload.
Incorrect Answers
B:Update relevant statistics after evidence of drift. This concerns a high-traffic analytics query runs slowly after statistics become stale. Instead, review memory grant estimation and statistics.
C:Correct join cardinality and relationship predicates. This concerns a join unexpectedly multiplies report rows. Instead, review memory grant estimation and statistics.
D:Monitor tempdb space and version-store consumption. This concerns a server experiences severe tempdb waits from row versioning. Instead, review memory grant estimation and statistics.
Question 562
A plan uses a hash join and spills to tempdb. The team selects: Review memory grant estimation and statistics. Which implementation action puts this design into practice?
- Join on full composite business key
- Force a known-good Query Store plan when supported
- Inspect WRITELOG waits and log file latency
- Refresh stats or tune cardinality before forcing hints
Explanation
Correct Answer
D:Refresh stats or tune cardinality before forcing hints. This is the concrete setup for review memory grant estimation and statistics, and inspect spill warnings and granted vs used memory supplies a direct confirmation.
Incorrect Answers
A:Join on full composite business key. This concerns a join unexpectedly multiplies report rows. Here, refresh stats or tune cardinality before forcing hints.
B:Force a known-good Query Store plan when supported. This concerns reusing last month's stable query plan after regression. Here, refresh stats or tune cardinality before forcing hints.
C:Inspect WRITELOG waits and log file latency. This concerns a high-write SQL server suffers excessive transaction log latency. Here, refresh stats or tune cardinality before forcing hints.
Question 563
A plan uses a hash join and spills to tempdb. After implementing the design, which check most directly validates the expected behavior?
- Inspect plan for Sort operator removal
- Compare P95 transaction commit time and I/O metrics
- Inspect spill warnings and granted vs used memory
- Compare expected vs actual rows at each join
Explanation
Correct Answer
C:Inspect spill warnings and granted vs used memory. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that memory grant was insufficient for actual rows.
Incorrect Answers
A:Inspect plan for Sort operator removal. This concerns a t-SQL query sorts millions of rows unnecessarily. Instead, inspect spill warnings and granted vs used memory.
B:Compare P95 transaction commit time and I/O metrics. This concerns a high-write SQL server suffers excessive transaction log latency. Instead, inspect spill warnings and granted vs used memory.
D:Compare expected vs actual rows at each join. This concerns a join unexpectedly multiplies report rows. Instead, inspect spill warnings and granted vs used memory.
Question 564
A plan uses a hash join and spills to tempdb. A test instead finds: Hash join writes many workfiles to tempdb. Which underlying issue best explains this finding?
- Memory grant was insufficient for actual rows
- Plan forcing conflicts with changed query or schema shape
- Join omitted a key column and formed fanout
- Offline rebuild or lock policy conflicts with workload
Explanation
Correct Answer
A:Memory grant was insufficient for actual rows. It accounts for the specific observed failure: hash join writes many workfiles to tempdb. The intended solution is review memory grant estimation and statistics with the required setup.
Incorrect Answers
B:Plan forcing conflicts with changed query or schema shape. This concerns forced plan fails to apply after schema change. Here, memory grant was insufficient for actual rows.
C:Join omitted a key column and formed fanout. This concerns customer totals are inflated by duplicated detail rows. Here, memory grant was insufficient for actual rows.
D:Offline rebuild or lock policy conflicts with workload. This concerns business transactions stall during index maintenance. Here, memory grant was insufficient for actual rows.
Question 565
An index on a date column is ignored when WHERE converts the column. Which database design or engineering approach most directly meets this requirement?
- Schedule/rescope maintenance and evaluate online index operations
- Make predicate SARGable by comparing date ranges directly
- Inspect Query Store plan history and regressions
- Review memory grant estimation and statistics
Explanation
Correct Answer
B:Make predicate SARGable by comparing date ranges directly. The implementation is rewrite convert(date,createdat)=@d to half-open datetime range; success can be checked by verify plan seeks date index. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Schedule/rescope maintenance and evaluate online index operations. This concerns an overnight index rebuild causes daytime blocking. Here, make predicate SARGable by comparing date ranges directly.
C:Inspect Query Store plan history and regressions. This is relevant when an API has latency spikes after an index disappears. Here, make predicate SARGable by comparing date ranges directly.
D:Review memory grant estimation and statistics. This is relevant when a plan uses a hash join and spills to tempdb. Here, make predicate SARGable by comparing date ranges directly.
Question 566
An index on a date column is ignored when WHERE converts the column. The team selects: Make predicate SARGable by comparing date ranges directly. Which implementation action puts this design into practice?
- Test filtered index and up-to-date subset statistics
- Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range
- Select supported vCore compute tier based on measured bottlenecks
- Set serializable isolation and appropriate indexed predicates
Explanation
Correct Answer
B:Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range. This is the concrete setup for make predicate sargable by comparing date ranges directly, and verify plan seeks date index supplies a direct confirmation.
Incorrect Answers
A:Test filtered index and up-to-date subset statistics. This concerns an API query reads a large table with many skewed statuses. Here, rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range.
C:Select supported vCore compute tier based on measured bottlenecks. This concerns more resources for predictable query load. Here, rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range.
D:Set serializable isolation and appropriate indexed predicates. This concerns preventing new qualifying rows during an in-flight range check. Here, rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range.
Question 567
An index on a date column is ignored when WHERE converts the column. After implementing the design, which check most directly validates the expected behavior?
- Verify forced plan status and performance under live skew
- Compare CPU and latency before/after scaling
- Confirm a regression aligns with index removal
- Verify plan seeks date index
Explanation
Correct Answer
D:Verify plan seeks date index. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that function applied to indexed column blocks seek optimization.
Incorrect Answers
A:Verify forced plan status and performance under live skew. This concerns reusing last month's stable query plan after regression. Instead, verify plan seeks date index.
B:Compare CPU and latency before/after scaling. This concerns more resources for predictable query load. Instead, verify plan seeks date index.
C:Confirm a regression aligns with index removal. This is relevant when an API has latency spikes after an index disappears. Instead, verify plan seeks date index.
Question 568
An index on a date column is ignored when WHERE converts the column. A test instead finds: Query scans all rows despite date index. Which underlying issue best explains this finding?
- Isolation level did not protect repeatable reads
- Join omitted a key column and formed fanout
- Function applied to indexed column blocks seek optimization
- Plan changed after schema or index modification
Explanation
Correct Answer
C:Function applied to indexed column blocks seek optimization. It accounts for the specific observed failure: query scans all rows despite date index. The intended solution is make predicate sargable by comparing date ranges directly with the required setup.
Incorrect Answers
A:Isolation level did not protect repeatable reads. This concerns seat count changes between reads inside one transaction. Here, function applied to indexed column blocks seek optimization.
B:Join omitted a key column and formed fanout. This concerns customer totals are inflated by duplicated detail rows. Here, function applied to indexed column blocks seek optimization.
D:Plan changed after schema or index modification. This concerns querying time changes abruptly after deployment. Here, function applied to indexed column blocks seek optimization.
Question 569
An aggregate report over a huge fact table must improve throughput. Which database design or engineering approach most directly meets this requirement?
- Create filtered statistics/index where predicates justify it
- Evaluate read committed snapshot isolation
- Evaluate columnstore with batch-mode aggregation
- Inspect Query Store plan history and regressions
Explanation
Correct Answer
C:Evaluate columnstore with batch-mode aggregation. The implementation is create columnstore index and assess reporting tradeoffs; success can be checked by measure compression, elapsed time and batch mode. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Create filtered statistics/index where predicates justify it. This concerns an API query reads a large table with many skewed statuses. Instead, evaluate columnstore with batch-mode aggregation.
B:Evaluate read committed snapshot isolation. This concerns avoiding blocking interactive readers under concurrent writes. Instead, evaluate columnstore with batch-mode aggregation.
D:Inspect Query Store plan history and regressions. This is relevant when an API has latency spikes after an index disappears. Instead, evaluate columnstore with batch-mode aggregation.
Question 570
An aggregate report over a huge fact table must improve throughput. The team selects: Evaluate columnstore with batch-mode aggregation. Which implementation action puts this design into practice?
- Test filtered index and up-to-date subset statistics
- Create columnstore index and assess reporting tradeoffs
- Create index on leading selective filters and needed includes
- Set isolation and hold transaction around read/update
Explanation
Correct Answer
B:Create columnstore index and assess reporting tradeoffs. This is the concrete setup for evaluate columnstore with batch-mode aggregation, and measure compression, elapsed time and batch mode supplies a direct confirmation.
Incorrect Answers
A:Test filtered index and up-to-date subset statistics. This concerns an API query reads a large table with many skewed statuses. Instead, create columnstore index and assess reporting tradeoffs.
C:Create index on leading selective filters and needed includes. This concerns a query returns a small result but reads millions of rows. Instead, create columnstore index and assess reporting tradeoffs.
D:Set isolation and hold transaction around read/update. This concerns preventing non-repeatable reads during decision. Instead, create columnstore index and assess reporting tradeoffs.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests