100% Latest & Updated Microsoft DP-300 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
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.
Question 301
During SQL troubleshooting the team must test this hypothesis: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. Which diagnostic artifact would most directly validate the stated condition?
- Dynamic management view wait and session task details
- Intelligent Insights diagnostic output and time-correlated monitored metrics
- sys.dm_exec_requests blocking_session_id and wait information
- Histogram freshness and actual versus estimated rows in execution plan
Explanation
Correct Answer
B:Intelligent Insights diagnostic output and time-correlated monitored metrics directly tests the observation that an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. It helps the team evaluate whether it should review supported Intelligent Insights findings together with measured workload. The check has a boundary: Automated diagnosis should not be treated as proof of root cause without validation.
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 an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
C: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 an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
D: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 an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions.
Question 302
A production database review needs evidence of this condition: A query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. Which diagnostic artifact would most directly validate the stated condition?
- sys.dm_exec_requests blocking_session_id and wait information
- Histogram freshness and actual versus estimated rows in execution plan
- Query Store runtime variation grouped by parameter-related plan behavior
- Dynamic management view wait and session task details
Explanation
Correct Answer
D:Dynamic management view wait and session task details directly tests the observation that a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. It helps the team evaluate whether it should correlate wait statistics with I/O locking network or resource symptoms. The check has a boundary: Aggregated historical waits require time-window baselining and context.
Incorrect Answers
A: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 has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
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 query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
C: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 query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits.
Question 303
A migration or configuration change request describes the following problem: A stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. Which technical or administrative action most directly addresses this requirement?
- Compare Query Store plans and consider a supported plan forcing or query correction
- Review supported Intelligent Insights findings together with measured workload
- Update relevant statistics and review the resulting plan
- Correlate wait statistics with I/O locking network or resource symptoms
Explanation
Correct Answer
A:Compare Query Store plans and consider a supported plan forcing or query correction directly resolves the reported requirement that a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan. Historical plan evidence helps identify and mitigate regressions. The implementation must also respect this constraint: Forcing a plan should be monitored because data distribution can change.
Incorrect Answers
B:Review supported Intelligent Insights findings together with measured workload applies when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions, not when a stored query becomes slower after a deployment and Query Store reveals the same query has switched to a costly plan.
C:Update relevant statistics and review the resulting 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:Correlate wait statistics with I/O locking network or resource symptoms 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.
Question 304
Before implementation the team records this technical condition: A SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. Which technical or administrative action most directly addresses this requirement?
- Evaluate a selective covering index against measured workload and write cost
- Identify the head blocker and address transaction duration or indexing
- Review supported Intelligent Insights findings together with measured workload
- Capture and interpret an actual execution plan for the representative slow query
Explanation
Correct Answer
D:Capture and interpret an actual execution plan for the representative slow query directly resolves the reported requirement that a SELECT takes too long and the DBA needs to see executed operator row counts rather than only a written SQL statement. Join choices scans and cardinality errors become visible. The implementation must also respect this constraint: Collecting actual plans can add diagnostic overhead on sensitive workloads.
Incorrect Answers
A:Evaluate a selective covering index against measured workload and write cost 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.
B:Identify the head blocker and address transaction duration or indexing 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.
C:Review supported Intelligent Insights findings together with measured workload applies when an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions, 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 305
The DBA must resolve this concrete requirement: A reporting transaction keeps row locks and multiple writers wait behind its session. Which technical or administrative action most directly addresses this requirement?
- Correlate wait statistics with I/O locking network or resource symptoms
- Capture and interpret an actual execution plan for the representative slow query
- Identify the head blocker and address transaction duration or indexing
- Review supported Intelligent Insights findings together with measured workload
Explanation
Correct Answer
C:Identify the head blocker and address transaction duration or indexing directly resolves the reported requirement that a reporting transaction keeps row locks and multiple writers wait behind its session. Blocked sessions can resume after the offending lock conflict is removed. The implementation must also respect this constraint: Killing sessions indiscriminately risks rollbacks and application inconsistency.
Incorrect Answers
A:Correlate wait statistics with I/O locking network or resource symptoms applies when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits, not when a reporting transaction keeps row locks and multiple writers wait behind its session.
B:Capture and interpret an actual execution plan for the representative slow query 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 reporting transaction keeps row locks and multiple writers wait behind its session.
D:Review supported Intelligent Insights findings together with measured workload 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 306
A migration or configuration change request describes the following problem: A high-frequency filter query has repeated scans and a DMV suggests a covering index. Which technical or administrative action most directly addresses this requirement?
- Capture and interpret an actual execution plan for the representative slow query
- Rewrite the filter into a searchable range predicate on the original indexed column
- Evaluate a selective covering index against measured workload and write cost
- Identify the head blocker and address transaction duration or indexing
Explanation
Correct Answer
C:Evaluate a selective covering index against measured workload and write cost directly resolves the reported requirement that a high-frequency filter query has repeated scans and a DMV suggests a covering index. Suitable indexing can lower logical reads for targeted queries. The implementation must also respect this constraint: Missing-index suggestions are hints rather than automatically safe prescriptions.
Incorrect Answers
A:Capture and interpret an actual execution plan for the representative slow query 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.
B:Rewrite the filter into a searchable range predicate on the original indexed column applies when a DATE column is wrapped in a conversion function and the query scans a large indexed table, not when a high-frequency filter query has repeated scans and a DMV suggests a covering index.
D:Identify the head blocker and address transaction duration or indexing applies when a reporting transaction keeps row locks and multiple writers wait behind its session, not when a high-frequency filter query has repeated scans and a DMV suggests a covering index.
Question 307
Before implementation the team records this technical condition: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Which technical or administrative action most directly addresses this requirement?
- Update relevant statistics and review the resulting plan
- Identify the head blocker and address transaction duration or indexing
- Evaluate a selective covering index against measured workload and write cost
- Compare Query Store plans and consider a supported plan forcing or query correction
Explanation
Correct Answer
A:Update relevant statistics and review the resulting plan directly resolves the reported requirement that a query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Accurate cardinality estimates may improve join strategy and memory grant. The implementation must also respect this constraint: Updating statistics does not guarantee an optimal plan for every parameter.
Incorrect Answers
B:Identify the head blocker and address transaction duration or indexing 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.
C:Evaluate a selective covering index against measured workload and write cost applies when a high-frequency filter query has repeated scans and a DMV suggests a covering index, not when a query optimizer estimates 30 rows for a predicate that returns millions after a large data load.
D:Compare Query Store plans and consider a supported plan forcing or query correction 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 308
The DBA must resolve this concrete requirement: A DATE column is wrapped in a conversion function and the query scans a large indexed table. Which technical or administrative action most directly addresses this requirement?
- Correlate wait statistics with I/O locking network or resource symptoms
- Rewrite the filter into a searchable range predicate on the original indexed column
- Evaluate a selective covering index against measured workload and write cost
- Investigate parameter-sensitive plans and supported query or plan remedies
Explanation
Correct Answer
B: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:Correlate wait statistics with I/O locking network or resource symptoms applies when a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits, not when a DATE column is wrapped in a conversion function and the query scans a large indexed table.
C:Evaluate a selective covering index against measured workload and write cost applies when a high-frequency filter query has repeated scans and a DMV suggests a covering index, not when a DATE column is wrapped in a conversion function and the query scans a large indexed table.
D:Investigate parameter-sensitive plans and supported query or plan remedies applies when the same procedure is fast for one parameter value but slow for another because cardinalities vary widely, not when a DATE column is wrapped in a conversion function and the query scans a large indexed table.
Question 309
Two independent Azure SQL administration tickets require separate actions. Case A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely. Case B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Select exactly TWO distinct implementation steps, one for each case.
- Update relevant statistics and review the resulting plan
- Identify the head blocker and address transaction duration or indexing
- Capture and interpret an actual execution plan for the representative slow query
- Investigate parameter-sensitive plans and supported query or plan remedies
- Rewrite the filter into a searchable range predicate on the original indexed column
Explanation
Correct Answers
A:Update relevant statistics and review the resulting plan directly resolves the reported requirement that a query optimizer estimates 30 rows for a predicate that returns millions after a large data load. Accurate cardinality estimates may improve join strategy and memory grant. The implementation must also respect this constraint: Updating statistics does not guarantee an optimal plan for every parameter.
D:Investigate parameter-sensitive plans and supported query or plan remedies directly resolves the reported requirement that 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. The implementation must also respect this constraint: One forced plan may perform badly for the other parameter group.
Incorrect Answers
B:Identify the head blocker and address transaction duration or indexing is appropriate for the separate condition that a reporting transaction keeps row locks and multiple writers wait behind its session. The actual 2 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. This option is not the specific implementation or corrective step for any of those stated cases.
C:Capture and interpret an actual execution plan for the representative slow query 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 2 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. This option is not the specific implementation or corrective step for any of those stated cases.
E:Rewrite the filter into a searchable range predicate on the original indexed column is appropriate for the separate condition that a DATE column is wrapped in a conversion function and the query scans a large indexed table. The actual 2 cases are A: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; B: A query optimizer estimates 30 rows for a predicate that returns millions after a large data load. This option is not the specific implementation or corrective step for any of those stated cases.
Question 310
Three independently confirmed SQL platform issues require separate evidence checks. Case A: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. Case B: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely. Case C: A DATE column is wrapped in a conversion function and the query scans a large indexed table. Select exactly THREE distinct diagnostic checks, one for each case.
- Actual execution plan with estimated and actual row counts
- Query Store runtime variation grouped by parameter-related plan behavior
- Dynamic management view wait and session task details
- Intelligent Insights diagnostic output and time-correlated monitored metrics
- Predicate expressions and actual index seek or scan operators
- sys.dm_exec_requests blocking_session_id and wait information
Explanation
Correct Answers
B: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.
D:Intelligent Insights diagnostic output and time-correlated monitored metrics directly tests the observation that an Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions. It helps the team evaluate whether it should review supported Intelligent Insights findings together with measured workload. The check has a boundary: Automated diagnosis should not be treated as proof of root cause without validation.
E: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.
Incorrect Answers
A: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: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions; B: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; C: A DATE column is wrapped in a conversion function and the query scans a large indexed table. This option is not the specific diagnostic evidence for any of those stated cases.
C:Dynamic management view wait and session task details is appropriate for the separate condition that a query has acceptable CPU but poor elapsed time and the team needs to identify the dominant waits. The actual 3 cases are A: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions; B: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; C: A DATE column is wrapped in a conversion function and the query scans a large indexed table. This option is not the specific diagnostic evidence for any of those stated cases.
F: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: An Azure SQL Database slows suddenly and administrators want automated diagnostic signals about notable performance regressions; B: The same procedure is fast for one parameter value but slow for another because cardinalities vary widely; C: A DATE column is wrapped in a conversion function and the query scans a large indexed table. This option is not the specific diagnostic evidence for any of those stated cases.
Administering Microsoft Azure SQL Solutions (DP-300) Practice Tests