Trace Flag 8048 May Still Be Necessary After SQL Server 2016

Big Box

Way back when, SQL Servers with lots of cores could hit some weird contention on CMEMTHREAD. To fix that and a lot of other issues associated with running on a large server, people would resort to all sorts of trace flags.

My dear friend L_____ (b|t) has a list. Maybe not the most up to date list, but there are a lot of good references in the post.

Something we’ve both observed working with large servers is that Trace Flag 8048 might still be necessary under some circumstances.

Two Reasons

Starting with SQL Server 2016, it partitions memory dynamically. That’s cool.

Beats what it used to do by a country mile. Maybe even a continent mile.

But there are two problems you can run into:

  • You don’t meet the dynamic threshold, but still generate enough activity to run into contention
  • You run into issues faster than the dynamic threshold gets hit and starts taking effect

Many tools in the First Responder Kit will warn you about high CMEMTHREAD waits. You may even see them from heavy Query Store use.

If you’re running a large server, this trace flag may still be of value even after SQL Server 2016.

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.