After a storm surge, emergency-management queries show large gaps between estimated and actual rows in execution plans on skewed incident tables. What problem should the team investigate?
Select an answer to reveal the explanation.
Short Explanation
When the plan thinks a neighborhood has a handful of incidents but the storm dumped thousands, that’s cardinality skew—the optimizer packed for a picnic and got a hurricane.
Full Explanation
Large gaps between estimated and actual rows in execution plans on skewed storm-surge incident tables point to cardinality estimation problems: the optimizer’s model (statistics, density, histograms, or assumption of uniformity) under- or over-predicts rows for highly skewed municipal distributions after the surge inserts. That mismatch drives poor join order, memory grants, and scan versus seek choices. The team should investigate stats freshness, histogram steps for hotspot neighborhoods, parameter sensitivity, and whether filtered stats or plan guides are needed for emergency-management queries. Always Encrypted ciphertext equality limitations affect how encrypted predicates can be compared; they do not produce estimated-versus-actual gaps on cleartext incident distributions. Missing GraphQL pagination defaults on a public shelter API is an API design issue, not a SQL cardinality symptom inside execution plans. Passwordless Entra login failures at an intake kiosk are authentication problems and will not appear as plan estimate skew on incident tables. Exam caveat: after bulk skew events, updating statistics (and sometimes using FULLSCAN or more steps) can help, but a single UPDATE STATISTICS is not always enough for extreme municipal skew or ascending keys; CE version also depends on compatibility level. Distinguish cardinality skew from parameter sniffing: both can show estimate gaps, but sniffing is plan reuse with atypical parameters, while skew is distribution shape. Operational check: pull the slow emergency query’s actual plan; compare estimated vs actual at the skewed operators; check stats last-updated time and histogram for the incident attribute (for example neighborhood or incident type); refresh stats or add filtered statistics for surge-heavy slices; re-run with actual plans and confirm estimates tighten and memory grants stabilize.