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)
Image 2 (Look at the current and pending task in node 0 and in node 1)
![clip_image002[4]](https://mssqlwiki.files.wordpress.com/2012/11/clip_image00241.jpg "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.
MSSQLWIKI 