Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information

In my previous post I showed how DBAs can be provided with read-only access to Virtual Center for monitoring VM performance in the data-center   One thing that many administrators don’t realize or think about is that Virtual Center uses a database for storing information about the virtual data center and the most common database platform used for this purpose is SQL Server.  Virtual Center can use an existing SQL Server in the environment, or it can install SQL Server Express during setup.

As a consultant, I do a lot of reviews of SQL Servers running on VMware, and as part of that work I have to understand the underlying virtualization configuration – not just for SQL Server, but for the virtualized data center.  This includes needing information about the host configuration, other VMs running on the host, and performance metrics from the VM historical data that is stored in the vCenter database, which is named VIM_VCDB by default.

The VIM_VCDB database isn’t entirely documented, but a list of the views and their definitions can be found in the Using VirtualCenter Database Views technical note from VMware.  Using this information, and additional information collected using a server-side trace against the database while working in vCenter, I’ve compiled a series of Transact-SQL scripts for pulling information from the VIM_VCDB database in an environment.

The first time I ever demonstrated these scripts was last week during the Virtualization module of our IE3: High Availability and Disaster Recovery course in Chicago.  I’m also using these scripts in the Pluralsight course on SQL Server Virtualization that I am currently recording.  When I demonstrated these scripts last week, the first question the class asked was where can they get them.  We always provide the scripts from our demos to students, but I also promised to blog them as well this week.

Host Configuration

The VPXV_HOSTS view provides host level information and I commonly JOIN that view to the VPXV_VMS view to aggregate VM information per host as a part of a environment health check.

-- Host Configuration
SELECT
    vh.NAME AS HOST_NAME,
    HOST_MODEL,
    CPU_MODEL,
    CPU_COUNT,
    CPU_CORE_COUNT,
    CPU_HZ,
    CPU_THREAD_COUNT,
    SUM(CASE WHEN vm.POWER_STATE = N'On' THEN vm.NUM_VCPU ELSE 0 END) AS VM_VCPU_ACTIVE,
    MEM_SIZE,
    SUM(CASE WHEN vm.POWER_STATE = N'On' THEN vm.NUM_VCPU ELSE 0 END)*1./CPU_THREAD_COUNT AS THREAD_OVERCommit,
    SUM(CASE WHEN vm.POWER_STATE = N'On' THEN vm.NUM_VCPU ELSE 0 END)*1./CPU_CORE_COUNT AS CORE_OVERCommit,
    CAST(MEM_SIZE AS BIGINT)/1024/1024 AS MEM_SIZE_MB,
    SUM(CASE WHEN vm.POWER_STATE = N'On' THEN vm.MEM_SIZE_MB ELSE 0 END) AS VM_MEM_SIZE_MB,
    SUM(CASE WHEN vm.POWER_STATE = N'On' THEN vm.MEM_SIZE_MB ELSE 0 END)*1./(CAST(MEM_SIZE AS BIGINT)/1024/1024) AS MEM_OVERCommit,
    SUM(CAST(vm.MEMORY_OVERHEAD AS BIGINT)) AS VM_MEMORY_OVERHEAD,
    SUM(vm.MEM_SIZE_MB) AS VM_MEM_SIZE_MB_POTENTIAL,
    SUM(vm.NUM_VCPU) AS VM_VCPU_ALLOC_POTENTIAL,
    NIC_COUNT,
    HBA_COUNT,
    SUM(CASE WHEN vm.VMMWARE_TOOL = N'OK' THEN 1 ELSE 0 END) AS VM_TOOLS_OK,
    SUM(CASE WHEN vm.VMMWARE_TOOL = N'Old' THEN 1 ELSE 0 END) AS VM_TOOLS_OUT_OF_DATE,
    SUM(vm.NUM_VCPU) AS VM_VCPU_ALLOC
FROM dbo.VPXV_HOSTS AS vh
INNER JOIN dbo.VPXV_VMS AS vm
    ON vh.HOSTID = vm.HOSTID
GROUP BY vh.NAME, HOST_MODEL, CPU_MODEL, CPU_COUNT, CPU_CORE_COUNT, CPU_HZ,
    CPU_THREAD_COUNT, MEM_SIZE, NIC_COUNT, HBA_COUNT;

Performance Counter Information

Performance counters are maintained historically in the VIM_VCDB database at multiple roll-up levels. The VPXV_HIST_STAT_DAILY view has the daily roll-up values for each of the VMs and the VPXV_HIST_STAT_WEEKLY view has the weekly roll-up values.

Querying individual counter values and making decisions is not appropriate for all counters. An example of this is the CPU Ready counter which has to be calculated from the roll-up summation to determine if a problem actually exists (see CPU Ready Time in VMware and How to Interpret its Real Meaning). Two counters that I look at for the virtual data center and all of the VMs are CPU Ready and Memory Ballooned (vmmemctl).

CPU Ready Values

-- Daily %RDY values
SELECT
    vh.NAME AS HostName,
    vv.NAME AS GuestName,
    SAMPLE_TIME,
    SAMPLE_INTERVAL,
    (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 AS READY_PERCENT,
    CASE
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 5
                AND (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 < 10
            THEN N'WARN'
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 10 THEN N'RED'
        ELSE N'OK'
    END AS CPURDY_State,
    STAT_VALUE AS CPUReady_Summation,
    NUM_VCPU
FROM dbo.VPXV_HIST_STAT_DAILY AS vhsd
INNER JOIN dbo.VPXV_VMS AS vv
    ON vhsd.ENTITY = N'vm-'+CAST(vv.VMID AS NVARCHAR)
INNER JOIN  dbo.VPXV_HOSTS AS vh
    ON vv.HOSTID = vh.HOSTID
WHERE STAT_GROUP = N'cpu'
  AND STAT_NAME = N'ready'
  AND CASE
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 5
            AND (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 < 10
            THEN N'WARN'
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 10 THEN N'RED'
        ELSE N'OK'
        END <> N'OK'
ORDER BY CPURDY_State, READY_PERCENT DESC;

-- Weekly %RDY values
SELECT
    vh.NAME AS HostName,
    vv.NAME AS GuestName,
    SAMPLE_TIME,
    SAMPLE_INTERVAL,
    (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 AS READY_PERCENT,
    CASE
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 5
                AND (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 < 10
            THEN N'WARN'
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 10 THEN N'RED'
        ELSE N'OK'
    END AS CPURDY_State,
    STAT_VALUE AS CPUReady_Summation,
    NUM_VCPU
FROM dbo.VPXV_HIST_STAT_WEEKLY AS vhsd
INNER JOIN dbo.VPXV_VMS AS vv
    ON vhsd.ENTITY = N'vm-'+CAST(vv.VMID AS NVARCHAR)
INNER JOIN  dbo.VPXV_HOSTS AS vh
    ON vv.HOSTID = vh.HOSTID
WHERE STAT_GROUP = N'cpu'
  AND STAT_NAME = N'ready'
  AND CASE
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 5
            AND (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 < 10
            THEN N'WARN'
        WHEN (STAT_VALUE/ (vv.NUM_VCPU * SAMPLE_INTERVAL * 1000)) * 100 > 10 THEN N'RED'
        ELSE N'OK'
        END <> N'OK'
ORDER BY CPURDY_State, READY_PERCENT DESC;

Memory Ballooning Counters

-- Weekly Memory Ballooned
SELECT
    vh.NAME AS HostName,
    vv.NAME AS VMName,
    Start,
    Finish,
    tab.SAMPLE_INTERVAL,
    MIN(STAT_VALUE)/1024. AS MinBallooned_MB,
    MAX(STAT_VALUE)/1024. AS MaxBallooned_MB,
    AVG(STAT_VALUE)/1024. AS AvgBallooned_MB,
    COUNT(*) * (tab.SAMPLE_INTERVAL/60) AS MinutesBallooned
FROM dbo.VPXV_HIST_STAT_WEEKLY AS vhsd
INNER JOIN dbo.VPXV_VMS AS vv
    ON vhsd.ENTITY = N'vm-'+CAST(vv.VMID AS NVARCHAR)
INNER JOIN  dbo.VPXV_HOSTS AS vh
    ON vv.HOSTID = vh.HOSTID
CROSS JOIN (SELECT
                MIN(SAMPLE_TIME) AS Start,
                MAX(SAMPLE_TIME) AS Finish,
                SAMPLE_INTERVAL
            FROM dbo.VPXV_HIST_STAT_WEEKLY
            WHERE STAT_NAME = N'vmmemctl'
              AND STAT_VALUE > 0
            GROUP BY SAMPLE_INTERVAL) AS tab
WHERE STAT_NAME = N'vmmemctl'
  AND STAT_VALUE > 0
GROUP BY vh.Name, vv.Name, Start, Finish, tab.SAMPLE_INTERVAL
ORDER BY HostName, MinutesBallooned DESC;

-- Daily Memory Ballooned
SELECT
    vh.NAME AS HostName,
    vv.NAME AS VMName,
    Start,
    Finish,
    tab.SAMPLE_INTERVAL,
    MAX(STAT_VALUE)/1024. AS MaxBallooned_MB,
    AVG(STAT_VALUE)/1024. AS AvgBallooned_MB,
    COUNT(*) * (tab.SAMPLE_INTERVAL/60) AS MinutesBallooned
FROM dbo.VPXV_HIST_STAT_DAILY AS vhsd
INNER JOIN dbo.VPXV_VMS AS vv
    ON vhsd.ENTITY = N'vm-'+CAST(vv.VMID AS NVARCHAR)
INNER JOIN  dbo.VPXV_HOSTS AS vh
    ON vv.HOSTID = vh.HOSTID
CROSS JOIN (SELECT
                MIN(SAMPLE_TIME) AS Start,
                MAX(SAMPLE_TIME) AS Finish,
                SAMPLE_INTERVAL
            FROM dbo.VPXV_HIST_STAT_DAILY
            WHERE STAT_NAME = N'vmmemctl'
                AND STAT_VALUE > 0
            GROUP BY SAMPLE_INTERVAL) AS tab
WHERE STAT_NAME = N'vmmemctl'
  AND STAT_VALUE > 0
GROUP BY vh.Name, vv.Name, Start, Finish, tab.SAMPLE_INTERVAL
ORDER BY HostName, MinutesBallooned DESC;

Summary

The VIM_VCDB contains a lot more information than I’ve covered in this post.  These are simply examples of the types of information that you can query from the database as a part of troubleshooting problems or evaluating the configuration of a virtual data center.  One of the important things about having access to this data is to always validate that you are applying the appropriate understanding to the values being stored in the database.

15 thoughts on “Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information

  1. Jonathan,
    Thanks for this post. I’ve started working with the Host_Configuration script you’ve provided. Can you explain the difference between these fields?

    SUM(vm.NUM_VCPU) AS VM_VCPU_ALLOC_POTENTIAL

    and

    SUM(vm.NUM_VCPU) AS VM_VCPU_ALLOC

    1. Hey Ed,

      There actually doesn’t appear to be a difference, but the intent was for one to be the number of vCPUs across all VMs even if they aren’t running vs the active running vCPU count. I’ll have to update the script and repost it to reflect those changes. Thanks for pointing this out.

      Jon

  2. This will give the Datastore a VMK guest is using

    USE VCDB;

    SELECT vv.NAME
    — ,vvd.[VM_ID]
    — ,vvd.[DS_ID]
    ,vd.NAME as DSName
    ,vd.STORAGE_URL
    ,((CONVERT(Bigint,vd.[CAPACITY]))/(1024*1024*1024)) as DataStoreCapacityGB
    ,((CONVERT(Bigint,vd.FREE_SPACE))/(1024*1024*1024)) as DataStoreFreeGB
    FROM
    [VCDB].[dbo].[VPXV_VM_DATASTORE] AS vvd WITH (NOLOCK,NOWAIT)
    INNER JOIN [VCDB].[dbo].[VPXV_DATASTORE] AS vd WITH (NOLOCK,NOWAIT)
    ON
    vd.[ID] = vvd.DS_ID
    INNER JOIN [VCDB].[dbo].[VPXV_VMS] AS vv WITH (NOLOCK,NOWAIT)
    ON
    vv.VMID = vvd.[VM_ID]
    ORDER BY
    vv.NAME, vd.NAME

  3. This will give information on VM guests configuration

    USE VCDB;

    SELECT DISTINCT
    VPG.[VMID]
    , VPG.[NAME]
    –, VPG.[VMGROUPID]
    , VMG.[NAME]
    , VPH.[NAME]
    –, VPG.[HOSTID]
    –, VPG.[CONFIGFILENAME]
    –, VPG.[VMUNIQUEID]
    –, VPG.[RESOURCE_GROUP_ID]
    , VPG.[MEM_SIZE_MB]
    , VPG.[NUM_VCPU]
    , VPG.[BOOT_TIME]
    , VPG.[SUSPEND_TIME]
    , VPG.[POWER_STATE]
    , ISNULL(VPG.[GUEST_OS],”) AS GUEST_OS
    , ISNULL(VPG.[GUEST_FAMILY],”) AS GUEST_FAMILY
    , VPG.[GUEST_STATE]
    –, VPG.[MEMORY_RESERVATION]
    , VPG.[MEMORY_OVERHEAD]
    –, VPG.[CPU_RESERVATION]
    , ISNULL(VPG.[DNS_NAME],”) AS DNS_NAME
    , ISNULL(VPG.[IP_ADDRESS],”) AS IP_ADDRESS
    , VPG.[VMMWARE_TOOL]
    , VPG.[TOOLS_VERSION]
    , VPG.[NUM_NIC]
    , VPG.[NUM_DISK]
    –,VPG.[IS_TEMPLATE]
    , VPG.[DESCRIPTION]
    , VPG.[ANNOTATION]
    , VPG.[SUSPEND_INTERVAL]
    , CAST((VPG.[AGGR_COMMITED_STORAGE_SPACE]/(1024*1024*1024)) AS DECIMAL(10,2)) AS AGGR_COMMITED_STORAGE_SPACE_GB
    , CAST((VPG.[AGGR_UNCOMMITED_STORAGE_SPACE]/(1024*1024*1024)) AS DECIMAL(10,2)) AS AGGR_UNCOMMITED_STORAGE_SPACE_GB
    , CAST((VPG.[AGGR_UNSHARED_STORAGE_SPACE]/(1024*1024*1024)) AS DECIMAL(10,2)) AS AGGR_UNSHARED_STORAGE_SPACE_GB
    , VPG.[STORAGE_SPACE_UPDATED_TIME]
    , VDS.[NAME] AS DATASTORE_NAME
    , (CAST(VDS.[CAPACITY] AS BIGINT)/(1024*1024*1024)) AS DATASTORE_CAPACITY_GB
    , (CAST(VDS.[FREE_SPACE] AS BIGINT)/(1024*1024*1024)) AS DATASTORE_FREE_SPACE_GB
    , VDS.[TYPE] AS DATASTORE_TYPE
    , CASE
    WHEN VRDM.[DEVICE_BACKING_LUN_UUID] IS NULL THEN ‘N’
    ELSE ‘Y’
    END AS HAS_RDM
    —-, VSL.[SCSI_LEVEL]
    –, VSL.[DISPLAY_NAME]
    —-, VSL.
    –, VSL.[VSTORAGE_SUPPORT]

    FROM
    [VCDB].[dbo].[VPXV_VMS] AS VPG WITH (NOLOCK,NOWAIT)
    JOIN [VCDB].[dbo].[VPXV_HOSTS] AS VPH WITH (NOLOCK,NOWAIT)
    ON VPH.HOSTID = VPG.HOSTID
    JOIN [VCDB].[dbo].[VPXV_VMGROUPS] AS VMG WITH (NOLOCK,NOWAIT)
    ON VMG.VMGROUPID = VPG.VMGROUPID
    JOIN [VCDB].[dbo].[VPXV_VM_DATASTORE] AS VVDS WITH (NOLOCK,NOWAIT)
    ON VVDS.[VM_ID] = VPG.[VMID]
    JOIN [VCDB].[dbo].[VPXV_DATASTORE] AS VDS WITH (NOLOCK,NOWAIT)
    ON VDS.[ID] = VVDS.DS_ID
    JOIN [VCDB].[dbo].[VPXV_VM_RDM_LUN_INFO] AS VRDM WITH (NOLOCK,NOWAIT)
    ON VRDM.[VM_ID] = VPG.[VMID]
    –LEFT OUTER JOIN [VCDB].[dbo].[VPX_SCSI_LUN] AS VSL WITH (NOLOCK,NOWAIT)
    — ON VRDM.[DEVICE_BACKING_LUN_UUID] = VSL.UUID

    –WHERE VPG.[IP_ADDRESS] = ‘10.0.20.210’
    ORDER BY
    VPG.[NAME]
    ;

  4. This will give guest disk information

    USE VCDB

    SELECT
    –vgd.[VM_ID]
    REPLACE((UPPER(vv.[DNS_NAME])),’.CCHCS.LDAP’,”) AS DNS_NAME
    ,vv.[IP_ADDRESS]
    , SYSDATETIME() as [CollTime]
    ,vgd.[PATH]
    — ,[CAPACITY]
    ,((CONVERT(BIGINT,vgd.[CAPACITY]))/(1024*1024*1024)) AS CapacityGB
    — ,[FREE_SPACE]
    ,((CONVERT(BIGINT,vgd.[FREE_SPACE]))/(1024*1024*1024)) AS FreeGB
    ,CONVERT(DECIMAL(5,1),(CONVERT(DECIMAL(16,0),vgd.[FREE_SPACE]))/(CONVERT(DECIMAL(16,0),vgd.[CAPACITY]))*100) AS Pct_Free
    FROM
    [VCDB].[dbo].[VPX_GUEST_DISK] AS vgd WITH (NOLOCK, NOWAIT)
    INNER JOIN [VCDB].[dbo].[VPX_VM] AS VV WITH (NOLOCK, NOWAIT)
    ON vv.[ID]=vgd.[VM_ID]
    ORDER BY
    –Pct_Free
    [PATH]

  5. I am trying to remove a dead Cluster in my vCenter 4.1 infrastructure datacenter. I know the ID from executing a select statement against the VPX_ENTITY table. What delete statements do I need to execute?

    1. Hey Tony,

      I wouldn’t recommend manually deleting objects from the database and have never done so. There is a big difference between querying information for easier reporting across the infrastructure and trying to delete data from the database.

  6. Can anyone suggest the best way to get VM CPU and memory consumption.

    I accessed the Daily/weekly/Monthly/yearly history stats and look for stat_name ‘usage’ and ‘consumed’. According to documentation
    • Consumed = Amount of guest physical memory consumed by the virtual machine for guest memory. Consumed memory does not include overhead memory. It includes shared memory and memory that might be reserved, but not actually used.
    • Usage = Memory usage as percentage of total configured or available memory. active ÷ virtual machine configured size

    While ‘consumed’ stat_value is consistent , ‘usage’ stat_value seems completely inconsistent — see below:
    SAMPLE_TIME STAT_ID STAT_NAME STAT_GROUP STAT_ROLLUP_TYPE ENTITY STAT_VALUE
    3/25/14 0:00 16 usage mem average vm-131 940
    3/25/14 0:00 90 consumed mem average vm-1312 4192903
    3/25/14 0:00 16 usage mem average vm-1316 196
    3/25/14 0:00 90 consumed mem average vm-1316 2094434

    the numbers 940 and 196 cannot be right if these are to be interpreted as percentage.

    Any ideas?

    Thanks in advance for any help.

    ~gSinha

    1. Hey Gautam,

      Unfortunately I can’t make suggestions here, your best bet would be contacting VMware support if the documentation is incorrectly reflecting information, or you can’t derive the expected values from the tables based on the documentation. I have only worked with a subset of the counters and can’t comment on all of the different ones that are available.

  7. And this will give you what VM is on what host – good for finding SQL VM not on the SQL clusters – Enjoy

    select VV.name AS [Server Name], VCR.name AS [Cluster Name], VH.name as [Host Name], VV.DNS_name, vv.IP_ADDRESS, VH.hostid, VH.FARMID –, *
    from vpxv_vms VV inner join
    VPXV_HOSTS VH on
    VV.hostid = VH.hostid
    inner Join VPXV_COMPUTE_RESOURCE VCR
    on VH.farmid = VCR.RESOURCEPOOLID
    –where VCR.name like ‘%SQL%’
    order by VCR.name, VV.name

  8. Hi Jonathan,

    Thanks for you post, it´s intersting, i try to get performance of only the host for example the one day, how i get information, maybe can you help me ?

  9. This is great info, thanks. Do you have any idea how to query VCDB for VCenter triggered alarms? I can find amazingly little information on this. It seems like many would want to know how to script this rather than checking the vCenter client GUI every day.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Bitten by SSD Bit Rot

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.