Exam Bundle

The actual plan demonstrates corrected cardinality and reduced work for the representative parameter set. That result is evidence for another corrective change: Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. The alternative problem is: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The current Lakeside Research issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

The same query has historical plan and runtime records that identify the regressed plan and its performance difference. That result is evidence for another corrective change: Enable and configure Query Store with appropriate capture and retention policies before diagnosing plan performance regressions. The alternative problem is: A frequently executed Azure SQL query regresses after an application update but Query Store is disabled, leaving no historical plan comparison. The current Lakeside Research issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

The targeted query uses an effective seek or narrower scan with fewer logical reads and acceptable write overhead. That result is evidence for another corrective change: Evaluate an appropriate nonclustered index using predicates, selectivity and maintenance costs, then test the actual execution plan. The alternative problem is: A reporting query scans a very large transaction table for a selective customer ID because the table lacks a useful access path. The current Lakeside Research issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. That relates to a separate SQL administration problem: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The Westhaven Retail requirement is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. That approach does not directly establish the required fix; the requested acceptance evidence is: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

Rewrite the date condition into a search-argument-friendly range predicate and confirm its semantic correctness. That relates to a separate SQL administration problem: A query performs a string function on an indexed date column in the WHERE predicate and loses partition or index seek efficiency. The Westhaven Retail requirement is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. That approach does not directly establish the required fix; the requested acceptance evidence is: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

Inspect actual execution plan memory grants, spill warnings and relevant DMVs before selecting an indexing or query change. That relates to a separate SQL administration problem: A SQL optimizer chooses an expensive sort and spill during a high-volume query but support only monitors aggregate CPU. The Westhaven Retail requirement is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. That approach does not directly establish the required fix; the requested acceptance evidence is: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

The tuned query completes with fewer spill events and lower tempdb I/O for representative datasets. That result is evidence for another corrective change: Inspect actual execution plan memory grants, spill warnings and relevant DMVs before selecting an indexing or query change. The alternative problem is: A SQL optimizer chooses an expensive sort and spill during a high-volume query but support only monitors aggregate CPU. The current Westhaven Retail issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

The affected query returns to the approved runtime profile and a forced-plan health check detects no unsupported plan behavior. That result is evidence for another corrective change: Use Query Store performance evidence and a supported plan forcing or regression correction after validating applicability. The alternative problem is: A monitoring team assumes all slow requests come from missing indexes but Query Store identifies a sudden plan regression without a schema change. The current Westhaven Retail issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

The same query has historical plan and runtime records that identify the regressed plan and its performance difference. That result is evidence for another corrective change: Enable and configure Query Store with appropriate capture and retention policies before diagnosing plan performance regressions. The alternative problem is: A frequently executed Azure SQL query regresses after an application update but Query Store is disabled, leaving no historical plan comparison. The current Westhaven Retail issue is instead: A stored procedure occasionally chooses an inefficient execution plan after parameter changes and the team blindly rebuilds every table index. Its decisive acceptance evidence would be: The stabilized query uses an appropriate plan for representative parameter sets and duration variance falls without global index rebuilds.

Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. That relates to a separate SQL administration problem: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The Westhaven Retail requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Correlate wait types with execution plans, physical reads and storage latency to locate the actual I/O bottleneck. That relates to a separate SQL administration problem: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. The Westhaven Retail requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Review the reported root-cause context and correlate it with Query Store and platform telemetry before implementing a targeted fix. That relates to a separate SQL administration problem: Azure SQL Intelligent Insights reports degraded database performance but administrators ignore its diagnostic context and scale every database equally. The Westhaven Retail requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

The actual plan demonstrates corrected cardinality and reduced work for the representative parameter set. That result is evidence for another corrective change: Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. The alternative problem is: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The current Westhaven Retail issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

The same query has historical plan and runtime records that identify the regressed plan and its performance difference. That result is evidence for another corrective change: Enable and configure Query Store with appropriate capture and retention policies before diagnosing plan performance regressions. The alternative problem is: A frequently executed Azure SQL query regresses after an application update but Query Store is disabled, leaving no historical plan comparison. The current Westhaven Retail issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

The rewritten query returns identical rows while the execution plan reads the targeted date range efficiently. That result is evidence for another corrective change: Rewrite the date condition into a search-argument-friendly range predicate and confirm its semantic correctness. The alternative problem is: A query performs a string function on an indexed date column in the WHERE predicate and loses partition or index seek efficiency. The current Westhaven Retail issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Correlate wait types with execution plans, physical reads and storage latency to locate the actual I/O bottleneck. That relates to a separate SQL administration problem: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. The Juniper Media requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Evaluate an appropriate nonclustered index using predicates, selectivity and maintenance costs, then test the actual execution plan. That relates to a separate SQL administration problem: A reporting query scans a very large transaction table for a selective customer ID because the table lacks a useful access path. The Juniper Media requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Shorten the transaction and move the external call outside the critical lock-holding region where consistency rules permit. That relates to a separate SQL administration problem: A transaction holds locks while calling an external service and unrelated queries queue behind its long-lived lock scope. The Juniper Media requirement is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. That approach does not directly establish the required fix; the requested acceptance evidence is: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Under concurrent load the lead transaction holds locks for less time and dependent requests no longer show sustained blocking. That result is evidence for another corrective change: Shorten the transaction and move the external call outside the critical lock-holding region where consistency rules permit. The alternative problem is: A transaction holds locks while calling an external service and unrelated queries queue behind its long-lived lock scope. The current Juniper Media issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

The actual plan demonstrates corrected cardinality and reduced work for the representative parameter set. That result is evidence for another corrective change: Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. The alternative problem is: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The current Juniper Media issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

The same query has historical plan and runtime records that identify the regressed plan and its performance difference. That result is evidence for another corrective change: Enable and configure Query Store with appropriate capture and retention policies before diagnosing plan performance regressions. The alternative problem is: A frequently executed Azure SQL query regresses after an application update but Query Store is disabled, leaving no historical plan comparison. The current Juniper Media issue is instead: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. Its decisive acceptance evidence would be: The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running.

Inspect active requests, session wait information and blocking chains using supported DMVs or Extended Events. That relates to a separate SQL administration problem: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. The Juniper Media requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

Evaluate an appropriate nonclustered index using predicates, selectivity and maintenance costs, then test the actual execution plan. That relates to a separate SQL administration problem: A reporting query scans a very large transaction table for a selective customer ID because the table lacks a useful access path. The Juniper Media requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

Collect SQL deadlock reports through supported monitoring and identify competing resource acquisition orders before fixing transaction logic. That relates to a separate SQL administration problem: A high-concurrency app hits deadlocks but operations treats them as ordinary slow queries and fails to capture the deadlock graph. The Juniper Media requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The diagnosed metric and affected query recover in a follow-up observation while unrelated databases retain their original sizing. That result is evidence for another corrective change: Review the reported root-cause context and correlate it with Query Store and platform telemetry before implementing a targeted fix. The alternative problem is: Azure SQL Intelligent Insights reports degraded database performance but administrators ignore its diagnostic context and scale every database equally. The current Juniper Media issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The optimized import returns the required dataset with reduced logical reads and a measured improvement under controlled load. That result is evidence for another corrective change: Evaluate row estimates, join order, predicate placement and suitable indexes using an actual execution plan. The alternative problem is: A data import starts running slowly as an application joins multiple large tables without selective predicates and the team only adds hardware. The current Juniper Media issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running. That result is evidence for another corrective change: Inspect active requests, session wait information and blocking chains using supported DMVs or Extended Events. The alternative problem is: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. The current Juniper Media issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

Evaluate row estimates, join order, predicate placement and suitable indexes using an actual execution plan. That relates to a separate SQL administration problem: A data import starts running slowly as an application joins multiple large tables without selective predicates and the team only adds hardware. The Northgate Telecom requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

Inspect active requests, session wait information and blocking chains using supported DMVs or Extended Events. That relates to a separate SQL administration problem: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. The Northgate Telecom requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

Compare estimated and actual plans with row counts and resource metrics to identify estimation or join-selection problems. That relates to a separate SQL administration problem: A SQL DBA sees an estimated execution plan only and concludes a query cannot have cardinality estimation errors at runtime. The Northgate Telecom requirement is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. That approach does not directly establish the required fix; the requested acceptance evidence is: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The tuned query completes with fewer spill events and lower tempdb I/O for representative datasets. That result is evidence for another corrective change: Inspect actual execution plan memory grants, spill warnings and relevant DMVs before selecting an indexing or query change. The alternative problem is: A SQL optimizer chooses an expensive sort and spill during a high-volume query but support only monitors aggregate CPU. The current Northgate Telecom issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The updated report returns the same required values while transferring fewer bytes and lowering measured memory demand. That result is evidence for another corrective change: Rewrite the query to project only required columns and review supporting indexes for the access pattern. The alternative problem is: A SQL report selects all columns from a wide transactional table even though it needs only three, causing high memory and network usage. The current Northgate Telecom issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.

The diagnosed metric and affected query recover in a follow-up observation while unrelated databases retain their original sizing. That result is evidence for another corrective change: Review the reported root-cause context and correlate it with Query Store and platform telemetry before implementing a targeted fix. The alternative problem is: Azure SQL Intelligent Insights reports degraded database performance but administrators ignore its diagnostic context and scale every database equally. The current Northgate Telecom issue is instead: A query waits on PAGEIOLATCH-related events but the DBA assumes it is always a CPU bottleneck and increases cores. Its decisive acceptance evidence would be: After the targeted I/O or query change physical reads and latency improve while CPU alone is not used as proof.