Common SQL Server Consulting Advice: Enabling Trace Flags

Lessen Earned

---

This is advice that I have to give less frequently these days, but is absolutely critical when I do.

With SQL Server 2016, suggestions that I’d normally make to folks became the default behavior

• 1117 and 1118 for tempdb performance and contention
• 2371 for stats update threshold improvements on large tables

By far the most common trace flag that I still have to get turned on is 8048. Please read the post at the link before telling me that it’s not necessary.

Most trace flags work best as a startup option, because then you don’t have to remember to turn them on again.

There are a lot of trace flags that I usually have people turn off, too. Most common at the 12XX trace flags that stick deadlock information in the error log.

There are far better ways to get at that information these days, like using the system health extended event session.

Lessen Earned

---

There are query-level trace flags that make sense sometimes, too as part of query tuning and experimentation.

Some of these have been replaced by written hints, too:

• 8649 can be replaced by OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
• 8690 can be replaced by OPTION(NO_PERFORMANCE_SPOOL);

Others are quite interesting to get more details about the optimization process. They almost all require 3604 to be used as well, to output messages the to console.

• 2315: Memory allocations taken during compilation
• 2363: (2014+) Statistics Info
• 2372: Shows memory utilization during the different optimization stages
• 2373: Shows memory utilization while applying optimization rules and deriving properties
• 7352: Show final query tree (post-optimization rewrites)
• 8605: Initial query tree
• 8606: Additional LogOp trees
• 8607: Optimizer output tree
• 8608: Input tree copied into memo
• 8609: Operation counts
• 8612: Extra LogOp info
• 8615: Final memo
• 8619: Applied transformation rules
• 8620: Add memo arguments to trace flag 8619
• 8621: Rule with resulting tree
• 8670: Disables Search2 phase of optimization
• 8671: Disables logic that prunes memo and prevents optimizer from stopping due to “Good Enough Plan found”
• 8675: Optimization phases and timing
• 8757: Disable trivial plan generation
• 9204: Interesting statistics loaded (< 2014)
• 9292: Interesting statistics (< 2014)

If this all seems daunting, it’s because it is. And in most cases, it should be. But like… Why not make trace flags safeguards?

Diagnostic

---

Microsoft creates trace flags to change default product behavior, often to solve a problem.

If you read through a cumulative update patch notes, you might find some documentation (no seriously, stop laughing) that says you need to apply the CU and enable a trace flag to see a problem get resolved.

It would be nice if SQL Server were a bit more proactive and capable of self-healing. If the issue at hand is detected, why not enable the trace flag automatically? There’s no need for it to act like a sinking ship.

I get that it’s not feasible all the time, and that some of them truly are only effective at startup (but that seems like something that could be done, too).

Let’s look at semi-recent Trace Flag 8101 as an example!

When you run many online transactions on a database in Microsoft SQL Server 2019, you notice severe spinlock contention. The severe contention is generally observed on new generation and high-end systems. The following conditions apply to severe spinlock contention:

• Requires modern hardware, such as Intel Skylake processors
• Requires a server that has many CPUs
• Requires a high number of concurrent users
• Symptoms may include unexpected high CPU usage

Okay, some of this stuff can be (or is interrogated at startup as part of Hekaton checks). Maybe some is subjective, like what constitutes a high number of concurrent users, or CPU.

But there’s more!

Note In SQL Server 2019 Cumulative Update 16, we fixed spinlock contention on SPL_HOBT_HASH and SPL_COMPPLAN_SKELETON.

Okay, and…

Note Trace flag 8101 has to be turned on to enable the fix.

That seems far less subjective, and a good opportunity to self-heal a little bit. Flip the switch, SQL Server.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.