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 of acquires on a latch along comparing sampling rates as to how long it should take to acquire a latch without contention. When a shared latch can be promoted (partitioned) in order to improve performance and scalability SQL Server will do so.
Taking a page from the latch design concepts, CMemThread is designed in SQL Server 2016 to dynamically promote a contended CMemThread object. SQL Server 2016 detects contention points on a specific CMemThread object and promotes the object to a per node or per cpu based implementation. Once promoted the object remains promoted until the SQL Server instance is restarted.
The dynamic CMemThread design is a superset of -T8048 behavior, allowing you to deploy SQL Server 2016 on a wide variety of hardware environments and SQL Server 2016 automatically adjusts CMemThread behavior to the applicable load. By partitioning contended CMemThread objects it removes the waits behaviors and allows SQL Server 2016 to scale to your applications needs.
The sys.dm_os_memory_objects DMV has been extended to include additional contention information which can be used to determine the promotion level along with new XEvents provided to monitor the activity.
‘It Just Runs Faster’ – Apply SQL Server 2016 and SQL Server dynamically partitions a CMemThread encountering contention, increasing scalability of the instance.
Posted at https://sl.advdat.com/3tEoz9Nhttps://sl.advdat.com/3tEoz9N
