7

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.

|improve this question

migrated from serverfault.com Sep 17 '13 at 21:37

This question came from our site for system and network administrators.

  • Yes - RAM is balanced - 128GB per socket, 2 sockets – Mark Sep 17 '13 at 10:12
  • 3
    I have a few other questions. What do you mean by "allocate?" Is this virtual or physical? I only ask because the 198GB of RAM is an odd configuration (unless you have a blade chassis that has 99 slots for 2GB sticks). How are you limiting SQL Server to 50%? Did you enable LPIM? Is anything else running on this server? Antivirus? – swasheck Sep 17 '13 at 22:24
  • 3
    In addition to swashesk's questions, it might also be useful to know what make and model of server this is. As well, is that SQL Server 2012 Service Pack 1 CU4 or 2012 CU4 (pre SP1)? Happy to answer with what information I would gather to diagnose the problem but it's probably best to get more details first to avoid misdirecting you. – James L Sep 17 '13 at 22:38
  • 4
    I assume 198 GB is supposed to be 192, which is common for a triple-channel memory architecture (2 sockets x 6 memory slots/socket x 16 GB/slot). In any event, have you run a RAM testing program, or swapped out the memory with spares? Sounds like it could be a bad memory chip or something. Did you buy those two machines at the same time? – Jon Seigel Sep 17 '13 at 22:45
5

I'd check the perfmon metrics for per-cpu CPU busy, as well as the perfmon metrics instance-wide and per NUMA node for PLE, database pages, etc. Two recent SQL Server KBs

However, for some workloads, especially with higher core count, high concurrent query count, and lots of database disk IO, using trace flags 8015 (disable NUMA support at SQL Server level) and 8048 (remove per-socket query memory allocation botlleneck) will provide even better results than the fixes contained in SQL Server 2012 SP1 CU4. (I confirmed this on our test equipment with simulations of our workload - YMMV)

Trace flag 8015 deserves thorough evaluation before deployment. Memory affinity and attending lower memory latency is sacrificed for managing a single large bpool. It also results in one lazy writer instead of one per NUMA node, and the idea of affinitizing a connection endpoint within a single SQL Server instance to each NUMA node also disappears. But for some workloads the benefits are undeniable. Don't use 8015 without 8048.

I've never seen or heard of any measurable cost to adding trace flag 8048, and if CMEMTHREAD waits and associated spinlocks are triggered by query memory allocation - its the only reliable way to eliminate them.

|improve this answer
  • We've implemented sql_sasquatch's trace flags, and the server is now stable and performant. Perhaps MS haven't got the NUMA support working 100% in CU4? – Mark Sep 27 '13 at 8:23

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.