SQL 2016 – It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning

Moved from: bobsql.com


The CMemThread waits (PWAIT_MEMTHREAD) can be a point of contention as machine sizes advance.   The CMemThread object type is utilized in 100s of objects throughout the SQL Server code base and can be partitioned globally, by node or by cpu.  


The vast majority of CMemThread objects leverage global partitioning.   Trace flag -T8048 only forces node based partitioning to cpu based partitioning.  Each time a highly contended, global partitioned object was encountered a hotfix was required to partition the object.


SQL Server 2005, 2012 and 2014 contains dynamic latch promotion and demotion logic (Super/Sub-Latch).   The concept is to watch the number