SQL Server max memory – why to cap in 64 bit environments

Hi Friends,

There is lot of questions floating around sql server max memory capping in 64 bit environments which I thought of discussing with an example so that we could have a picture as to why this setting is recommended.

As we know in Win32 memory architecture, each process has a 4 GB address space (2^32 in size or roughly speaking 4 GB). By default, 2 GB of that address space is accessible to user mode (Application like SQL Server etc.) and the remaining 2 GB is accessible to kernel mode (OS processes etc.) so in 32 Bit windows architecture 2 GB of memory is maximum available for SQL Server unless you specify /3 GB switch in boot.ini by which user mode address space becomes 3GB leaving 1 GB for kernel mode i.e. OS etc.

This isn’t the case in 64 bit environment, i.e. servers that are x64/IA64 or 64 bit have a VAS region that is 2^64 in size or roughly speaking 16 trillion GB. Since this is well beyond the physical capabilities for memory, the user mode and kernel mode VAS are capped at 8TB each for x64 based 64 bit servers. This expanded VAS changes the dynamic of how applications such as SQL Server allocate memory.

SQL Server User address space is broken into two regions: MemToLeave and Buffer Pool. Size of MemToLeave (MTL) and Buffer Pool is determined by SQL Server during start up as calculated below;

Memory to Leave a.k.a VAS = (Stack size * max worker threads) + Additional space to load Dll’s

Now, Stack size = 2048 KB per thread for 64 Bit SQL Server i.e. = (2048 KB * 256)

So, MTL = Stack size + Additional Space to load Dll’s

i.e. MTL = 524288 MB + 256 MB = 524544 MB

You can also specify VAS size by adding –g parameter in startup parameters followed by bouncing SQL services else calculation goes like above equation.

We know that buffer pool is calculated as following;

Buffer Pool = Minimum (Physical memory, (User address space – MTL a.k.a VAS)) – BUF structures

Let’s say, you have 16 GB physical memory in a 64 bit environment, so if you do not CAP Max memory Buffer Pool calculation would be like following;

Buffer Pool = Minimum(16GB, (8TB – 0)) – 0

Point to be noted here is on 64 bit SQL Servers, the VAS Reservation at startup does not occur due to the size of VAS available on 64 bit systems.

As you observed in above discussion, anyway it is always going to be total physical memory available on server that gets assigned to buffer pool and as a result there could be a situation wherein SQL may eat up all system memory leaving nothing for OS causing blue screen, unexpected server reboot etc. So, in 64 bit environment it is always recommended to CAP Max memory setting to avoid any such issues just in case.

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *