Come on 64bit so we can leave the mem….

  • Article

I recently saw a question the other day about some errors that indicate memory pressure for the SQL Server Engine and how it might be related to the infamous “MemToLeave” for SQL Server. The problem with this situation was that the errors were happening on a 64bit version of SQL Server. So…I thought it made sense to make sure and dispel any questions or myths about “MemToLeave’ and its relationship to 64bit SQL Server.

Ready for the big answer?

There is no “MemToLeave” for the 64bit version of the SQL Server Engine!

Let me explain why….

This concept has to do with virtual address space (VAS) memory and not physical (or virtual memory) for the computer. We created the concept of “MemToLeave” memory for the virtual address space of a 32bit SQLSERVR.EXE because the virtual address space of a 32bit process in Windows is a limited resource. (2Gb by default, up to 3Gb with 4GT tuning options, and 4Gb in WoW64). And since the VAS is limited, the designers of the engine felt that by default the buffer pool should reserve as much of the VAS it could at startup (provided the amount of physical memory is 2Gb or higher. No sense reserving a big portion of the VAS if the amount of RAM is less than the max VAS for the process). So… since we are reserving most of the VAS for the Buffer Pool, we realized we can’t “take it all”. Why? Memory may be needed in the VAS for other “things” such as:

  • Thread Stacks – When a thread is created, reserved memory is required for a thread stack (this is why you might seen some type of error like 17189 SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. This error typically means the thread stack could not be reserved)
  • Heaps – Default Windows heap and any other heap created by a DLL
  • SQL Server Multi-Page Allocations (MPA) – The engine itself may allocate memory outside the Buffer Pool because the size required is bigger than a SQL page which is 8Kb.
  • Any other DLL that needs to use VirtualAlloc

What are the other “things” besides thread stacks and SQL MPA? Extended Stored Procedures, Linked Server Providers, COM objects, and host of other edge cases where DLLs get loaded in the process space of SQL Server.

The actual term “MemToLeave” comes from an internal variable name in the code that refers to the total amount of memory we “leave around” for these “things”.

The general algorithm at server startup calls for us to reserve this space using VirtualAlloc() and then after reserving the space for the buffer pool, we free this reservation. Now we have “left it” for these other “things” to reserve and commit memory. The general algorithm for the amount to “leave” is:

thread stack size * ‘max worker threads’ + “the value of –g startup parameter” (measured in Mb)

  • The thread stack size is platform dependent (for example 512Kb on x86 and 2Mb on x64)
  • ‘max worker threads’ when set to 0 is dynamic and based on number of CPUs. See BOL for details
  • The default for –g is 256 (which stands for 256Mb)

By now I hope you can guess that this algorithm and code are not needed for 64bit SQL Engine systems because the virtual address space for 64bit Windows applications are not as limited as with 32bit. In theory, the VAS for 64bit applications is 16 Exabytes.but in practical terms for Windows it is 8TB. (you should also note that the physical memory limit of Windows Server 2008 is 2TB so it is not even possible today to address the maximum VAS for a 64bit Windows application). Because of this, SQL Server doesn’t need to make any special VAS reservation at startup.  In fact, on 64bit SQL Server systems, the engine doesn’t reserve a large amount of memory for the buffer pool as with 32bit. So there is no need to leave any VAS space around since we don’t reserve most of it. You might be fooled on a x64 machine with 2Gb or greater if you monitor the “Virtual Bytes” of SQLSERVR.EXE right after startup. On my laptop with 3Gb of physical RAM, my virtual bytes was close to this number of 3Gb. But that is not the engine reserving space at startup. That is just the natural growth of the Buffer Pool when it allocates memory for overhead structures at startup such as SQLOS and lock manager. When we “grow” the Buffer Pool we reserve space in large, small number of blocks instead of small, larger number of blocks.

I hope this can help you and other bust the myth that “MemToLeave” exists on the x64 versions of SQL Server. It only applies to 32bit versions of SQL Server (even those 32bit versions running in Wow64).

Bob Ward
Microsoft