Do you know to never give SQL Server all your RAM?
  v5.0 Posted at 20/03/2020 10:18 AM by Christian Morford-Waite
​Microsoft SQL Server is made to use all the available memory in a server for itself. It will eat all the memory you throw at it. This can be a problem because your other applications may suffer performance problems as all the system memory is gone. To limit this behaviour you can limit the maximum amount of memory SQL is allowed to use.
  1. Open SQL Server Management Studio
    ​​Figure: SQL Server Management Studio - Login Screen
  2. Right click on the server name and select “Properties”
    Figure: SQL Database options and properties menu
  3. Select the “Memory” tab
    Figure: Server Properties showing the ridiculously large value set for the maximum server memory​
  4. You will see that the default number is HUGE. Change this to something more realistic. Let SQL use half of the memory in your server.Leave about 1024MB headroom. For example, if you server has 4GB of RAM, give the SQL server a Maximum server memory of 2048mb.
    Figure: Maximum server memory settings in server properties
This will prevent SQL from “owning” all of the RAM on your system,leaving some memory left for your other applications to use.

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address: