Changing the Memory Limit for a Microsoft SQL Server Instance

To change the memory use of a Microsoft SQL Server instance:

  1. Open Microsoft SQL Server Management Studio.
  2. Log in to your server.
  3. In Object Explorer, right-click a server and select Properties.
    Result:The Server Properties dialog is opened.
  4. Click the Memory node.
  5. In Server memory options, enter values to the Minimum server memory and Maximum server memory fields.
  6. Click OK to close the Server Properties dialog.

Guidelines for Microsoft SQL Server Memory Limit

If Microsoft SQL Server memory consumption is not limited, Microsoft SQL Server can use a large amount of memory and the server can after some time start to use virtual memory. This can cause performance issues and other problems.

The best practice is to set the Microsoft SQL Server memory limit so that the operating system does not use the paging file in normal operation. It is usually better to set the memory limit too low than too high. In most environments, you must keep at least 2 GB memory free for the operating system. In systems with more available memory, leave more capacity for the operating system.

There are many things that have an effect on the applicable memory limit. You can, for example, monitor the system behavior to find the correct memory limit.

Examples

If Microsoft SQL Server is used on a dedicated server and the server machine has 8 GB of memory, set the Microsoft SQL Server memory limit to 5 or 6 GB.

If Microsoft SQL Server is used on a server that does also other tasks, make sure that there is enough free memory for other applications. If M-Files Server is used on the same server, decrease the memory limit 1 to 2 GB more. For example, if the server machine has 8 GB of memory and M-Files Server is not in heavy use, the applicable Microsoft SQL Server memory limit is 3 or 4 GB.