Memory starvation for non-sql server applications | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Memory starvation for non-sql server applications

Hi, I have 2 different servers (dedicated solely for sql server database) with server1 having a total physical memory of 1280 mb & server2 having a total physical memory of 3840 mb, now the sql server on both the servers has the default config_value for "min server memory" = 0 & "max server memory" = 2147483647 mb. Server1 consists of sql server 2000 & server2 consists of sql server 7.0 . I applied a perfmon on both the servers & in both the case the buffer cache hit ratio is above 99 % & sql server is not starved for memory, but the total available memory & total memory pages faults/sec are considerably high due to which it seems that all the other applications or processes including the OS processes may be having shortage of memory. Now my doubt is since sql server as a product has its memory autotuned ie : whatever memory it requires it uses while the rest is released back to the system, therefore is the starvation of memory for the non-sql server application due to the our "max server memory" = 2147483647 mb value or the server is physically facing a shortage of memory or possibly even sql server may not be even releasing the memory back to the system ? Thanks & Rgds
Bhushan Agrawal
We always set a limit on max memory to leave a couple of hundred MB to the OS or sometimes more if there are other application on the server. In theory SQL server should give back memory to other applications or the OS when they need it but it doesn’t seem to work 100%. So cap the max memory and leave 250-300MB for the OS.
If the SQL server is not a dedicated machine, then I suggest to set the min and max memory settings for SQL. Otherwise let SQL manage the memory dynamically. SQL Server grows and shrinks its memory usage dynamically by committing and de-committing buffers from the buffers reserved at startup. The LazyWriter process is responsible for growing and shrinking the BPool. A committed bitmap array is maintained to track the commit or de-commit of buffers. An interesting information from this MSDN blog about memory management. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.