Running SQL Server in a Virtual Environment - SQL Server 2012 Internals and Troubleshooting - page 505

is that the percentage is proportional to the total amount of the virtual server's memory. If the
hypervisor grants the virtual server more memory at any time, you would expect the percentage of
memory being used to remain constant even though SQL Server is using more memory. Likewise, if
the balloon driver begins to inl ate, you would expect the percentage of total memory SQL Server
is using to begin to drop. You could use absolute values, but monitoring a percentage takes into
account the physical memory size. The way this query works is dependant on Hyper-V's Dynamic
Memory model so it won't work in VMware based environments.
select (m.total_physical_memory_kb /1024)
as 'Physical Memory (MB)',
convert(decimal(3,1),(convert(decimal(10,1),m.available_physical_memory_kb / 1024)
/ convert(decimal(10,1),m.total_physical_memory_kb / 1024)) * 100)
as 'Available Memory as % of Physical Memory'
from sys.dm_os_sys_memory m,sys.dm_os_sys_info i
select convert(decimal(10,1),(convert(decimal(10,1),i.committed_target_kb / 1024)
/convert(decimal(10,1),m.total_physical_memory_kb / 1024))
* 100) as 'Committed Target as % of Physical Memory',
convert(decimal(3,1),(convert(decimal(10,1),i.committed_kb / 1024)
/convert(decimal(10,1),m.total_physical_memory_kb / 1024))
* 100) as 'Total Committed as % of Physical Memory'
From sys.dm_os_sys_memory m, sys.dm_os_sys_info i
Examples of output from this script run on a Hyper-V virtual server are shown in Figure 17-10 and
Figure 17-11. The i rst instance was run just after SQL Server started, the second after a heavy workload
when the hypervisor allocated additional memory to the virtual server using Dynamic Memory.
FIGURE 17-10
FIGURE 17-11