How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes

There seems to be some semantic(s) confusion on the books online description of SOFT NUMA. The area of confusion is from the SQL Server 2008 Books Online section, shown below.

Soft-NUMA

SQL Server allows you to group CPUs into nodes referred to as soft-NUMA. You usually configure soft-NUMA when you have many CPUs and do not have hardware NUMA, but you can also use soft-NUMA to subdivide hardware NUMA nodes into smaller groups. Only the SQL Server scheduler and SQL Server Network Interface (SNI) are soft-NUMA aware. Memory nodes are created based on hardware NUMA and therefore not impacted by soft-NUMA. So, for example, if you have an SMP computer with eight CPUs and you create four soft-NUMA nodes with two CPUs each, you will only have one memory node serving all four NUMA nodes. Soft-NUMA does not provide memory to CPU affinity.

The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA. There is a single I/O thread and a single lazy writer thread for each NUMA node. Depending on the usage of the database, these single threads may be a significant performance bottleneck. Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance.

You cannot create a soft-NUMA that includes CPUs from different hardware NUMA nodes. For example, if your hardware has eight CPUs (0..7) and you have two hardware NUMA nodes (0-3 and 4-7), you can create soft-NUMA by combining CPU(0,1) and CPU(2,3). You cannot create soft-NUMA using CPU (1, 5), but you can use CPU affinity to affinitize an instance of SQL Server to CPUs from different NUMA nodes. So in the previous example, if SQL Server uses CPUs 0-3, you will have one I/O thread and one lazy writer thread. If, in the previous example SQL Server uses CPUs 1, 2, 5, and 6, you will access two NUMA nodes and have two I/O threads and two lazy writer threads.  

The confusion is centered on the use of memory node it a bit of a generic way and combining a general I/O comment and some beta/CTP behaviors. Instead this section should make a clear statement about what buffer pool does with the memory and what the SQL OS considers a memory node for tracking.  

The SQL OS memory node is aligned for each physical NUMA node presented on the system. This is regardless of the SOFT NUMA usage. As stated in the books online documentation the Soft NUMA configuration is not allowed to cross physical NUMA (SQL OS Memory Node) boundaries. Soft NUMA allows you to divide a physical node into logical nodes but you are not allowed to combine physical nodes into a logical node.  

There were some beta/CTP builds were Soft NUMA is enabled the buffer pool to treat all memory as a flat memory model (single node). Instead of tracking and handling per, physical node information the buffer pool will treat all memory as if only a single, physical node exists. Now the only way to tell buffer pool to treat all memory as a single node (all flat access) is to enabled trace flag 839 or 8015.

The I/O comment directly refers to the I/O completion port and thread that is created on a per logical node. So you can configure soft NUMA to allow advanced TCP/IP bindings and each logical node receives a specific I/O completion port and managing thread. Logical nodes do NOT receive additional lazy writer thread but the physical nodes do.

NOTE: The I/O Completion threads in SQL Server 2005 and 2008 are designed to handle connection requests and TDS traffic. They are NOT handling database, data and log file I/O operations.

The lazy writer thread creations are tied to the SQL OS view of the physical NUMA memory nodes. So whatever the hardware presents as physical NUMA nodes will equate to the number of lazy writer threads that are created.  The trace flag 8015 tells SQL OS to ignore physical NUMA detection. As with any trace flag this should be used with care as this trace flag reverts behavior to a pre-SQL 2005 logical state and is not recommended for production use.  

The following table outlines the expected behavior of SQL Server 2008. The behavior can be slightly different on SQL Server 2005 installations.