For various (good and positive) reasons I never seem to get the time to write blog articles these days, however with the authoring of my next SQLBits session now on my todo list I thought I’d take a moment to explain the background behind it and what its likely to contain.
What is NUMA?
NUMA stands for Non-Uniform Memory Architecture and is a server hardware architecture which any modern server with more than 1 CPU will use. In two sentences, to make servers scale better each CPU in a server is now only directly connected to specific pieces of the server’s physical memory. If a CPU needs to access memory which it isn’t directly connected to it needs to go via a memory controllers, this adds latency to the memory request so in an ideal world modern software should do its best to avoid ever needing to read “foreign memory”. There’s also something called Soft-NUMA which can be misleadingly positioned as being similar to Hard-NUMA, I’ll talk about that more in my session.
What’s this got to do with SQL Server?
Today, SQL Server is regularly deployed on servers with several CPUs and large amounts of memory and as a consequence both SQL Server and Windows are NUMA aware. They each do their part in trying to ensure these expensive “foreign” memory requests are kept to a minimum, Windows at the CPU instruction level, SQL Server at the SQLOS scheduler level.
You can’t influence how SQL Server works in the NUMA world, it all happens deep within the internals, but you can see NUMA “in action”. A lot of information about how SQL Sever is internally load balancing its workload and caches between the server’s NUMA nodes is exposed in the DMVs. And, if you’re anything like me then when you start looking into these DMVs you’ll be curious about what’s actually happening internally. How do parallel queries run across multiple schedulers when there is a “buffer pool” per NUMA node, why does all the memory seem to be assigned to one NUMA etc etc.
It was these questions which made me curious enough to research the topic for myself earlier this year and I hope for those who come to my session on the Friday in March you’ll leave equally as impressed as I was about how SQL Server “floats” across your server’s motherboard.