How do I limit MS SQL Server memory usage?

Solution 1:

From How to configure memory options using SQL Server Management Studio:

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

1. In Object Explorer, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

You can also do it in T-SQL using the following commands (example):

exec sp_configure 'max server memory', 1024
reconfigure

Solution 2:

To Restrict MS SQL Service memory consumption:

Set "max server memory" in SQL Server Management Studio

1. Now we will set the "max server memory" option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing "Properties".

2. In Object Explorer, right-click a server and select Properties.

3. Click the Memory node as shown below:

4. Under Server Memory Options, enter the amount that you want for  Maximum server memory. Below as you can see we are setting up max server memory to 4096 MB (i.e. 4 GB).

To Restrict MS SQL Service memory consumption:

We can set "max server memory" also by using a T-SQL script:

The following example sets the max server memory option to 4 GB:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Ref: https://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx