Limiting the RAM consumption of MS SQL SERVER

Question

I just rolled a small web application into production on a remote host. After it ran a while, I noticed that MS SQL Server started to consume most of the RAM on the server, starving out IIS and my application.

I have tried changing the "server maximum memory" setting, but eventually the memory usage begins to creep above this setting. Using the activity monitor I have determined that I am not leaving open connections or something obvious so I am guessing its in cache, materialized views and the like.

I am beginning to believe that this setting doesn't mean what I think it means. I do note that if I simply restart the server, the process of memory consumption starts over without any adverse impact on the application - pages load without incident, all retrievals work.

There has got to be a better way to control sql or force it to release some memory back to the system????

Answer 1

From the MS knowledge base:

Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration.

Are you using any extended stored procedures, .NET code, etc that could be leaking memory outside of SQL Server's control?

There is some more discussion on memory/address space usage here

Answer 2

Typically I recommend setting the max server memory to a couple of Gigs below the physical memory installed.

If you need to run IIS and an application server installed on the server then you'll want to give SQL Server less memory.