Q: I thought I've seen reference recently to problems with SQL2005 not releasing memory when under pressure, and this was possibly due to the Lock Pages In Memory option. We have a server where this seems to be happening, i.e. SQL2005 uses all available memory and the server basically dies because there's no memory left for other processes.
How do I make it work like SQL2000 where it will release memory when it detects memory pressure from other applications?
A: The memory behavior is slightly different between SQL Server 2005 and SQL Server 2000. SQL Server 2000 won't respond to memory pressure when running with lock pages in memory option so you will have to configure max server memory setting in such a way that you leave enough free memory to Windows and other applications on the machine (see memory setting recommendations below)
SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled. However there is a caveat that you need to be aware of - when OS hits memory pressure, depending on the state of the machine and applications, it could be really slow - attempting to free up the resources. This means that SQL Server might not get enough CPU cycles to respond to memory pressure quickly and hence causing other applications and possibly OS to return OOM errors. In case when lock pages in memory is not enabled it is possible that OS will page out SQL Server. This situation might result in inappropriate SQL Server performance.
You can avoid such conditions by setting max memory setting to appropriate value. So what is this value? I usually recommend to derive this value based on amount of RAM available on the machine and amount of available memory you need to keep OS and other applications happy.
RAM Installed Available Memory (as reported by TM) under heavy load you would want to have
< 4GB 512MB - 1GB4-32GB 1GB - 2GB32GB - 128GB 2GB-4GB128GB - 4GB-
There are couple of things you need be aware of:
A. You only need to consider setting max server memory if you expect load on the machine to be memory bound
B. You need to look at the machine during heavy load to come up with appropriate max server memory setting so that you keep available memory in recommended range
C. The more RAM you have on the machine (32GB+) the more important to have max server memory setting on.
D. My recommendations are "personal" recomendations, you will need to tune your box to find what is the best combination for you.
E. Max worker threads setting does affect how much memory server will require under heavy concurrent load. Consider on x64 platform each SQL Server thread can consume 2MB of physical memory. So if you configure SQL Server for 2000 threads, under heavy load in addition to max server memory it might require additional 4GB of physical memory (2MB * 2000), it is even higher on IA64, i.e 8GB (4MB * 2000)
F. Max server memory setting only controls size of the Buffer Pool and hence doesn't affect allocations going through Multi Page Allocator (MPA). If your load does require MPA you will have to take that into account as well. You can monitor your MPA usage by using sys.dm_os_memory_clerks DMV.
G. Memory allocations requested by external components such as xps, COM (sp_OACreate), and others are not controlled by max server memory setting
Let me know if you have more questions