patternsqlModerate
Trace flag 2861 and what a 'zero-cost' plan actually means
Viewed 0 times
whatflag2861meansplanactuallyandcostzerotrace
Problem
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.
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.
Solution
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.
Trace flag 2861 was only effective for a very short time. It does not exist in modern builds. Sean Gallardy of Microsoft says in a blog post:
Here’s the low down, trace flag 2861 did exist for the briefest of moments in history. It was added in SQL Server 2000 SP3 (I can’t find the exact build number at this time) and subsequently removed before SQL Server 2005 shipped. That’s right, it existed for roughly two service packs of SQL Server 2000 before being removed, for good.
If you’re still using this trace flag today, it literally does nothing.
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:
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.
Trace flag 2861 was only effective for a very short time. It does not exist in modern builds. Sean Gallardy of Microsoft says in a blog post:
Here’s the low down, trace flag 2861 did exist for the briefest of moments in history. It was added in SQL Server 2000 SP3 (I can’t find the exact build number at this time) and subsequently removed before SQL Server 2005 shipped. That’s right, it existed for roughly two service packs of SQL Server 2000 before being removed, for good.
If you’re still using this trace flag today, it literally does nothing.
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:
- 3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 by the SQL Programmability & API Development Team
- Plan Caching and Recompilation in SQL Server 2012 by Greg Low
Context
StackExchange Database Administrators Q#139659, answer score: 12
Revisions (0)
No revisions yet.