共用方式為

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

  • 發行項

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

Comments

  • Anonymous
    January 24, 2010
    100125SLHFF Timberland is leader in the design, engineering and marketing of premium-quality footwear, apparel and accessories for consumers who value the outdoors and their time in it. Timberland roll top  products are sold worldwide through leading department and specialty stores as well as Timberland retail stores. Timberland 6 Inch boots dedication to making mens timberland custom quality products is matched by the company commitment to doing well and doing good, mens timberland chukka  is very comfortable. Forging powerful partnerships among employees, consumers and service partners to transform the communities in which they live and work. http://www.timberlandsou.com/

  • Anonymous
    April 15, 2010
    私人侦探，话费查询，财产调查，钻石行为全国各地的客户提供钻戒，钻石戒指，结婚钻戒，结婚戒指，订婚钻戒，钻石戒指价格，结婚钻戒价格刘嘉玲结婚钻戒，结婚钻戒网，结婚钻戒图片，结婚钻戒...的的<a href="http://www.51ggs.com/zhizuo.htm"> T恤衫</ 1>私家侦探，商标侵权上海私家侦探。 。Ŧ恤好在哪里？它舒适，随意，Ŧ恤能够让人尽情地表达个性，Ŧ恤还有一个最大Ŧ恤印制质量稳定，Ŧ恤图案效果<1的的HREF =“http://www 。hztcdl.cn /“>杭州搬家公司</ 1>。婚姻调查 。网站建设资讯，教程基地。下设站长新闻，建站工具与应用，谷歌系统与安全，网页设计排名，网站开发的上海的<a href="http://www.hztqjd.cn/">私家侦探</ 1>私人侦探，话费查询，财产调查，钻石行为全国各地的客户提供钻戒，钻石戒指，结婚钻戒，结婚戒指，订婚钻戒，钻石戒指价格，结婚钻戒价格刘嘉玲结婚钻戒，结婚钻戒网，结婚钻戒图片，结婚钻戒。 ！。的的<a href="http://www.kingzo.com/">钻石</">http://www.kingzo.com/">钻石</ 1>。私家侦探，商标侵权上海私家侦探 钻石行为全国各地的客户提供钻戒，钻石戒指，结婚钻戒，结婚戒指，订婚钻戒，钻石戒指价格，结婚钻戒图片，结婚钻戒，钻戒的<1的HREF =“http://www.kingzo.com/” > </ 1>！ <br/>，请的<a href="http://www.hzgolden.cn/">杭州私人侦探</ 1>私人侦探，话费查询，财产调查，钻石行为全国各地的客户提供钻戒，钻石戒指，结婚钻戒，结婚戒指，订婚钻戒，钻石戒指价格，结婚钻戒价格刘嘉玲结婚钻戒，结婚钻戒网，结婚钻戒图片，结婚钻戒。 私家侦探，商标侵权上海私家侦探私家侦探，商标侵权上海私家侦探的的<a href="http://www.hztqjd.cn/">上海私人侦探</一> ...钻石行为全国各地的客户提供钻戒，钻石戒指，结婚钻戒，结婚戒指，订婚钻戒，钻石戒指价格，结婚钻戒图片，结婚钻戒 ...！的的<a href="http://www.hztdjp.cn/">杭州特价机票</ 1>私家侦探，商标侵权上海私家侦探...

  • Anonymous
    October 22, 2010
    Look here! <a=”vipwindow.ru”>пластиковые окна Петрозаводск</a>

  • Anonymous
    August 02, 2011
    Thanks Bob, Great information... so if we get the famous SQL server 32-bit error "WARNING:  Failed to reserve contiguous memory of Size=" in 64-bit platform, does it mean bpool pressure? this used to be an indication of MemtoLeave pressure.. Appreciate your comments.

  • Anonymous
    April 05, 2015
    This shows that a 64 bit system can 32 bit processes up to the limit of its memory, but it does not indicate the individual 32 bit processes can ACCESS memory beyond the 4GB allocated to it. You have just demonstrated that the 64 bit operating system can map 32 bit processes to any area of physical memory within its address space, which is to be expected. Each of those 32 bit processes is STILL limited to 4G of physical memory, as indicated by your status display.