MSSQLWIKI

Karthick P.K on SQL Server

SQL Server NUMA load distribution

Posted by Karthick P.K on November 22, 2012

When port affinity is not configured all the connection to SQL Server enters through single port and connections are tied to nodes in round robin basis.

 

We might end with Imbalance of Workload in NUMA systems under below conditions.

 

1. When a connection is tied (or) affinitized to a node, all the work from that connection will be completed on the same node (in which connection is directed)  if plans are serial.  We don’t consider the CPU load across the NUMA to pick up the node for serial plans, We use the same node in which connection is made for serial plan execution. Parallel query would use any NUMA node regardless of node this query came from. When all the queries execute from connections made to same node and if plans are also serial we might end up with overloading one Node while others are not fully used.

 

2. State of each nodes is internally maintained by SQL Server and updated every 2 seconds so there is  remote possibility that all parallel queries end with same node some times and cause spike in one node, while the other nodes are unused.

 

3. When there is imbalance between the number of online schedulers in each node (Ex: 16-CPU in Node1 and 4-CPU in Node2 ) and if all plans are serial (assume we have set Max DOP 1) We might end up with overloading the schedulers in node with least schedulers. while the schedulers on other node is underused, similarly when memory is shared across nodes we share it equally irrespective of number of schedulers on each node so in this case first 16 schedulers would have got  half of memory and 4 schedulers of second node would have got remaining half. So ensure you choose the CPU affinity carefully (Specially when you have installed SQL Server with limited processor license on system with larger number of CPU’s). 

 

 

Image 1:  sys.dm_os_schedulers (6 – CPU’S on node-0 and  1- CPU on node-1. Look at current task count)

 

clip_image002

 

Image 2 (Look at the current and pending task in node 0 and in node 1)

clip_image002[4]

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

 

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

3 Responses to “SQL Server NUMA load distribution”

  1. […] Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.   […]

  2. […] SQL Server NUMA load distribution […]

  3. I have been exploring for a little for any high quality articles or blog posts
    on this kind of space . Exploring in Yahoo I at last stumbled upon this site.
    Reading this info So i’m glad to show that
    I’ve a very good uncanny feeling I came upon exactly what I
    needed. I so much for sure will make sure to do not omit
    this web site and give it a look regularly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: