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

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.

Ajay Jagannathan - Principal SQL Server Program Manager

Benjamin Satzger - SQL Server Software Engineer

Bob Dorr - Principal SQL Server Software Engineer

DEMO - It Just Runs Faster: Dynamic Memory Object Partitioning

Overview

Creating a demonstration for a thread synchronization object, such as CMemThread, is difficult. Not only are there 1000s of memory objects (sys.dm_os_memory_objects) but if we find a hot spot the development team fixes it. The other issue with such a demonstration is machine variance. To create such a hot spot requires tight collisions across multiple CPUs.

Actual Scenarios

SQL Server 2016 has been vetted by a wide range of customers. The positive impact of these changes has been realized by:
 

• EVERY Customer gets the advantage of the CMemThread improvements if any one of the 1000s of objects encounters contention
• Shipping customer was able to increase overall batch rate significantly
• Web Hosting Service improved web page response times by a third

Video

Demonstration Transcript

The associated video shows the dramatic impact of dynamic, CMemThread partitioning.

The demonstration leverages a custom, extended procedure (xp_alloc) and the srv_alloc / srv_free ODS APIa. The MEMOBJ_XP object is global by default and when heavily used by this stress scenario shows the dramatic impact of dynamic, CMemThread partitioning.

The routine accepts the number of loops and size of allocation and uses srv_alloc and srv_free to access the MEMOBJ_XP memory object.

SRV_RESULT xp_alloc(SRVPROC* pSrvProc)
{

        .. get parameter values Loops and Size
 

for(int iLoop = iLoops; iLoop > 0; iLoop--)

{

    BYTE * pData = srv_alloc(iSize);
if(nullptr != pData)
srv_free(pData);

}

}

The stress uses the RML, Ostress utility to execute 48 threads, repeating 300000 times, 100 loops of 1 byte allocations to maximize pressure on the MEMOBJ_XP, CMemThread object.

ostress.exe -S.\SQLPMO -E -d"master" -Q"execute xp_alloc 100, 1" -n48 -r300000 -b -q