When a process allocates memory it is supposed to de-allocate it and release it back to OS. If it misses to de-allocate the memory due to flaw in code it is called as leak and It can cause memory pressure both to the operating system and application.
What we think wrong about SQL Server memory leak
• SQL Server memory management is designed to dynamically grow and shrink its memory based on the amount of available memory on the system and Max server memory setting in SQLServer.
• Often when performance problems occur, the first counters looked at by system administrators are the CPU utilization and Memory Utilization counters, most often I would suspect through the Windows Task Manager and then PerfMon. When a Windows Administrator/User that doesn’t understand SQL Server sees that SQL Server is consuming 2.7GB of memory on a 4GB server, for whatever reason, the first conclusion that they come to is that SQL Server has a memory leak.
This is incorrect, SQL Server is server based application and its memory manager is designed in such a way that it will keep growing its memory usage on need (Exception large pages) and will not scale down its usage unless there is low memory notification from Windows. Well first recall that SQL Server has two types of memory usage, VAS (aka MemToLeave on 32bit servers) and the Buffer Pool or BPool. We can control the memory usage of SQL Server using Max server memory setting in SQLServer. This setting limits the Bpool usage of SQL Server and doesn’t control the overall memory usage of SQLServer. There are portions of SQLServer memory that is allocated outside BPOOL we do not have a way to control how much memory SQL Server can use outside bpool, but non bool memory usage will be normally low and can be easily estimated by studying the components running in SQL Server.
What can cause SQL Server Memory leak?
SQL Server code has a logic to allocate memory but doesn’t de-allocate it. If any of the components in SQL Server is causing a memory leak in SQL Server it can be identified easily using the DMV’s like sys.dm_os_memory_allocation,sys.dm_os_memory_clerks and sys.dm_os_memory_objects etc., but most of the memory leaks in SQL Server is caused by 3rd party Dll’s which are loaded in SQL Server process.
Note: All the memory allocations by Non SQL server Dll’s loaded in SQL Server will happens in “Mem to Leave”(outside the Bpool) and they are called as direct windows allocations (DWA).
When there is out of memory conditions in SQL Server and if you suspect there is a memory leak.First thing to determine is who is consuming the memory. If SQL Server is not using majority of the memory in MemToLeave and still you get Mem to leave errors probably there is a leak and it caused by some DLL’s loaded in SQL Server.
Below query can be used to determine actual memory consumption by SQL Server in MTL.
select sum(multi_pages_kb) from sys.dm_os_memory_clerks
What is Buffer Pool?
Remember SQL Server has two memory settings that you can control using sp_conifigure. They are max and min server memory. These two setting really control the size of the buffer pool. They do not control overall amount of physical memory consumed by SQL Server. In reality we can’t control amount of memory consumed by SQL Server because there could be external components loaded into server’s process.
When SQL Server starts, during initialization, Buffer Pool first decides how much of VAS it needs to reserve for its usage. It bases its decision on the amount of physical memory, RAM, present on the box.
Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can’t exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.
The size of SQL Server database page is 8KB. Buffer Pool is a cache of data pages. Consequently Buffer Pool operates on pages of 8KB in size. It commits and decommits memory blocks of 8KB granularity only. If external components decide to borrow memory out of Buffer Pool they can only get blocks of 8KB in size. These blocks are not continues in memory. Interesting, right? It means that Buffer Pool can be used as underneath memory manager forSQL Server components as long as they allocate buffers of 8KB. (Sometimes pages allocated from BP are referred as stolen).
Below is the query to see the Buffer Pool Memory Use:
SELECT physical_memory_in_bytes / 1024 / 1024 AS physical_memory_MB, bpool_committed / 128 AS bpool_MB, bpool_commit_target / 128 AS bpool_target_MB, bpool_visible / 128 AS bpool_visible_MB FROM sys.dm_os_sys_info(nolock)