SQL Server NUMA Surprise

Today I welcome myself back to my blog after months of neglect, sorry blog. 

Today as part of my MCM prep I decided to dive into Software NUMA and SQL Server.  I have read a lot of information regarding Software NUMA over the years but felt that I needed to dive deeper into NUMA to better understand exactly how and when to configure it. 

As part of my studies I came across two blog posts from Johnathan Kehayias (Blog | Twitter) regarding NUMA.  In his second blog on NUMA SQL Server and Soft NUMA  Johnathan does a great job of walking through how he calculated the CPU mask to divide a 24 CPU server into 4 NUMA nodes.  After reading through the blog post I was shocked to find out at the end that Johnathan discovered NUMA does not work as reported in BOL and MSDN. 

 

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

After configuring 4 Soft NUMA nodes SQL Server still only created one lazy writer thread.  This is contrary to BOL which as you can see states that SQL Server will create a lazy writer for each Soft NUMA node.  In his post Johnathan references a post from Bob Dorr, an escalation engineer at Microsoft in the Product Support division, called How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes. In his post Bob explains that additional lazy writer threads are only created for Hard NUMA nodes and not Soft NUMA nodes.

I find this particularly interesting because this goes against everything I studied for the MCITP: Database Administrator certification.  Given this new information from Bob Dorr and Johnathan Kehayias, the next question is why would I implement Soft NUMA?  Soft NUMA will handle I/O completion threads.  When we think of I/O completion threads we would generally think this means writing data to transaction and data files but as Bob Dorr points out, I/O completion threads actually handle connection requests and TDS data. 

If you are considering implementing Soft NUMA or just want to learn more about NUMA I urge you to read the aforementioned blog posts from Johnathan and Bob.