locked
NUMA doubts RRS feed

  • Question

  • Hi SQL Experts,

    Can anyone help in below queries.

    What is NUMA? Why NUMA ? How it is related/affects SQL Server Performance?
    How it affects the MAXDOP setting.

    Please don't paste urls/links. I just wanted to understand in simple layman terms or short descriptions.

    Thanks,

    Sam

    Thursday, March 24, 2016 10:16 AM

Answers

  • NUMA = Non-Uniform Memory Access. As I understand it, different processors own different parts of the memory. Thus, if processor 1 needs to access memory controlled by processor 2 there is extra overhead.

    I think that is a short and simple it can be in layman's terms.

    As Shanky says, the topic far more difficult, and I would be lying if I said that I mastered all of it myself.

    Thursday, March 24, 2016 11:16 AM
  • Hello Sam,

    Additional, see TechNet Understanding Non-uniform Memory Access (SQL Server)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 24, 2016 11:50 AM
  • What is NUMA? Why NUMA ? How it is related/affects SQL Server Performance?
    How it affects the MAXDOP setting.

    As the size of a server gets large, with lots of cores and lots of RAM, if you don't partition it somehow then everything gets in the way of each other and performance goes way down.

    So, the hardware is constructed to partition the memory into chunks.  Things are full speed as long as you stay in your chunk.  Things get much slower if you use memory across multiple chunks.

    So, one SPID should stay within a NUMA chunk if it can.  So all the cores on one physical processor should stay within the same NUMA chunk.

    So if you want a high MAXDOP that would spread work across all the NUMA nodes, then it had better be especially clever about how it does that or it will cross boundaries and slow down.

    However, I've seen almost nothing from Microsoft on what SQL Server actually does, other than these kinds of general statements of high principle.

    Josh

    Thursday, March 24, 2016 4:26 PM

All replies


  • What is NUMA? Why NUMA ? How it is related/affects SQL Server Performance?
    How it affects the MAXDOP setting.

    Please don't paste urls/links. I just wanted to understand in simple layman terms or short descriptions.

    Sam,

    I am sorry but your attitude towards learning is not good one you want things cooked and ready and do not want to give effort. I specially is not going to write description about what is NUMA. Please also note NUMA cannot be explained in layman terms I don't know how to do that because it is not that simple.Your question itself is big and it is strange you are expecting answer which does not include links


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    • Proposed as answer by Enric Vives Thursday, March 24, 2016 2:15 PM
    Thursday, March 24, 2016 10:43 AM
  • NUMA = Non-Uniform Memory Access. As I understand it, different processors own different parts of the memory. Thus, if processor 1 needs to access memory controlled by processor 2 there is extra overhead.

    I think that is a short and simple it can be in layman's terms.

    As Shanky says, the topic far more difficult, and I would be lying if I said that I mastered all of it myself.

    Thursday, March 24, 2016 11:16 AM
  • Hello Sam,

    Additional, see TechNet Understanding Non-uniform Memory Access (SQL Server)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 24, 2016 11:50 AM
  • Simple layman terms, don't worry about it.  Changing the CPU settings is an advanced feature.  You only need to change these settings under extremely rare circumstances.
    Thursday, March 24, 2016 1:23 PM
  • What is NUMA? Why NUMA ? How it is related/affects SQL Server Performance?
    How it affects the MAXDOP setting.

    As the size of a server gets large, with lots of cores and lots of RAM, if you don't partition it somehow then everything gets in the way of each other and performance goes way down.

    So, the hardware is constructed to partition the memory into chunks.  Things are full speed as long as you stay in your chunk.  Things get much slower if you use memory across multiple chunks.

    So, one SPID should stay within a NUMA chunk if it can.  So all the cores on one physical processor should stay within the same NUMA chunk.

    So if you want a high MAXDOP that would spread work across all the NUMA nodes, then it had better be especially clever about how it does that or it will cross boundaries and slow down.

    However, I've seen almost nothing from Microsoft on what SQL Server actually does, other than these kinds of general statements of high principle.

    Josh

    Thursday, March 24, 2016 4:26 PM
  • Hi Shanky,

    If you not interested to answer. That fine but give crappy answers to get noticed in public forums. We are pretty new SQL and so we look for basic understanding or Anology based answers to better understand.

    Thursday, March 24, 2016 5:16 PM
  • Thanks JRStern.
    Thursday, March 24, 2016 5:18 PM
  • Hi Shanky,

    If you not interested to answer. That fine but give crappy answers to get noticed in public forums. We are pretty new SQL and so we look for basic understanding or Anology based answers to better understand.

    Its strange you ask question which ask ready made answers with no effort from your side  and you say I give crappy answers. And regarding getting noticed on forum I dont need that, people already know me.

    My answer was mostly focused on " dont post URL's and links". And I still stick to it ,forum is not a place to get "cooked food" I never do it nor do I encourage. You want answer but you are not ready to read links this seems serious. Post same question on other SQL Server forums and your question would be put on hold. This does not happens on this forum so people just come and dump anything.

    If you are new to SQL Server only reading and reading with hands on can help you and that is what I was suggesting


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Wednesday, April 6, 2016 5:56 AM