Trace flag 2861 and what a 'zero-cost' plan actually means
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:


3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 by the SQL Programmability &amp; API Development Team
Plan Caching and Recompilation in SQL Server 2012 by Greg Low