Thread by @sqL_handLe

there's some good stuff in here about #sqlserver soft-NUMA which applies to autosoftNUMA
~~
Understanding Non-uniform Memory Access
2012 October 4
docs.microsoft.com/en-us/previous…

"Memory nodes are created based on hardware NUMA and therefore not impacted by soft-NUMA."
Thumbs up! soft-numa nodes must be fully contained in memory nodes. so memory nodes enforce something on soft-numa, but not the other way 'round.

"Soft-NUMA does not provide memory to CPU affinity."
Thumbs up, i think. i guess i would probably have said that memory-to-cpu affinity is based on memory node rather than soft-NUMA node.

"The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA."
ok, this is getting dicey.

"Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance."
and... that's a problem, bub.
Maybe this was true about lazy writers in #sqlserver 2008? i don't think so. Definitely not true in #sqlserver 2019.

all right then. my super-secret 64 "logical cpu" system with #sqlserver 2019 cu9 installed. with a single memory node. but 4 softNUMA nodes due to autosoftNUMA.

A single lazy writer. because 1 per memory node.

but for some extra fun notice there are 8 transaction log writers, all on softNUMA node 3.

at 128 "logical cpus", 2 lazy writers. because 2 memory nodes. (and 2 processor groups because limited to 64 "logical processors" in a processor group).

autosoftNUMA created 8 softNUMA nodes.

and we're still at 8 transaction log writers, because as of #sqlserver 2019 cu9 that's the most you'll get.

anyway, i bring this up because a colleague came across the article after we discussed disabling autosoftNUMA on a system.
my colleague wanted to make sure there wouldn;t be a lazy writer bottleneck due to disabling autosoftNUMA.
nope, i don't think so.

https://twitter.com/sqL_handLe/status/1377013654961393670?s=20

ahhh, here's the other #sqlserver article i had in mind.
~~
How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes
2010 April 2
techcommunity.microsoft.com/t5/sql-server-…

"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..."

"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."

"Logical nodes do NOT receive additional lazy writer thread but the physical nodes do... 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."