I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query.
That seems to be patently untrue since every 'trivial' query I run seems to get cached. So I'm wondering what the point of 2861 is, unless I'm misunderstanding what a trivial plan actually is. When I query the cached plan and it says it's ad hoc and trivial then I have no reason to doubt it.
Hoping someone can enlighten me.
Best Answer
Cost is an overloaded term, as is trivial.
When talking about execution plans, the estimated cost is computed by the query optimizer, as a way to choose one plan, or one small part of a plan, over another. The final plan has an associated cost computed by summing all the subtree estimated operator costs. A trivial plan is one obtained without going through cost-based optimization.
When talking about the plan cache, a separate cache cost is computed that is an input into how long the plan will survive in cache (normally as a response to memory pressure). The details of this computation, and what is considered a "zero cache cost" (or "trivial") plan has changed many times over the years. There are many exceptions, caveats, and restrictions, which again have changed quite often.
I have no idea if trace flag 2861 is still effective in modern builds.
You can find some of the details (that were correct at the time they were written, to the best of my knowledge) in resources like: