Thursday, October 25, 2012

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.

7 comments:

  1. Hi, great to see your website. I like the content and the research done behind every aspect of your blog. It looks great and very knowledgeable. Keep it up the good work. 2018 hostgator coupons

    ReplyDelete
  2. (You could also receive an unexpected inheritance from a sibling or more distant relative.) While the unanticipated wealth from such a surprise can be a boon financially, it can also create issues in your own retirement and estate planning.nagy pl├╝ss maci

    ReplyDelete
  3. This post is good enough to make somebody understand this amazing thing, and I’m sure everyone will appreciate this interesting things. website hosting companies in south africa

    ReplyDelete
  4. It is a decent time to pick committed server facilitating in the event that you are a business who is hoping to develop and need more alternatives, adaptability, and control than shared facilitating and VPS gives. esxi dedicated server hosting

    ReplyDelete
  5. Committed servers frequently picked by people and organizations who are hoping to update from a common server plan since they have outgrown their mutual server and need more transfer speed and space to run their applications and handle higher measures of traffic. https://monovm.com/vps-linux/

    ReplyDelete
  6. 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. hostgator coupon

    ReplyDelete
  7. Great work man i would like to congratulate you on this effort https://ello.co/elmenybox99

    ReplyDelete