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
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings