SQL Server Memory Frequently Asked Question and Answers (FAQ) in Interviews Part 1
Memory in SQL Server is one of the most interesting concept. SQL Server implements its own memory architecture and management. If you are attending an interview for a senior DBA role or attending an interview with a very reputed organization who are expecting a very talented resource, then most often or not you will be asked lot of questions about the SQL Server internals and it definitely includes Memory related question. Understanding SQL Server Memory architecture is simple, yet very interesting and challenging as we dig deeper.
Below could be some of the Frequently Asked Question and Answers (FAQ) in Interviews on SQL Server Memory.
Is SQL Server Memory architecture different in x86, x64 and WoW modes?
Yes, SQL Server has different architecture for servers with x86, x64 and WoW modes of SQL Server instances, primarily because of how Virtual Address Space (VAS) is allocated by the Operating System. On x86 system, VAS is limited to 4 GB and out of this 4 GB, SQL Server can only use VAS up to 2 GB by default or 3 GB if PAE or /3 GB switch is enabled, where as in x64 systems, it is as high as 16 TB and SQL Server can use up to 8 TB of VAS.
What is Virtual Address Space (VAS)?
Virtual Address Space (VAS) is a Operating System concept, which is nothing but set of addresses which are allocated to each process running on the system. The idea behind VAS is, If processes running on system are only allowed to access the physical RAM, then very quickly the system will run out of memory, so VAS concept was employed, where each process is allocated some virtual addresses and when required, the processes can request the OS to map their virtual addresses with the physical addresses where the data is to be loaded. This way all processes get their own private virtual addresses and can get physical memory allocated as and when required. This way Operating System can manage the available physical memory efficiently across all the processes.
What are the available Virtual Address Space (VAS) on x86 and x64 systems for SQL Server?
SQL Server is also a user processes, so it gets its own private Virtual Address Space (VAS) from the Operating System. On x86 system, 4 GB of VAS is available in a system, which is divided into two regions, User mode VAS and Kernal mode VAS. Each process running on the server gets its own 2 GB of VAS and remaining 2 GB is available for the OS for its own use. We can increase the User mode VAS of a process from 2 GB to 3 GB using /3GB switch or /USERVA. so, SQL Server on x86 system is a user process, so it gets 2 GB or 3 GB or VAS depending upon the /3 GB switch. On a x64 system, VAS available 16 TB of which 8 TB is available for each process and 8 TB is available for the OS. SQL Server On x64 system, can use up to 8 TB of VAS.
What is SQL Server Memory Architecture or How is SQL Server Memory Divided?
SQL Server user VAS is divided into two regions, which are BufferPool and Memory-To-Leave (MTL).
BufferPool – BufferPool is the region of memory which is used for storing the in memory data and index pages which are <= 8 KB of size. MemToLeave (MTL) – MTL is contiguous memory which is allocated during the startup of SQL Server, which is used for SQL Server thread stacks, third party DLL’s, Extended Stored Procedures, COM objects, Memory used by linked servers, CLR, any memory allocated for requests greater than 8 KB of contiguous memory.
How are the memory sizes for BufferPool and MemToLeave (MTL)?
Size of BufferPool and MTL differs from x86 and x64 systems. On x86 system, during startup of SQL Server MTL is reserved, which is contiguous memory and by default is 384 MB on systems with less than 4 processors. More accurate way of calculating the size of MTL is as below.
MTL = (Size of Stack Size * Number of SQL Server Worked Threads) + Additional space reserved, by default 256 MB and the value specified in -g startup parameter).
– Size of stack on x86 system is 512 KB
– Number of worked threads on a server can be different based on available processors. On x86 system with <= 4 processors, there will be 256 worker threads available, but can be changed using sp_configure options as well. Refer here for more information about worked threads.
So, on x86 system with <= 4 processors and -g option not used, MTL = ((256 * 512 KB)/1025) MB + 256 MB = 384 MB. Once this 384 MB contiguous memory is allocated out of available 2 GB VAS, there will be 1.6 GB of memory remaining which will be used as BufferPool memory.
On a x64 system, SQL Server Stack Size is 2048 KB. So a x64 system with <=4 processors will have MTL = ((512 * 2048)/1024) MB + 256 MB = 1280 MB. This is contiguous memory allocated during the startup of SQL Server and then BufferPool is allocated and finally remaining VAS is left to be used for MTL.
BufferPool is also calculated, where BufferPool = Minimum(Physical memory, User address space – MTL) – BUF structures
On x86 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 2 GB – 384 MB) – 32 MB = 1632 MB ~ 1.59 GB
On x64 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 8 TB – 1280 MB) – 32 MB = 4064 MB ~ 3.96 GB
What can SQL Server MTL memory region contain?
MTL regios is used for thread stacks, third party DLL’s, COM components, Extended Stored Procedures, CLR objects loaded in SQL Server, linked server objects memory, any contiguous memory greater than 8 KB.
What can SQL Server BufferPool memory region?
SQL Server BufferPool memory region contains data or index pages which are less than or equal to 8 KB size.
What is Address Windowing Extensions (AWE) and how is it useful and how it can be used with SQL Server?
Address Windowing Extensions aka AWE is nothing but set of API’s which is used for addressing more than 4 GB of physical memory. On x86 system, SQL Server by default can only use 4 GB physical memory, so to allow SQL Server to use more than 4 GB of memory, AWE needs to be enabled. AWE for 32 bit SQL Server instance can be enabled either from Sp_Configure or from SQL Instance Properties from SSMS.
What happens on x86 system with 64 GB RAM and /3 GB switch enabled?
On 32 bit system with /3 GB switch enabled, SQL Server instance can only use a maximum of 16 GB physical memory. This is because, when /3 GB switch is enabled SQL Server process gets 3 GB of VAS, but Kernal mode VAS is reduced to 1 GB, with this 1 GB of kernal VAS, OS can only use up to 16 GB physical memory, thus SQL Server also cannot see beyond this 16 GB, so be mindful with enabling /3 GB switch.
Should we enabled AWE in x64 systems?
No, although we have the option to enable AWE on x64 systems, it has no affect. By default x64 systems can use large amounts of physical memory as it has 8 TB if user VAS, so no requirement of using AWE.
What is Lock Pages in Memory?
“Lock Pages in Memory” is Operating System policy, which allows a process to lock its data in memory and does not trim/release that memory working set under memory pressure. This is not completely true, as Operating System can trim working set of any process if required, but it will only trim the process workingset with lock pages in memory enabled at the last. Enabling this for SQL Server service account, will lock bufferPool pages in memory and will not trim the BPool unless OS is completely out of memory and all other processes memory has been already trimmed.
Should we enabled Lock Pages in Memory for SQL Server instance?
On x86 system, to use AWE, lock pages needs to be enabled.
On x64 system, if the server is dedicated SQL Server system, then it is good to enable lock pages in memory.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.