A database administrator is troubleshooting a performance issue where a specific query intermittently runs very slowly. Upon inspection of the query execution plan, they find that sometimes the query uses an efficient index seek, but other times it defaults to a less efficient table scan. This behavior is inconsistent and seems to occur more frequently after large data loads. What is the MOST likely cause of this intermittent plan change?
- ANetwork latency fluctuations.
- BDisk fragmentation on the data files.
- CInsufficient memory available at query execution time.
- DOutdated or inaccurate database statistics.
Show answer & explanationAnswer & explanation
Correct answer: D. Outdated or inaccurate database statistics.
Database statistics provide the query optimizer with information about the data distribution in tables and indexes. After large data loads, these statistics can become outdated, causing the optimizer to make inaccurate cost estimations. This can lead to the optimizer choosing a sub-optimal execution plan (like a table scan instead of an index seek) because it incorrectly believes it's more efficient, resulting in intermittent slow performance.
Why the other options are wrong
- A. Network latency would affect all queries consistently and wouldn't typically cause a change in the execution plan from an index seek to a table scan.
- B. Disk fragmentation primarily affects the physical I/O efficiency of a chosen plan, not the optimizer's choice of *which* plan to use (index seek vs. table scan). While it can slow down an index seek, it wouldn't cause the optimizer to abandon it for a full scan unless the statistics were also misleading.
- C. While insufficient memory can cause performance issues (e.g., spills to tempdb), it usually doesn't directly cause a change in the chosen access path (seek vs. scan) unless the optimizer's memory grant estimations are severely impacted, which is often tied back to statistics accuracy.
Database Statistics (Optimizer)
Metadata maintained by the database system that describes the data distribution within columns and indexes, used by the query optimizer to estimate query costs and choose execution plans.
- Crucial for the query optimizer to select efficient execution plans.
- Become outdated after significant data modifications (inserts, updates, deletes).
- Need to be regularly updated, either manually or via auto-update mechanisms.
Memory trick: Optimizer needs fresh stats for good plans.