SQL Server 2014 – Set MAX Memory Based on CPU Model

cpumemorynumasql serversql server 2014

I have a general question regrading setting up the MAX SQL memory settings-

For our current CPU with below specs, server team bumped up OS memory from 400GB to 1TB:

Intel® Xeon® Processor E5-4640 v2 – It's a 4 socket 10 core per processors with HT enabled so total of 80 logical processors 4 NUMA nodes

Looking at the specs on intel site here max memory supported seems to be 768.

So what does that exactly mean in terms of SQL space? even OS has 1 TB should one be setting MAX memory settings based on 1 TB or 768 GB? We can see issues from SQL when trying to bump up the MAX memory settings lets say over 850 and when we lower it back to 400 goes back to normal.

Issues when bump up SQL MAX memory – App time outs for queries running over 3 secs based on time out settings.

Tested this couple of times and looks to be same. Need to understand why

Appreciate your inputs on this.

Best Answer

The max memory setting is how much RAM SQL will use. It will usually not use more then the max memory set. (Following Shanky's comment, see below for the exception to that rule): Quoting from Memory Management Architecture Guide

Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

Now if your hardware has 1 TB but only 768Gb are recognize in the OS, setting up the max memory to higher then 768Gb will allow SQL to use all available RAM (all the RAM detected by the OS) and this could cause potential issue with the OS as it will have to do memory pressure on SQL to claim it back.

Set the SQL MaxMemory setting depending on how much RAM is seen by the OS and it should be all good. (Leaving at least 4 Gb for the OS)

P.s. Do not set min memory setting the the same value as the max memory. Set it lower as SQL will not release RAM under the min value when there is memory pressure and this could lead to an OS crash.

Related Solutions

Sql-server – SQL Server – Anyone use SUMA, trace flag 8048, or trace flag 8015

This is an awesome post.

To answer your final question, I'd speculate that your answer is "yes".

That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading. Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.

On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.

Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds. Makes for some interesting analysis when workload changes or trace flags are implemented:

SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC'

and

SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) 
GROUP BY wait_type
ORDER BY COUNT (*) DESC

Mysql – Can putting thesql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance

I have a client at my employer's company with 192GB RAM using 162GB Buffer Pool.

Here is what we did for the client

InnoDB Tuning : I set the following options
- innodb_read_io_threads (maxed it to 64)
- innodb_write_io_threads (maxed it to 64)
- innodb_thread_concurrency = 0 (It's default now in MySQL 5.5/5.6)
- innodb_io_capacity (set it to IOPs of Disk, for this client, 20000 [2-0-0-0-0 not typo])
- innodb_buffer_pool_size=162G
- innodb_buffer_pool_instances=2 (Number of Physical Cores)
OS Tuning (numactl --interleave=all)

If you forget numactl --interleave=all, the buffer pool experiences swapping.

Here are my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

Best Answer

Related Solutions

Sql-server – SQL Server – Anyone use SUMA, trace flag 8048, or trace flag 8015

Mysql – Can putting thesql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance

Related Question