Have you ever noticed that SQL Server loves to use memory, even when there is not much activity happening on the server?  Is there a way to manage the amount of memory utilized by SQL Server? Yes, you can limit the amount of memory SQL Server can use. In this blog, we will examine where to set the limitation and explain how it would be done.  You will need to access SQL Server Management Studio(SSMS). 

Go to your SSMS, log into it.  Right click on the server name and select Properties.

Next go to Select a Page and click on Memory and locate the Maximum server memory (In MB) field.  In this example, the SQL Server that is installed in my virtual machine had already been limited to 4 gigs. The machine has 8 gigs of physical RAM. What I have often seen is the value in the  Maximum server memory field, is an amount equal to all of the available physical memory. 

How much memory should you allocate to SQL Server? Since every environment is different there will not be one answer that fits all environment. But here is a very basic example that does not take into account any other applications you may have installed on the SQL Server machine. In this example, the machine where SQL Server resides has 32 gigs of physical memory. 

The first thing to consider is to allow enough physical memory for the operating system (OS).  You would want to allow between 1-4 GB for the operating system., more is better for the OS. If the OS runs out of memory, it starts paging instead of using memory. Paging is a slower process, and you want to avoid that from happening. Then, subtract the amount of memory you allocated to the operating system and assign the remainder to SQL Server.  In our example, we will reserve 4 gigs of memory for the OS. You would then enter in the max server memory box,  32 - 4 or 28 gigs. If you represent 28 gigs in megabytes, it would be 28 x 1024 = 28, 672.   (1 gig = 1024 megabyte)

The Maximum server memory (in MB) setting would look like this. 

A quick way to find out how much memory your SSMS is using, on the machine where it's running open Task Manager and locate sqlserver.exe in the Name column.

I hope this blog gave you awareness about how to limit the amount memory to SSMS. SSMS should not be allowed to use all available memory on the machine, it will take it all and leave the OS with very little and force it to begin paging. As always when making any changes, please consult with your business partner.