Introduction to Memory configuration
Yesterday I was discussion with one of my friend on memory management and internals to expertise in sql server. So started working more on it and found very fundamental information everyone should aware of it. Memory management for MS sql server is very important. We should have a basic knowledge of memory information, how it works and how to configure to effectively usage of it. Memory is a common resource of the system.
Memory stores everything about sql server.
For 32 Bits system(x86):-
Memory has a limit of 4GB x86 (32bit 232) of VAS (Virtual Address Space) which divides as 64KB for Null blocks which stores NULL Pointers, 2GB is for USER MODE and 2 GB for Kernel Mode. This is the default behavior of the memory configuration.
We can increase the USER mode to 3GB by adding /3GB option with /USERVA at BOOT.ini file till Windows server 2003 and earlier version, for Windows server 2008 is BCDEdit.exe. This will be good for USERMODE but restricting KERNEL mode to 1GB which may impact performance somewhere.
What if we wanted to use more than 4GB of memory? For Windows to recognize more than 4GB of memory we have to enable PAE option. PAE option will increase the system to recognize more than 4GB of memory, which is up to 64GB for x86.
Now our system has more than 4GB of memory, but for MS Sql server to use more than 3GB of memory requires to enable AWE configuration option. Using AWE option Sql server will recognize that extra memory enabled by PAE. This extra memory is only good for database cache pages not procedure cache.
USER Mode of Memory is divided into two parts:
- MemToLeave (Stack Size * Max Worker Thread ) + (-g startup option)
Stack size =512k
Max worker thread = 256k
-g option =256mb (we can change this)
- Buffer Pool (USERMode (2GB) – MemToLeave).
For 64 Bits system:-
The system is having same partitions but here the system has huge capacity for the system. 2 64. so here USER MODE could be 2GB to 8TB which is very high and generally nobody uses this. And Kernel Mode uses up to 8TB, means for 64 Bit system there is no upper limit for memory. Hence no need for PAE option. But we can use AWE ability to increase the VAS is used.
WOW (Windows on windows)
System, which has 32 Bit Sql server on 64 Bit Windows system(WOW). We can have 4GB of USER Mode space. And still use AWE for extra USER Memory.
Buffer Pool Stores:
>> Sql server connection requires 32 bits of Memory
>> Lock requires 96 Bytes of memory.
>> Data Pages
>> Compile plan
>> Execution Plan
As this is my first blog exclusive on memory, will try to write some more blogs on Memory in future.
Bob Ward’s PASS Webcast.