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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More