Do anyone else have a problem with a SQL query in the Vcenter database eating up a lot of CPU resources?
We noticed that almost all the time one CPU core is 100% utilized by the SQL server, digging deeper we found that it's always the same query running over and over again:
SELECT DISTINCT
VPX_SCSI_LUN.UUID AS lunUuid, CASE VPX_HOST_BUS_ADAPTER.VPX_TYPE WHEN N'vim.host.BlockHba' THEN CAST(VPX_PSA_PATH.HOST_ID AS VARCHAR(255))
+ '-' + VPX_SCSI_LUN.UUID WHEN N'vim.host.ParallelScsiHba' THEN CAST(VPX_PSA_PATH.HOST_ID AS VARCHAR(255)) + '-' + VPX_SCSI_LUN.UUID ELSE VPX_SCSI_LUN.UUID END AS scsiVolumeEntityId,
VPX_DATASTORE.NAME AS datastore, VPXV_DS_LUN_INFO.DATASTORE_ID AS datastoreEntityId
FROM VPX_DATASTORE INNER JOIN
VPXV_DS_LUN_INFO INNER JOIN
VPX_PSA_ADAPTER INNER JOIN
VPX_PSA_PATH ON VPX_PSA_ADAPTER.HOST_ID = VPX_PSA_PATH.HOST_ID AND VPX_PSA_ADAPTER.KEY_VAL = VPX_PSA_PATH.ADAPTER_LINK_KEY INNER JOIN
VPX_PSA_DEVICE ON VPX_PSA_PATH.LUN_LINK_KEY = VPX_PSA_DEVICE.KEY_VAL AND VPX_PSA_PATH.HOST_ID = VPX_PSA_DEVICE.HOST_ID INNER JOIN
VPX_HOST_BUS_ADAPTER ON VPX_PSA_ADAPTER.HOST_ID = VPX_HOST_BUS_ADAPTER.HOST_ID AND VPX_PSA_ADAPTER.LINK_KEY = VPX_HOST_BUS_ADAPTER.KEY_VAL INNER JOIN
VPX_SCSI_LUN ON VPX_PSA_DEVICE.HOST_ID = VPX_SCSI_LUN.HOST_ID AND VPX_PSA_DEVICE.LINK_KEY = VPX_SCSI_LUN.KEY_VAL ON VPXV_DS_LUN_INFO.UUID = VPX_SCSI_LUN.UUID AND
VPXV_DS_LUN_INFO.HOST_ID = VPX_PSA_PATH.HOST_ID ON VPX_DATASTORE.ID = VPXV_DS_LUN_INFO.DATASTORE_ID
WHERE (VPXV_DS_LUN_INFO.DATASTORE_ID IS NOT NULL)
sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
52 RUNNABLE NT AUTHORITY\SYSTEM NIF-VCENTER02 . nif-vcenter02_vcdb SELECT 20273570 1436 10/20 12:38:26 Microsoft SQL Server JDBC Driver 52 0
Anyone else noticed this problem with Vcenter 5.5 Build 2183111 (Update 2) and MSSQL 2012 SP2 + CU2 ?
