We're running SQL Server 2012 SP1 CU4 on a 64-bit 12-core, 2 socket server using NUMA architecture server. OS is Windows Server 2008 R2 x64.
Whenever we allocate more than 50% of the physical RAM to SQL server, the machine becomes unstable or unresponsive.
The symptoms are typical of OS memory starvation - i.e. process fail to start, GUI object fail to render, applications misbehave, remote desktop sessions become unresponsive, etc.
We’ve seen this on 2 different machines – one with 192GB RAM, the other with 256GB RAM… as soon as SQL is given more than 50% of the respective physical total, the symptoms appear.
Has anyone else seen this behavior?
--- EDIT ---
The SQL service runs under an account with LPIM (Lock Pages in Memory) privileges.
McAfee antivirus is, sadly, imposed on the server, though it is at least set up with exclusions for all SQL files.
When the RAM utilization is allowed to exceed 50%, the common behavior we see is:
- High RESOURCE_SEMAPHORE wait times.
- Inability to connect to SQL server or run queries.
- All CPUs maxed out with low disk I/O when running query workloads.
-- EDIT 2 ---
We've tried re-installing SQL (SP1 CU4), and have checked that nothing else is chomping the RAM. Generally, there's at least 100GB of the total 256GB free at any time. When we turn off LPIM, we get see the problem "the operating system has swapped at a significant portion of SQL's memory", which is why we have it turned on.