"Table Scan Behavior" in NUMA

Question

Post reply

"Table Scan Behavior" in NUMA

Bhuvnesh

SSC Guru

Points: 59351
More actions
November 7, 2013 at 4:45 am

#284853

I was reading at msdn article at http://technet.microsoft.com/en-us/library/ms345403(v=sql.105).aspx and found that in case of table scan behavior , "A table scan running on Node N1 will only fill the memory associated with node N1 unless the scan is being run in parallel on CPUs from multiple nodes. If the scan is running exclusively on a single node, only the buffer pages from that node will be used."
My question is why the NUMA is not benefitting here by provide foreign memory (from other nodes).
OR
Does above quote imply that other's node memory will be used in case of parallelism only?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

My question is why the NUMA is not benefitting here by provide foreign memory (from other nodes).

That's the whole point of NUMA. Local memory preference improves contention issues.

Does above quote imply that other's node memory will be used in case of parallelism only?

Yes.

-- Gianluca Sartori

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Thanks but can you explain how the contention can be avoided by NUMA ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

In SMP all processors shared the same RAM with no difference between local and foreign memory. In this case, the bus between RAM and CPU was the bottleneck, as it is shared between all CPUs.

NUMA introduces the concept of local and foreign memory, thus reducing contention on the bus. Each NUMA node uses its local memory as far as possible and requests foreign memory only when needed. This reduces contention and helps scalability.

You can read about NUMA on Wikipedia: http://en.wikipedia.org/wiki/Non-uniform_memory_access

-- Gianluca Sartori

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

I think this video also has some numa explanations: http://technet.microsoft.com/en-US/sqlserver/gg508899.aspx

As Gianlucca said, the idea is that memory is linked to a node so that it ensures resources are available for that node and not taken by another one.