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.
Something we’ve both observed working with large servers is that Trace Flag 8048 might still be necessary under some circumstances.
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
If you’re running a large server, this trace flag may still be of value even after SQL Server 2016.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.