Microsoft DP-800 Practice Test 1
Question 1
At Lakeview Services, an online-order table is mostly queried by CustomerId and recent dates. The observed constraint is: Table scans occur despite highly selective filters. What initial check best narrows the technical cause without altering business data?
- Inspect actual execution plans and filter selectivity for CustomerId and OrderDate
- Inspect whether system time and change history must be queryable
- Inspect active-row selectivity and parameterized query filters
- Inspect repeated JSON paths and execution-plan predicates
Explanation
Correct Answer
A:
Inspect the actual execution plan, predicate selectivity, and existing indexes for CustomerId and OrderDate. This will show why the optimizer scans the orders table despite narrow filters and whether an index change is warranted; do not change production schema before obtaining that evidence.
Incorrect Answers
B:
“Inspect whether system time and change history must be queryable” concerns Overwriting rows discards prior valid values; the reported problem instead is Table scans occur despite highly selective filters. Inspect the actual execution plan, predicate selectivity, and existing indexes for CustomerId and OrderDate. This will show why the optimizer scans the orders table despite narrow filters and whether an index change is warranted; do not change production schema before obtaining that evidence.
C:
“Inspect active-row selectivity and parameterized query filters” concerns A full index is large and mostly unused; the reported problem instead is Table scans occur despite highly selective filters. Inspect the actual execution plan, predicate selectivity, and existing indexes for CustomerId and OrderDate. This will show why the optimizer scans the orders table despite narrow filters and whether an index change is warranted; do not change production schema before obtaining that evidence.
D:
“Inspect repeated JSON paths and execution-plan predicates” concerns Repeated JSON_VALUE evaluation produces expensive scans; the reported problem instead is Table scans occur despite highly selective filters. Inspect the actual execution plan, predicate selectivity, and existing indexes for CustomerId and OrderDate. This will show why the optimizer scans the orders table despite narrow filters and whether an index change is warranted; do not change production schema before obtaining that evidence.
Question 2
A database engineer at Crownridge Travel is investigating this case: An online-order table is mostly queried by CustomerId and recent dates. The relevant finding is: Table scans occur despite highly selective filters. Which proposed change is the most suitable response to the observed constraint?
- Create a suitably ordered nonclustered covering index after evaluating write overhead
- Enforce a UNIQUE constraint or index on the approved business key
- Create supported computed-column or JSON index strategy for the hot path
- Create a filtered index matching the stable active-status predicate
Explanation
Correct Answer
A:
Table scans occur despite highly selective filters. Create a suitably ordered nonclustered covering index after evaluating write overhead.
Incorrect Answers
B:
“Enforce a UNIQUE constraint or index on the approved business key” concerns Application-level duplicate checks race under concurrency; the reported problem instead is Table scans occur despite highly selective filters. Create a suitably ordered nonclustered covering index after evaluating write overhead.
C:
“Create supported computed-column or JSON index strategy for the hot path” concerns Repeated JSON_VALUE evaluation produces expensive scans; the reported problem instead is Table scans occur despite highly selective filters. Create a suitably ordered nonclustered covering index after evaluating write overhead.
D:
“Create a filtered index matching the stable active-status predicate” concerns A full index is large and mostly unused; the reported problem instead is Table scans occur despite highly selective filters. Create a suitably ordered nonclustered covering index after evaluating write overhead.
Question 3
Oakridge Advisors is responsible for an Azure SQL or Microsoft SQL workload. An online-order table is mostly queried by CustomerId and recent dates. Operational evidence: Table scans occur despite highly selective filters. What isolated pilot would provide the strongest evidence before rollout?
- Import synthetic valid and invalid line items in a staging copy
- Replay a representative workload against an indexed test copy
- Populate a test table with representative short and long values
- Benchmark representative insert sequences with alternative designs
Explanation
Correct Answer
B:
Table scans occur despite highly selective filters. Replay a representative workload against an indexed test copy.
Incorrect Answers
A:
A foreign key protects parent-child validity, not the access path for a selective CustomerId and OrderDate query. Inspect the query plan and index key order to address the table scans.
C:
Changing CHAR to VARCHAR saves space for variable-length values, but it is not a remedy for a selective query that scans instead of seeking an appropriate index.
D:
Fill factor can reduce B-tree page splits in update-heavy workloads; it does not by itself create the selective key order needed for the CustomerId and date filters.
Question 4
At Horizon Communications, an online-order table is mostly queried by CustomerId and recent dates. The observed constraint is: Table scans occur despite highly selective filters. Which acceptance result proves BOTH that selective requests now obtain appropriate index seeks AND that the index does not impose unacceptable write-side latency?
- Selective reads use index seeks and write latency stays acceptable
- Analytical query scans fewer bytes within acceptable load limits
- Queries filter correctly and use the intended supported index
- New values are allocated uniquely while gaps remain an accepted property
Explanation
Correct Answer
A:
Table scans occur despite highly selective filters. Selective reads use index seeks and write latency stays acceptable.
Incorrect Answers
B:
“Analytical query scans fewer bytes within acceptable load limits” concerns Row-oriented scans consume excessive I/O for aggregation; the reported problem instead is Table scans occur despite highly selective filters. Selective reads use index seeks and write latency stays acceptable.
C:
Checking that queries return correct results through the intended index is useful but insufficient for this stated dual acceptance test: the additional covering index can slow inserts and updates. A includes measured read-plan improvement and acceptable write latency.
D:
“New values are allocated uniquely while gaps remain an accepted property” concerns IDENTITY on one table cannot coordinate numbering across tables; the reported problem instead is Table scans occur despite highly selective filters. Selective reads use index seeks and write latency stays acceptable.
Question 5
A database engineer at Brookfield Labs is investigating this case: An online-order table is mostly queried by CustomerId and recent dates. The relevant finding is: Table scans occur despite highly selective filters. What operational condition would provide the earliest useful regression warning?
- Watch memory usage and conflict/retry rates
- Track fragmentation and write amplification over time
- Track digest storage, verification failures, and feature compatibility
- Track index usage and modification overhead after rollout
Explanation
Correct Answer
D:
Table scans occur despite highly selective filters. Track index usage and modification overhead after rollout.
Incorrect Answers
A:
“Watch memory usage and conflict/retry rates” concerns A small set of hot rows is updated at high frequency; the reported problem instead is Table scans occur despite highly selective filters. Track index usage and modification overhead after rollout.
B:
Fill factor can reduce B-tree page splits in update-heavy workloads; it does not by itself create the selective key order needed for the CustomerId and date filters.
C:
“Track digest storage, verification failures, and feature compatibility” concerns Ordinary audit timestamps are not tamper-evident; the reported problem instead is Table scans occur despite highly selective filters. Track index usage and modification overhead after rollout.
Question 6
Cascade Manufacturing is responsible for an Azure SQL or Microsoft SQL workload. A warehouse aggregates billions of immutable sale lines each night. Operational evidence: Row-oriented scans consume excessive I/O for aggregation. What initial check best narrows the technical cause without altering business data?
- Compare columnstore suitability with row modification frequency and query plan
- Compare contention waits and transactional feature limitations
- Inspect active-row selectivity and parameterized query filters
- Inspect repeated JSON paths and execution-plan predicates
Explanation
Correct Answer
A:
Row-oriented scans consume excessive I/O for aggregation. Compare columnstore suitability with row modification frequency and query plan.
Incorrect Answers
B:
Memory-optimized OLTP tables target high-concurrency transactional updates. This warehouse primarily scans huge immutable fact sets for aggregates; columnstore compression and column elimination are the pertinent design.
C:
A filtered rowstore index benefits a selective subset of rows, but cannot replace column-oriented scanning for broad aggregation across billions of sale lines.
D:
Indexing a computed JSON path helps predicates that repeatedly extract properties; it does not reduce the I/O of wide row-oriented scans for aggregate reporting.
Question 7
At Clearwater Utilities, a warehouse aggregates billions of immutable sale lines each night. The observed constraint is: Row-oriented scans consume excessive I/O for aggregation. Which proposed change is the most suitable response to the observed constraint?
- Partition the fact table by event date with aligned supporting indexes
- Choose a clustered columnstore design for the large analytical fact table
- Evaluate an append-only or updatable ledger table with digest verification
- Configure system-versioned temporal tables with a history table
Explanation
Correct Answer
B:
Row-oriented scans consume excessive I/O for aggregation. Choose a clustered columnstore design for the large analytical fact table.
Incorrect Answers
A:
“Partition the fact table by event date with aligned supporting indexes” concerns Large deletes cause long log usage and blocking; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Choose a clustered columnstore design for the large analytical fact table.
C:
“Evaluate an append-only or updatable ledger table with digest verification” concerns Ordinary audit timestamps are not tamper-evident; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Choose a clustered columnstore design for the large analytical fact table.
D:
“Configure system-versioned temporal tables with a history table” concerns Overwriting rows discards prior valid values; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Choose a clustered columnstore design for the large analytical fact table.
Question 8
A database engineer at Cedarbridge Health is investigating this case: A warehouse aggregates billions of immutable sale lines each night. The relevant finding is: Row-oriented scans consume excessive I/O for aggregation. What isolated pilot would provide the strongest evidence before rollout?
- Benchmark active-customer queries and archival writes
- Benchmark grouped sums on rowstore and columnstore test tables
- Allocate IDs in separate test tables including a rolled-back transaction
- Benchmark representative insert sequences with alternative designs
Explanation
Correct Answer
B:
Row-oriented scans consume excessive I/O for aggregation. Benchmark grouped sums on rowstore and columnstore test tables.
Incorrect Answers
A:
A filtered rowstore index benefits a selective subset of rows, but cannot replace column-oriented scanning for broad aggregation across billions of sale lines.
C:
“Allocate IDs in separate test tables including a rolled-back transaction” concerns IDENTITY on one table cannot coordinate numbering across tables; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Benchmark grouped sums on rowstore and columnstore test tables.
D:
“Benchmark representative insert sequences with alternative designs” concerns Page split events spike on a busy B-tree; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Benchmark grouped sums on rowstore and columnstore test tables.
Question 9
Ashwood Solutions is responsible for an Azure SQL or Microsoft SQL workload. A warehouse aggregates billions of immutable sale lines each night. Operational evidence: Row-oriented scans consume excessive I/O for aggregation. Which acceptance result most directly demonstrates the requirement has been satisfied?
- Page-split rate falls without excessive storage growth
- Analytical query scans fewer bytes within acceptable load limits
- Selective reads use index seeks and write latency stays acceptable
- Queries filter correctly and use the intended supported index
Explanation
Correct Answer
B:
Row-oriented scans consume excessive I/O for aggregation. Analytical query scans fewer bytes within acceptable load limits.
Incorrect Answers
A:
“Page-split rate falls without excessive storage growth” concerns Page split events spike on a busy B-tree; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Analytical query scans fewer bytes within acceptable load limits.
C:
“Selective reads use index seeks and write latency stays acceptable” concerns Table scans occur despite highly selective filters; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Analytical query scans fewer bytes within acceptable load limits.
D:
Indexing a computed JSON path helps predicates that repeatedly extract properties; it does not reduce the I/O of wide row-oriented scans for aggregate reporting.
Question 10
At Seabrook University, a warehouse aggregates billions of immutable sale lines each night. The observed constraint is: Row-oriented scans consume excessive I/O for aggregation. What operational condition would provide the earliest useful regression warning?
- Watch constraint violations in ingestion logs
- Watch rowgroup health and segment elimination
- Watch partition boundary maintenance and skew
- Watch conversion and arithmetic overflow errors
Explanation
Correct Answer
B:
Row-oriented scans consume excessive I/O for aggregation. Watch rowgroup health and segment elimination.
Incorrect Answers
A:
“Watch constraint violations in ingestion logs” concerns Orphan records appear after bulk import; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Watch rowgroup health and segment elimination.
C:
“Watch partition boundary maintenance and skew” concerns Large deletes cause long log usage and blocking; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Watch rowgroup health and segment elimination.
D:
“Watch conversion and arithmetic overflow errors” concerns FLOAT introduces approximate representations in money calculations; the reported problem instead is Row-oriented scans consume excessive I/O for aggregation. Watch rowgroup health and segment elimination.
