SQL Server 2008/2008 R2 on Newer Machines with More Than 8 CPUs Presented per NUMA Node May Need Trace Flag 8048

Published Jan 15 2019 02:02 PM 169 Views
Microsoft
First published on MSDN on Sep 01, 2011

Applies To: SQL 2008, 2008 R2 and Denail builds

The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for.   The developer may choose a global, CPU, Node, or even worker partitioning scheme.   Several of the allocation activities within SQL Server use the CMemPartitioned allocator.  This partitions the memory by CPU or NUMA node to increase concurrency and performance.

You can picture CMemPartitioned like a standard heap (it is not a HeapCreate ) but this concept is the same.  When you create a heap you can specify if you want synchronized assess, default size and other attributes.   When the SQL Server developer creates a memory object they indicate that they want things like thread safe access, the partitioning scheme and other options.

The developer creates the object so when a new allocation occurs the behavior is upheld.  On the left is a request from a worker against a NODE based memory object.  This will use a synchronization object (usually CMEMTHREAD or SOS_SUSPEND_QUEUE type) at the NODE level to allocate memory local to the workers assigned NUMA NODE.   On the right is an allocation against a CPU based memory object.  This will use a synchronization object at the CPU level to allocate memory local to the workers CPU.

In most cases the CPU based design reduces synchronization collisions the most because of the way SQL OS handles logical scheduling.  Preemptive and background tasks make collisions possible but CPU level reduces the frequency greatly.  However, going to CPU based partitioning means more overhead to maintain individual CPU access paths and associated memory lists.

The NODE based scheme reduces the overhead to the # of nodes but can slightly increase the collision possibilities and may impact ultimate, performance results for very specific scenarios. I want to caution you the scenarios encountered by Microsoft CSS have been limited to very specific scopes and query patterns.

Newer hardware with multi-core CPUs can present more than 8 CPUs within a single NUMA node.  Microsoft has observed that when you approach and exceed 8 CPUs per node the NODE based partitioning may not scale as well for specific query patterns.   However, using trace flag 8048 (startup parameter only requiring restart of the SQL Server process) all NODE based partitioning is upgraded to CPU based partitioning.   Remember this requires more memory overhead but can provide performance increases on these systems.

HOW DO I KNOW IF I NEED THE TRACE FLAG?

The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spin_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE).   Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.

Bob Dorr - Principal SQL Server Escalation Engineer

