1

I need to understand following behavior of memory distribution across numa nodes within sql server

Current set up:

2 node cluster SQL server 2014 SP2 with virtualization disabled

We have 4 NUMA nodes with 4 sockets with each 20 cores making total of 80 logical processors available using ent core based licensing. Total memory on box is 1 TB with max memory to 656 GB to sql

When checking the memory usage as i blve each numa node will have its own based on max memory/ no of numa nodes:

Therefore all 4 numa nodes have total node memories as 164 GB each.

However when i check the available free across each node they vary by quite a that is numa nodes 0 has 256 MB available, node 1 has 127, node 3 has 111 and 4 has 28.

Similarly when i check the PLE it also responds similarly across all numa nodes- ie on avg node 1 is having ple of 687, 2 with ple 699, 3 with ple 333 and 4 with 122. I know these PLE is just avg and max min changes throughout the day.

But can this uneven distribution cause a performance issue or is there a change which can be implemented? Thanks

  • I believe what you are seeing is normal. I seldom see uniform distribution on my system having NUMA, they are always changing – Shanky Nov 15 '18 at 17:04
  • @Shanky : thanks. But wouldn't one node running low on available memory impact the queries running on those specific schedulers within nodes. We have a batch request/ sec on this server for avg of 6k. Just checking if this is an issue – BeginnerDBA Nov 15 '18 at 17:32
  • There is a Foreign Node Memory perfmon counter which tracks amount of Non local memory present on that node if that is high it may signify that current NUMA node is facing memory pressure. Can you check that counter please track that for 2-3 hours and sample it – Shanky Nov 16 '18 at 7:44
  • @Shanky : i kept for couple of hours for that counter: node3 has on avg 365 MB, node 2: 1.7 GB, node 1:188 MB, node 0: : 388 MB – BeginnerDBA Nov 16 '18 at 18:56
  • That is pretty much normal and this means there is foreign memory being excessively used and this is good sign showing that current memory if "sufficient" for NUMA nodes – Shanky Nov 17 '18 at 10:52
0

For a system with 1TB of RAM, and over 600 GB dedicated for SQL Server, page-life-expectancy of 300 to 600 seconds seems very low. That indicates, on average, every page in the buffer pool is being replaced every 5 to 10 minutes. That means the server is experiencing significant I/O just to service queries - as in 100GB per minute (or more) of data being transferred from disk into memory, all the time. I'd recommend looking at getting PLE higher, by:

  1. Look for queries that perform an excessive amount of I/O. Check if those queries might be optimized to avoid excessive reads by either adding appropriate indexes or breaking large queries up into smaller chunks with intermediate #temp tables.

  2. Add physical memory. On this note, why is max server memory set to 656GB when the machine has 1TB of memory? You're leaving 250GB on the table, that SQL Server would probably dearly love to use. Make sure this server is dedicated to only run SQL Server, and up the max_server_memory to at least 900GB, perhaps more. I'd set min server memory to something like 768GB to ensure Windows can "claw-back" some memory from SQL Server if it really needs to.

In extreme cases, you may want to configure thread affinity per the choices listed in this MSDN blog article. Trace Flag 2479 can be used to enable "Connection Bound" thread placement, which place threads on one node. SQL Server treats threads as if the connection node is the only node on the system, forcing threads for any given query to be processed by the NUMA node where the client connection started. Per that page, parallelism is allowed as long as free threads on the node are >= (degree-of-parallelism * branches-in-query).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.