100% Latest & Updated Microsoft DP-300 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 290
Three independently confirmed SQL platform issues require separate evidence checks. Case A: A DATE column is wrapped in a conversion function and the query scans a large indexed table. Case B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Case C: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. Select exactly THREE distinct diagnostic checks, one for each case.
- Predicate expressions and actual index seek or scan operators
- Query Store runtime variation grouped by parameter-related plan behavior
- Histogram freshness and actual versus estimated rows in execution plan
- Query Store plan history and runtime statistics for the query_id
- Actual execution plan with estimated and actual row counts
- Intelligent Insights diagnostic output and time-correlated monitored metrics
Explanation
Correct Answers
A:Predicate expressions and actual index seek or scan operators directly tests the observation that a DATE column is wrapped in a conversion function and the query scans a large indexed table. It helps the team evaluate whether it should rewrite the filter into a searchable range predicate on the original indexed column. The check has a boundary: SARGability gains depend on data distribution and available indexes.
C:Histogram freshness and actual versus estimated rows in execution plan directly tests the observation that a query optimizer estimates 30 rows for a predicate that returns millions after a large data load. It helps the team evaluate whether it should update relevant statistics and review the resulting plan. The check has a boundary: Updating statistics does not guarantee an optimal plan for every parameter.
E:Actual execution plan with estimated and actual row counts directly tests the observation that a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. It helps the team evaluate whether it should capture and interpret an actual execution plan for the representative slow query. The check has a boundary: Collecting actual plans can add diagnostic overhead on sensitive workloads.
Incorrect Answers
B:Query Store runtime variation grouped by parameter-related plan behavior is appropriate for the separate condition that the same procedure is fast for one parameter value but slow for another because cardinalities vary widely. The actual 3 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load; C: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. This option is not the specific diagnostic evidence for any of those stated cases.
D:Query Store plan history and runtime statistics for the query_id is appropriate for the separate condition that a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. The actual 3 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load; C: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. This option is not the specific diagnostic evidence for any of those stated cases.
F:Intelligent Insights diagnostic output and time-correlated monitored metrics is appropriate for the separate condition that an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. The actual 3 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load; C: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. This option is not the specific diagnostic evidence for any of those stated cases.
Question 291
A database architecture review reports the following requirement: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely. Which Azure SQL service or administration concept most precisely fits this situation?
- Intelligent Insights diagnostics
- Parameter-sensitive plan behavior
- Stale statistics misestimation
- Non-SARGable filter rewrite
Explanation
Correct Answer
B:Parameter-sensitive plan behavior matches the precise requirement: the same procedure is fast for one parameter value but slow for another because cardinalities vary widely. Execution strategy can better match skewed parameter distributions. Important scope limit: One forced plan may perform badly for the other parameter group.
Incorrect Answers
A:Intelligent Insights diagnostics applies when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions, not when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely.
C:Stale statistics misestimation applies when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load, not when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely.
D:Non-SARGable filter rewrite applies when a DATE column is wrapped in a conversion function and the query scans a large indexed table, not when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely.
Question 292
A Microsoft Azure SQL solution proposal begins with this condition: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. Which Azure SQL service or administration concept most precisely fits this situation?
- Query Store plan regression
- Missing index recommendation review
- Intelligent Insights diagnostics
- Non-SARGable filter rewrite
Explanation
Correct Answer
C:Intelligent Insights diagnostics matches the precise requirement: an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. Automated signals can highlight regressions worth investigating. Important scope limit: Automated diagnosis should not be treated as proof of root cause without validation.
Incorrect Answers
A:Query Store plan regression applies when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan, not when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
B:Missing index recommendation review applies when a high-frequency filter query has repeated scans and a DMV suggests a covering index, not when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
D:Non-SARGable filter rewrite applies when a DATE column is wrapped in a conversion function and the query scans a large indexed table, not when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
Question 293
The data platform team is assessing this situation: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. Which Azure SQL service or administration concept most precisely fits this situation?
- Wait statistics investigation
- Actual execution plan lookup
- Non-SARGable filter rewrite
- Query Store plan regression
Explanation
Correct Answer
A:Wait statistics investigation matches the precise requirement: a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. Elapsed time can be attributed to wait categories rather than CPU alone. Important scope limit: Aggregated historical waits require time-window baselining and context.
Incorrect Answers
B:Actual execution plan lookup applies when a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement, not when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
C:Non-SARGable filter rewrite applies when a DATE column is wrapped in a conversion function and the query scans a large indexed table, not when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
D:Query Store plan regression applies when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan, not when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
Question 294
An operator is collecting proof for the following observation: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. Which diagnostic artifact would most directly validate the stated condition?
- Dynamic management view wait and session task details
- Histogram freshness and actual versus estimated rows in execution plan
- Query Store plan history and runtime statistics for the query_id
- Plan seek versus scan behavior and missing-index DMV recommendations
Explanation
Correct Answer
C:Query Store plan history and runtime statistics for the query_id directly tests the observation that a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. It helps the team evaluate whether it should compare Query Store plans and consider a supported plan forcing or query correction. The check has a boundary: Forcing a plan should be monitored because data distribution can change.
Incorrect Answers
A:Dynamic management view wait and session task details applies when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits, not when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan.
B:Histogram freshness and actual versus estimated rows in execution plan applies when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load, not when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan.
D:Plan seek versus scan behavior and missing-index DMV recommendations applies when a high-frequency filter query has repeated scans and a DMV suggests a covering index, not when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan.
Question 295
During SQL troubleshooting the team must test this hypothesis: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. Which diagnostic artifact would most directly validate the stated condition?
- Query Store runtime variation grouped by parameter-related plan behavior
- sys.dm_exec_requests blocking_session_id and wait information
- Actual execution plan with estimated and actual row counts
- Plan seek versus scan behavior and missing-index DMV recommendations
Explanation
Correct Answer
C:Actual execution plan with estimated and actual row counts directly tests the observation that a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. It helps the team evaluate whether it should capture and interpret an actual execution plan for the representative slow query. The check has a boundary: Collecting actual plans can add diagnostic overhead on sensitive workloads.
Incorrect Answers
A:Query Store runtime variation grouped by parameter-related plan behavior applies when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely, not when a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement.
B:Sys.dm_exec_requests blocking_session_id and wait information applies when a reporting transaction keeps row locks and multiple writers wait behind its session, not when a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement.
D:Plan seek versus scan behavior and missing-index DMV recommendations applies when a high-frequency filter query has repeated scans and a DMV suggests a covering index, not when a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement.
Question 296
A production database review needs evidence of this condition: A reporting transaction keeps row locks and multiple writers wait behind its session. Which diagnostic artifact would most directly validate the stated condition?
- Query Store runtime variation grouped by parameter-related plan behavior
- Histogram freshness and actual versus estimated rows in execution plan
- Intelligent Insights diagnostic output and time-correlated monitored metrics
- sys.dm_exec_requests blocking_session_id and wait information
Explanation
Correct Answer
D:sys.dm_exec_requests blocking_session_id and wait information directly tests the observation that a reporting transaction keeps row locks and multiple writers wait behind its session. It helps the team evaluate whether it should identify the head blocker and address transaction duration or indexing. The check has a boundary: Killing sessions indiscriminately risks rollbacks and application inconsistency.
Incorrect Answers
A:Query Store runtime variation grouped by parameter-related plan behavior applies when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely, not when a reporting transaction keeps row locks and multiple writers wait behind its session.
B:Histogram freshness and actual versus estimated rows in execution plan applies when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load, not when a reporting transaction keeps row locks and multiple writers wait behind its session.
C:Intelligent Insights diagnostic output and time-correlated monitored metrics applies when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions, not when a reporting transaction keeps row locks and multiple writers wait behind its session.
Question 297
An operator is collecting proof for the following observation: A high-frequency filter query has repeated scans and a DMV suggests a covering index. Which diagnostic artifact would most directly validate the stated condition?
- Query Store runtime variation grouped by parameter-related plan behavior
- Actual execution plan with estimated and actual row counts
- Query Store plan history and runtime statistics for the query_id
- Plan seek versus scan behavior and missing-index DMV recommendations
Explanation
Correct Answer
D:Plan seek versus scan behavior and missing-index DMV recommendations directly tests the observation that a high-frequency filter query has repeated scans and a DMV suggests a covering index. It helps the team evaluate whether it should evaluate a selective covering index against measured workload and write cost. The check has a boundary: Missing-index suggestions are hints rather than automatically safe prescriptions.
Incorrect Answers
A:Query Store runtime variation grouped by parameter-related plan behavior applies when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely, not when a high-frequency filter query has repeated scans and a DMV suggests a covering index.
B:Actual execution plan with estimated and actual row counts applies when a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement, not when a high-frequency filter query has repeated scans and a DMV suggests a covering index.
C:Query Store plan history and runtime statistics for the query_id applies when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan, not when a high-frequency filter query has repeated scans and a DMV suggests a covering index.
Question 298
During SQL troubleshooting the team must test this hypothesis: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Which diagnostic artifact would most directly validate the stated condition?
- Dynamic management view wait and session task details
- sys.dm_exec_requests blocking_session_id and wait information
- Histogram freshness and actual versus estimated rows in execution plan
- Query Store plan history and runtime statistics for the query_id
Explanation
Correct Answer
C:Histogram freshness and actual versus estimated rows in execution plan directly tests the observation that a query optimizer estimates 30 rows for a predicate that returns millions after a large data load. It helps the team evaluate whether it should update relevant statistics and review the resulting plan. The check has a boundary: Updating statistics does not guarantee an optimal plan for every parameter.
Incorrect Answers
A:Dynamic management view wait and session task details applies when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits, not when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load.
B:Sys.dm_exec_requests blocking_session_id and wait information applies when a reporting transaction keeps row locks and multiple writers wait behind its session, not when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load.
D:Query Store plan history and runtime statistics for the query_id applies when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan, not when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load.
Question 299
Two independent Azure SQL administration tickets require separate actions. Case A: A DATE column is wrapped in a conversion function and the query scans a large indexed table. Case B: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. Select exactly TWO distinct implementation steps, one for each case.
- Investigate parameter-sensitive plans and supported query or plan remedies
- Review supported Intelligent Insights findings together with measured workload
- Correlate wait statistics with I/O locking network or resource symptoms
- Rewrite the filter into a searchable range predicate on the original indexed column
- Compare Query Store plans and consider a supported plan forcing or query correction
Explanation
Correct Answers
C:Correlate wait statistics with I/O locking network or resource symptoms directly resolves the reported requirement that a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. Elapsed time can be attributed to wait categories rather than CPU alone. The implementation must also respect this constraint: Aggregated historical waits require time-window baselining and context.
D:Rewrite the filter into a searchable range predicate on the original indexed column directly resolves the reported requirement that a DATE column is wrapped in a conversion function and the query scans a large indexed table. The optimizer can consider index seeks instead of evaluating each row expression. The implementation must also respect this constraint: SARGability gains depend on data distribution and available indexes.
Incorrect Answers
A:Investigate parameter-sensitive plans and supported query or plan remedies is appropriate for the separate condition that the same procedure is fast for one parameter value but slow for another because cardinalities vary widely. The actual 2 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. This option is not the specific implementation or corrective step for any of those stated cases.
B:Review supported Intelligent Insights findings together with measured workload is appropriate for the separate condition that an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. The actual 2 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. This option is not the specific implementation or corrective step for any of those stated cases.
E:Compare Query Store plans and consider a supported plan forcing or query correction is appropriate for the separate condition that a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. The actual 2 cases are A: A DATE column is wrapped in a conversion function and the query scans a large indexed table; B: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. This option is not the specific implementation or corrective step for any of those stated cases.
Question 300
Three independently confirmed SQL platform issues require separate evidence checks. Case A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely. Case B: A high-frequency filter query has repeated scans and a DMV suggests a covering index. Case C: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. Select exactly THREE distinct diagnostic checks, one for each case.
- sys.dm_exec_requests blocking_session_id and wait information
- Plan seek versus scan behavior and missing-index DMV recommendations
- Actual execution plan with estimated and actual row counts
- Query Store runtime variation grouped by parameter-related plan behavior
- Histogram freshness and actual versus estimated rows in execution plan
- Query Store plan history and runtime statistics for the query_id
Explanation
Correct Answers
B:Plan seek versus scan behavior and missing-index DMV recommendations directly tests the observation that a high-frequency filter query has repeated scans and a DMV suggests a covering index. It helps the team evaluate whether it should evaluate a selective covering index against measured workload and write cost. The check has a boundary: Missing-index suggestions are hints rather than automatically safe prescriptions.
D:Query Store runtime variation grouped by parameter-related plan behavior directly tests the observation that the same procedure is fast for one parameter value but slow for another because cardinalities vary widely. It helps the team evaluate whether it should investigate parameter-sensitive plans and supported query or plan remedies. The check has a boundary: One forced plan may perform badly for the other parameter group.
F:Query Store plan history and runtime statistics for the query_id directly tests the observation that a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. It helps the team evaluate whether it should compare Query Store plans and consider a supported plan forcing or query correction. The check has a boundary: Forcing a plan should be monitored because data distribution can change.
Incorrect Answers
A:sys.dm_exec_requests blocking_session_id and wait information is appropriate for the separate condition that a reporting transaction keeps row locks and multiple writers wait behind its session. The actual 3 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A high-frequency filter query has repeated scans and a DMV suggests a covering index; C: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. This option is not the specific diagnostic evidence for any of those stated cases.
C:Actual execution plan with estimated and actual row counts is appropriate for the separate condition that a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. The actual 3 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A high-frequency filter query has repeated scans and a DMV suggests a covering index; C: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. This option is not the specific diagnostic evidence for any of those stated cases.
E:Histogram freshness and actual versus estimated rows in execution plan is appropriate for the separate condition that a query optimizer estimates 30 rows for a predicate that returns millions after a large data load. The actual 3 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A high-frequency filter query has repeated scans and a DMV suggests a covering index; C: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. This option is not the specific diagnostic evidence for any of those stated cases.
Administering Microsoft Azure SQL Solutions (DP-300) Practice Tests