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.