SQL Query to check Vmware cpu and memory overcommit

08 March,2017 by Jack Vamvas

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 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Query to check Vmware cpu and memory overcommit


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer