SQL Query to check Vmware cpu and memory overcommit

When reviewing ESX clusters and VM guests it’s important to understand the levels of memory and cpu commitment levels.

For example, you may have a cluster with 3 hosts   and 15 guests. Understanding the host capacity and resource requirements of the VM guests , will assist in sizing and capacity planning exercises.

This query was passed to me by a colleague – but it’s one I use regularly

use VMware_DB
go

SELECT DISTINCT
   VPH.NAME AS [HOST_NAME]
 , CPU_COUNT
 , CPU_CORE_COUNT
 , CPU_THREAD_COUNT
 , SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.NUM_VCPU ELSE 0 END) AS VM_VCPU_ACTIVE
 , CAST(SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.NUM_VCPU ELSE 0 END)*1.0/CPU_THREAD_COUNT AS DECIMAL(6,5))*100 AS Thread_OverCommitPercent
 , CAST(SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.NUM_VCPU ELSE 0 END)*1.0/CPU_CORE_COUNT AS DECIMAL(6,5))*100 AS Core_OverCommitPercent
 , CAST(MEM_SIZE AS BIGINT)/1024/1024/1024 AS MEM_SIZE_GB
 , SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.MEM_SIZE_MB ELSE 0 END)/1024 AS VM_MEM_SIZE_GB
 , (CAST(MEM_SIZE AS BIGINT)/1024/1024/1024) - (SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.MEM_SIZE_MB ELSE 0 END)/1024) AS Memory_AvailGB
 , CAST(SUM(CASE WHEN VPG.POWER_STATE = N'On' THEN VPG.MEM_SIZE_MB ELSE 0 END)*1.0/(CAST(MEM_SIZE AS BIGINT)/1024/1024) AS DECIMAL(6,5))*100 AS MEM_OVERCommitPercent
 , SUM(VPG.NUM_VCPU) AS VM_VCPU_ALLOC

FROM
 [dbo].[VPXV_VMS] AS VPG WITH (NOLOCK,NOWAIT)
  INNER JOIN [dbo].[VPXV_HOSTS] AS VPH WITH (NOLOCK,NOWAIT)
   ON
    VPH.HOSTID = VPG.HOSTID
  LEFT OUTER  JOIN [dbo].[VPXV_RESOURCE_POOL] AS VRP WITH (NOLOCK,NOWAIT)
   ON
    VRP.RESOURCEPOOLID = VPG.RESOURCE_GROUP_ID
  LEFT OUTER JOIN  [dbo].[VPXV_COMPUTE_RESOURCE] AS VCR  WITH (NOLOCK,NOWAIT)
   ON
    VCR.RESOURCEPOOLID = VRP.PARENT_ID
WHERE  VCR.NAME = 'MY_PROD_SQL_CLUSTER'

GROUP BY
   VPH.NAME
 , HOST_MODEL
 , CPU_MODEL
 , CPU_COUNT
 , CPU_CORE_COUNT
 , CPU_HZ
 , CPU_THREAD_COUNT
 , MEM_SIZE

Read More

How to find VMWare ESX Host event messages using VPXV_EVENTS 

Query VMWare vCenter VCDB database for guest and host details