DP-300 Practice Question: Monitor, configure, and optimize database resources

Correct answer & explanation

Stale statistics can cause the optimizer to significantly underestimate cardinality. In this scenario, the optimizer mistakenly estimated a low number of rows, leading it to choose a nested loops join (optimal for small datasets) instead of a hash join (better for larger datasets). This is a classic plan regression due to statistics staleness. Option A is incorrect: parameter sniffing typically results in a plan optimized for the initial parameter values, but it does not directly cause a cardinality underestimation that forces a switch from hash to nested loops. Option B is incorrect: dropping an index would likely cause a table scan or a different plan (e.g., clustered index scan), but the specific change from hash join to nested loops is not characteristic of a missing index; it is more indicative of a cardinality misestimate. Option C is incorrect: forced parameterization encourages plan reuse and can prevent plan changes, not cause them.