Monitor SQL Server Memory

Symptom
--------
Sometimes, we will notice we limit the SQL Server memory by setting max server memory, but the SQLServer.exe is using more than the value which we configred in max server memory option. For example, set the max server memory 256 MB but the SQLServer process will use about 300 MB in task manager.

Resolution
------------
An instance of SQL Server has primarily 2 regions of memory, from which it performs allocations for objects.
1. Executable code (also called MemToLeave) - used for external components such as Linked Servers, OLE DB Providers, Extended Stored Procedures, OLE Automation objects (COM components)
2. Buffer Pool - The main region of memory from which most allocations are made (for data and index pages, procedure cache etc.)

The behavior you encountered of SQL Server memory is normal. The command EXEC sp_configure 'max server memory (MB)' is only used to configure the max memory of the Buffer Pool. However, the Memtoleave size if not controlled by this configuration. Therefore, sometimes you will notice the size of SQLServr.exe process will larger than the actual configuration value of max server memory. The following are examples of things that can consume memory within the SQL Server process but outside of the buffer pool:

-- Linked server queries (7MB each)
-- Extended stored procedures
-- Worker threads (255 configured by default, .5MB each)
-- COM objects
-- DBCC Pintable
-- Larger than default network packet sizes

Here is the relationship between Total Server memory, Total pages, Memtoleave and Privates bytes of sqlservr process

Total server memory = Total pages + Memtoleave (under sql control)
Privates bytes = Total server memory + Memtoleave (out of sql control)

Total Pages                   ---Buffer Pool of SQL Server
Total server memory     ---The virtual memory under sqlservr process control
Privates byes                --- The virtual memory exclusively used by sqlservr process

For example, the performon output is as below:

--------------------------

Object:
Process          sqlservr
Private Bytes  307481272.320
Working Set   299461140.480
Object:
SQLServer:Buffer Manager Total pages  32768.000
Object:
SQLServer:Memory Manager Total Server Memory (KB) 266704.000

--------------------------

Therefore, in this situation, we can see the size of Memtoleave is as below:

Memtoleave (under sql control) = Total server memory - Total pages = 266704 - 32768*8 = 4560 KB
Memtoleave (out of sql control) = Privates bytes - Total server memory = 307458048/1024 - 266704 = 33548 KB
The total size of Memtoleave = Memtoleave (under sql control) + Memtoleave (out of sql control) = 4560 + 33548 = 38108 KB

The above concepts are based on the virtual memory, which is different from physical memory. The memory usage we noticed in the task manager is the physical memory used by the sqlservr process, which is generally close to counter of working set of sqlservr process. As to a process running on windows, it access the virtual memory directly but not physical memory. The windows will help manage the mapping and relationship between physical memory and virtual memory. Therefore, in this case, we cannot compare the physical memory used by sqlservr.exe with the above virtual memory concepts directly.

For more information regarding to virtual memory on windows, please refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/virtual_address_space.asp

Thanks and regards,
Michael