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.



4 thoughts on “Common SQL Server Consulting Advice: Enabling Trace Flags

  1. Howdy Erik!!
    As of today, your blog post is the only mention i see of SPL_HOBT_HASH or SPL_COMPPLAN_SKELETON on the worldwide intertubes, other than the various Microsoft pages for KB4538688. Those docs explain – as you mention in this post – that while most of trace flag 8101’s goodness was in SQL Server 2019 CU14, it was enhanced in CU16 to mitigate contention for those two spinlock resources as well.

    So… what do you think those two spinlock resources are FOR? 🙂

    1. That’s an interesting question! I suppose it depends on if we’re to interpret SPL as having some special meaning (like showplan) or just interpret it as being short for spinlock.

      When I look at a list of spinlocks on my 2019 CU16 (15.0.4223.1) instance, I don’t even see the ones mentioned in the KB article about contention listed.

      I get these back:

      SPL_AKV_LAST_FOUND_TIME
      SPL_DISPATCHER_LIST
      SPL_DISPATCHER_QUEUE
      SPL_FREEXDESLIST
      SPL_NONYIELD_ANALYSIS
      SPL_QUERY_STORE_CTX_INITIALIZED
      SPL_QUERY_STORE_EXEC_STATS_AGG
      SPL_QUERY_STORE_EXEC_STATS_READ
      SPL_QUERY_STORE_STATS_COOKIE_CACHE
      SPL_SOS_DISPATCHER
      SPL_TDS_PKT_QUEUE
      SPL_XE_BUFFER_MGR
      SPL_XE_DISPATCHER_QUEUE
      SPL_XE_NOTIFICATION_CALLBACK_LIST
      SPL_XE_SESSION_EVENT_MGR
      SPL_XE_SESSION_MGR
      SPL_XE_SESSION_TARGET_MGR

      Which seems to cover some odd categories that sort of rule out showplan as the shorthand. Both Query Store and Extended Events! And, uh, the other stuff. Shame that 99% of the spinlocks are undocumented at the dm_os_spinlock_stats page.

      Ah well. Dare to dream.

      1. more evidence for your world-renown and trusted status as a SQL Server expert 🙂 i thought about this quite a while yesterday and didn’t come up with ‘showplan’ OR ‘spinlock’ as possibilities for SPL. i actually came up completely empty-handed L0L

Comments are closed.