Avoid Using Trace Flag 2861 to Cache Zero-Cost Query Plans

Trace flag 2861 causes SQL Server to cache query plans for trivial queries that have a cost of zero or close to zero. 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. Typically this design makes sense and efficiently manages compilation. However, some people like to use the fn_get_sql() function to see what queries are running on a server, but fn_get_sql() can't see queries associated with the zero-cost plans unless you enable trace flag 2861.

Enabling trace flag 2861 seems reasonable if you need to see all queries that are running, but doing so on a busy server can create severe performance problems. Thus, you shouldn't enable trace flag 2861 except under controlled circumstances and on a server that doesn't handle a significant workload. Enabling trace flag 2861 can cause your syscacheobjects table to grow substantially, which has a ripple effect that causes query compilations to take much longer than they should.

Several months ago, I saw an extreme case on a broken server. Queries that should have taken 10 to 50 milliseconds to execute took 5 seconds or more because of high compile times. We wasted a lot of time diagnosing the problem before we figured out that the tool we were using had caused the degraded performance.