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.
Related Posts
- How To Find Poorly Performing SQL Server Queries To Tune Using Query Store
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: Why Does sp_PressureDetector Make A Big Deal About The Remote DAC?
- SQL Server Community Tools: sp_PressureDetector Doesn’t Show You Irrelevant Waits