0

This question already has an answer here:

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.

marked as duplicate by Kin Shah sql-server Jan 12 '18 at 15:44

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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. – hot2use 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

Browse other questions tagged or ask your own question.