0

I've inherited multiple servers, each containing one database and one instance of SQL Server 2008 R2 SP2. Each server is set to use 12GB min/max memory and has Lock Pages in Memory enabled. The servers have a total of 16GB memory, so 4GB is left for the OS and anything else.

My question is - Is this best practice? Should the servers be set to the same value for min/max memory or will this cause performance issues? I'm investigating a sudden increase in CPU usage across multiple servers and want to rule this out if possible.

Powellellogram
  • 135
  • 1
  • 6
  • 1
    That's about the standard. I wouldn't touch a memory button when investigating CPU. – Erik Darling Jan 12 '18 at 12:11
  • 3
    Personally, I'm not a fan of same min/max. It doesn't let SQL Server have any wiggle room to itself. 12 GB Max Server Memory isn't all inclusive, either. – Sean Gallardy Jan 12 '18 at 12:15
  • I'd personally set max_memory to 11GB based on this article How much memory does my SQL Server actually need?, but as @sp_BlitzErik pointed out: Don't change memory settings because of CPU issues. – John K. N. Jan 12 '18 at 12:24
  • Ooh I totally missed the min/max thing. Sean is right. There's some documentation here: If the same value is specified for both min server memory and max server memory, then once the memory allocated to the SQL Server Database Engine reaches that value, the SQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool. – Erik Darling Jan 12 '18 at 12:33
  • 1
    Did you recently patch Windows for the Spectre/Meltdown CPU issues? That could be the CPU issue :) – Kevin3NF Jan 12 '18 at 13:23
  • @SeanGallardy-Microsoft My thoughts exactly... Guess I'll continue the CPU issue investigation and reduce the min memory by a 1-2GB in the meantime – Powellellogram Jan 12 '18 at 13:28
  • If this is a virtual machine, hosted on VMware, then the min memory may be set like that as a hack to get around VMware ballooning. If that's the case, you're better off NOT setting lock pages in memory, and decreasing the min memory setting.

    (actually, the REAL fix would be to get the VMware admin to dedicate the resources to the VM and not use ballooning, but if that were an option it probably would have already been done and this hack wouldn't be in place.)

     – Eric Cobb Jan 12 '18 at 13:33
  • Use Test-DbaMaxMemory – Kin Shah Jan 12 '18 at 15:43

0 Answers0