%3CLINGO-SUB%20id%3D%22lingo-sub-316646%22%20slang%3D%22en-US%22%3ESQL%20Server%202008%2F2008%20R2%20on%20Newer%20Machines%20with%20More%20Than%208%20CPUs%20Presented%20per%20NUMA%20Node%20May%20Need%20Trace%20Flag%208048%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316646%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Sep%2001%2C%202011%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3EApplies%20To%3A%20%3C%2FSTRONG%3E%20SQL%202008%2C%202008%20R2%20and%20Denail%20builds%3C%2FP%3E%0A%20%20%3CP%3EThe%20SQL%20Server%20developer%20can%20elect%20to%20partition%20memory%20allocations%20at%20different%20levels%20based%20on%20the%20what%20the%20memory%20is%20used%20for.%26nbsp%3B%26nbsp%3B%20The%20developer%20may%20choose%20a%20global%2C%20CPU%2C%20Node%2C%20or%20even%20worker%20partitioning%20scheme.%26nbsp%3B%26nbsp%3B%20Several%20of%20the%20allocation%20activities%20within%20SQL%20Server%20use%20the%20%3CEM%3E%20CMemPartitioned%20%3C%2FEM%3E%20allocator.%26nbsp%3B%20This%20partitions%20the%20memory%20by%20CPU%20or%20NUMA%20node%20to%20increase%20concurrency%20and%20performance.%3C%2FP%3E%0A%20%20%3CP%3EYou%20can%20picture%20%3CEM%3E%20CMemPartitioned%20%3C%2FEM%3E%20like%20a%20standard%20heap%20(it%20is%20not%20a%20%3CEM%3E%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Faa366599(VS.85).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20HeapCreate%20%3C%2FA%3E%20%3C%2FEM%3E%20)%20but%20this%20concept%20is%20the%20same.%26nbsp%3B%20When%20you%20create%20a%20heap%20you%20can%20specify%20if%20you%20want%20synchronized%20assess%2C%20default%20size%20and%20other%20attributes.%26nbsp%3B%26nbsp%3B%20When%20the%20SQL%20Server%20developer%20creates%20a%20memory%20object%20they%20indicate%20that%20they%20want%20things%20like%20thread%20safe%20access%2C%20the%20partitioning%20scheme%20and%20other%20options.%3C%2FP%3E%0A%20%20%3CP%3EThe%20developer%20creates%20the%20object%20so%20when%20a%20new%20allocation%20occurs%20the%20behavior%20is%20upheld.%26nbsp%3B%20On%20the%20left%20is%20a%20request%20from%20a%20worker%20against%20a%20NODE%20based%20memory%20object.%26nbsp%3B%20This%20will%20use%20a%20synchronization%20object%20(usually%20CMEMTHREAD%20or%20SOS_SUSPEND_QUEUE%20type)%20at%20the%20NODE%20level%20to%20allocate%20memory%20local%20to%20the%20workers%20assigned%20NUMA%20NODE.%26nbsp%3B%26nbsp%3B%20On%20the%20right%20is%20an%20allocation%20against%20a%20CPU%20based%20memory%20object.%26nbsp%3B%20This%20will%20use%20a%20synchronization%20object%20at%20the%20CPU%20level%20to%20allocate%20memory%20local%20to%20the%20workers%20CPU.%3C%2FP%3E%0A%20%20%3CP%3EIn%20most%20cases%20the%20CPU%20based%20design%20reduces%20synchronization%20collisions%20the%20most%20because%20of%20the%20way%20SQL%20OS%20handles%20logical%20scheduling.%26nbsp%3B%20Preemptive%20and%20background%20tasks%20make%20collisions%20possible%20but%20CPU%20level%20reduces%20the%20frequency%20greatly.%26nbsp%3B%20However%2C%20going%20to%20CPU%20based%20partitioning%20means%20more%20overhead%20to%20maintain%20individual%20CPU%20access%20paths%20and%20associated%20memory%20lists.%3C%2FP%3E%0A%20%20%3CP%3EThe%20NODE%20based%20scheme%20reduces%20the%20overhead%20to%20the%20%23%20of%20nodes%20but%20can%20slightly%20increase%20the%20collision%20possibilities%20and%20may%20impact%20ultimate%2C%20performance%20results%20for%20%3CEM%3E%20very%20specific%20scenarios.%20%3CSTRONG%3E%20I%20want%20to%20caution%20you%20the%20scenarios%20encountered%20by%20Microsoft%20CSS%20have%20been%20limited%20to%20very%20specific%20scopes%20and%20query%20patterns.%20%3C%2FSTRONG%3E%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F66810i85926B794EC93516%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ENewer%20hardware%20with%20multi-core%20CPUs%20can%20present%20more%20than%208%20CPUs%20within%20a%20single%20NUMA%20node.%26nbsp%3B%20Microsoft%20has%20observed%20that%20when%20you%20approach%20and%20exceed%208%20CPUs%20per%20node%20the%20NODE%20based%20partitioning%20may%20not%20scale%20as%20well%20for%20specific%20query%20patterns.%26nbsp%3B%26nbsp%3B%20However%2C%20using%20trace%20flag%208048%20(startup%20parameter%20only%20requiring%20restart%20of%20the%20SQL%20Server%20process)%20all%20NODE%20based%20partitioning%20is%20upgraded%20to%20CPU%20based%20partitioning.%26nbsp%3B%26nbsp%3B%20Remember%20this%20requires%20more%20memory%20overhead%20but%20can%20provide%20performance%20increases%20on%20these%20systems.%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20HOW%20DO%20I%20KNOW%20IF%20I%20NEED%20THE%20TRACE%20FLAG%3F%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20issue%20is%20commonly%20identified%20by%20looking%20as%20the%20DMVs%20%3CEM%3E%20dm_os_wait_stats%20%3C%2FEM%3E%20and%20%3CEM%3E%20dm_os_spin_stats%20%3C%2FEM%3E%20for%20types%20(CMEMTHREAD%20and%20SOS_SUSPEND_QUEUE).%26nbsp%3B%26nbsp%3B%20Microsoft%20CSS%20usually%20sees%20the%20spins%20jump%20into%20the%20trillions%20and%20the%20waits%20become%20a%20hot%20spot.%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-316646%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Sep%2001%2C%202011%20Applies%20To%3A%26nbsp%3B%20SQL%202008%2C%202008%20R2%20and%20Denail%20builds%20The%20SQL%20Server%20developer%20can%20elect%20to%20partition%20memory%20allocations%20at%20different%20levels%20based%20on%20the%20what%20the%20memory%20is%20used%20for.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-316646%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ememory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%202008%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 02:02 PM
Updated by: