The SQL Server memory leak myth

If you glance at the Processes tab in Windows Task Manager on your DB Server, you can see sqlservr.exe process uses huge amount of memory.  Is that SQL Server has a memory leak? No.

SQL Server is designed to use memory in large quantities, in order to cache information, such as recently used data pages. This improves performance by reducing the need to consistently read data from disk (a much slower and more expensive operation than accessing it from memory).

SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the operating system has set the memory low resource notification flag, which triggers SQL Server to reduce its memory allocations.

However, SQL Server offers two instance-level memory settings that can be used to control how memory is allocated to, and removed from, the buffer pool. They are,

·     min server memory - specifies the minimum size to which SQL Server can shrink the buffer pool when under memory pressure; it does not specify the minimum amount of memory that SQL Server will initially allocate.

·     max server memory – specifies the maximum amount of memory that SQL Server can use for the buffer pool, which is primarily used for caching data pages in memory.

It is recommended that the max server memory is set appropriately for a SQL instance, based on workload, in order to ensure that the Windows OS still has sufficient available memory, to prevent the OS low memory notifications.

To Change max server memory

Right Click on Instance name in Object Explorer -> Select Properties -> Select memory page in server properties -> Change server memory options.