Memory Counters to Check on Virtual Machine Running SQL Server


These days it is common to see SQL Server instances running on virtual machine. There would be multiple virtual machine guest systems running on one host server. To have more clarity, lets look at some terms.
Host – This is the server which runs on top of the hardware and manages and allocates all server hardware to all virtual machines.
Guest – This is the virtual machine which has been allocated few hardware resources(CPU, Memory, Storage) from the available pool of resources on the server.

As one virtual machine host system can create and host multiple guest virtual machine systems, all these guests will share the available CPU, Memory and Storage on the server. As a DBA, one of the responsibility is to ensure that the SQL Server instances running the virtual machine guest does not face any performance problems, but due to many configuration settings at the host and guest level, it is possible that all hardware resources allocated the the virtual machine guest system cannot be fully utilized by the SQL Server, instead the host may apply some further limits and does not allow the SQL Server to use the resources allocated to it. Sometimes, when the other applications on another guest system consumes more resources, it is possible that remaining guest systems on the same host may be impacted.

Memory is one of the important resource to ensure high performance, as reading data from memory is faster compared to reading from disk drives. Out guest system may be allocated certain amount of memory and DBAs may have set certain max server memory cap for SQL Server instance, but it is possible that SQL Server could never be allowed to use the allocated memory or is forced to release the memory allocated to it, so it is important to monitor if any memory is being asked to be released from the SQL Server. In general, DBAs monitor general memory counters like Server: Available Memory, Buffer Manager: Buffer cache hit ratio, Page Life Expectancy, Memory grants pending, Memory grants outstanding, free pages, total server memory, target server memory, etc. However there are also some memory counters to be monitored on virtual machine which include below.

Memory Limit(MB) – Amount of memory allocated to the guest.
Memory Reservation(MB) – This is the lowest amount of memory the guest will have. Although this is 100% of the guest’s memory, but that’s not always true. For SQL Server, generally the reservations is expected at 75% of the guest memory allocated.
Memory Ballooned(MB) – Memory released from the guest by Balloon driver.
Memory Swapped(MB) – Current amount of guest physical memory swapped out to the virtual machine’s swap file by the VMkernel. Swapped memory stays on disk until the virtual machine needs it.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


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.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.