After a nightly bulk load tripled the size of a Sales table, several reports that used to finish in seconds now take minutes. Execution plans show estimated row counts far lower than the actual rows returned. What should the DBA do first?
Select an answer to reveal the explanation.
Short Explanation
The optimizer is like a driver using last year's traffic map: it picks the route that used to be fast. When estimated rows are way off actual rows, that's your clue the statistics are stale, so refresh them. Don't fall for clearing the cache; a fresh compile with the same old map picks the same bad route.
Full Explanation
The query optimizer chooses join methods, join order and access paths from cardinality estimates, and those estimates come from statistics: histograms and density information describing how values are distributed in a column or index. A bulk load that triples a table can leave statistics describing the old data, so the optimizer believes a predicate returns a few thousand rows when it returns millions and picks nested loops or seeks that collapse at the real volume. Updating statistics on the affected table, ideally with an adequate sample, corrects the estimates and normally lets the optimizer select an efficient plan again. Clearing the plan cache only forces recompilation; recompiling against the same stale statistics reproduces the same poor estimates. Shrinking data files changes allocated space, not optimizer knowledge, and tends to fragment indexes. Forcing a join order with hints hard-codes a plan that may suit neither the old nor the new data distribution and hides the real cause. Exam caveat: automatic statistics updates exist in many engines but may not trigger soon enough after a large load, so ETL jobs often update statistics as a final step. Operational check: compare estimated and actual row counts in the plan after the update and confirm the gap has closed.