Moved from: bobsql.com
Various changes from automatic soft NUMA to CMemThread partitioning have muddied the water around the trace flag –T8048 messaging.
Prior to SQL Server 2016 the trace flag –T8048 is used to upgrade (only) NUMA partitioned, CMemThread objects to CPU partitioned based objects. A few of the most common CMemThread objects have been upgraded to NUMA partitioning, allowing –T8048 to be used as needed for additional partitioning.
The three types of CMemThread partitioning and associated details are described in my previous post: https://blogs.msdn.microsoft.com/psssql/2012/12/20/how-it-works-cmemthread-and-debugging-them/
Keep in mind that the vast majority of CMemThread objects (SQL 2005, 2008, 2008 R2, 2012, 2014, Azure DB and 2016) are not partitioned. A few are partitioned by NUMA node and fewer by CPU during their creation. Trace flag –T8048 ONLY upgrades those CMemThread objects already created with NUMA partitioning. This means the scope of –T8048 is limited to a few, key CMemThread objects.
Recommendation Unchanged: Prior to SQL Server 2016 enable –T8048 on larger systems.
Trace flag –T8048 longer exists in SQL Server 2016. Instead, the design of CMemThread was updated. A CMemThread can still be created with designated partitioning. For example, the memory object supporting spatial is created with CPU partitioning by default so partitioning is never adjusted for the spatial memory object. The new design monitors the waits associated with the individual CMemThread objects. If the waits start to become intrusive SQL Server automatically upgrades the partitioning used by the specific CMemThread. This means the SQL Server will automatically adjust the CMemThread object based on the waiting task activities.
- If the memory object is global (not partitioned) and needs to be optimized the SQL Server partitions it by NUMA node.
- If the memory object is partitioned by NUMA node during creation or due to a previous partitioning adjustment needs further wait optimization it is partitioned by CPU.
This is actually based loosely on the design of sub/super latch promotion in the buffer pool. Just as a contended latch can be partitioned so too is a hot memory object partitioned.
This means that trace flag -T8048 is no longer valid or needed on SQL Server 2016 because the design is such that SQL Server dynamically adjusts to CMemThread wait activities in order to optimize performance.
There are several benefits to this design
- No need for the trace flag
- Dynamically adjusts to the workload which could be different at different times of the day or day of the week
- Does not require additional memory until partitioning is required
Once the memory object is partitioned it remains partitioned for the life of the SQL Server instance. Wait information state is volatile and not persisted so restarting the SQL Server instance returns the memory objects to their default state. Again, if wait pressure occurs the memory object is partitioned with no required intervention from the DBA.
Posted at https://sl.advdat.com/3tdgzwchttps://sl.advdat.com/3tdgzwc