Press "Enter" to skip to content

Trivial Plans and Stats Updates

Lonny Niederstadt lays out the harshness of reality:

OK.  SQL Server trivial plans for rowstore table INSERT. And related optimizer stats interaction.
TL;DR cached trivial plans for INSERT can be surprisingly stubborn. If a query matches to one, it won’t perform or queue a stats update even if the stats are stale.  If the stats have been updated and would otherwise warrant a per-index plan – but there is a matching cached trivial plan for a per-row plan… outta luck. Might hafta DBCC FREEPROCCACHE or add OPTION(RECOMPILE) hint to make sure a cached trivial plan doesn’t prevent a per-index update for an INSERT when you really want one.

Read on for a dive into the topic.