High CPU on SCOM SQL Database

Dashboard Server
,
1

Hi

 

I’m experiencing high cpu usage on the sql server hosting the Operations database.

The query that is causing it is:

SELECT [ManagedEntityGenericView].[Id],
[ManagedEntityGenericView].[Name],
[ManagedEntityGenericView].[Path],
[ManagedEntityGenericView].[FullName],
[ManagedEntityGenericView].[DisplayName],
[ManagedEntityGenericView].[IsManaged],
[ManagedEntityGenericView].[IsDeleted],
[ManagedEntityGenericView].[LastModified],
[ManagedEntityGenericView].[TypedManagedEntityId],
[ManagedEntityGenericView].[MonitoringClassId],
[ManagedEntityGenericView].[TypedMonitoringObjectIsDeleted],
[ManagedEntityGenericView].[HealthState],
[ManagedEntityGenericView].[StateLastModified],
[ManagedEntityGenericView].[IsAvailable],
[ManagedEntityGenericView].[AvailabilityLastModified],
[ManagedEntityGenericView].[InMaintenanceMode],
[ManagedEntityGenericView].[MaintenanceModeLastModified],
NULL AS SourceEntityId,
[ManagedEntityGenericView].[TimeAdded],
[ManagedEntityGenericView].[LastModifiedBy]
FROM dbo.ManagedEntityGenericView
INNER JOIN (
SELECT DISTINCT [BaseManagedEntityId]
FROM dbo.[TypedManagedEntity] TME WITH(NOLOCK)
JOIN [dbo].[DerivedManagedTypes] DT ON DT.[DerivedTypeId] = TME.[ManagedTypeId]
WHERE DT.[BaseTypeId] =@ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived AND TME.IsDeleted = 0
) AS ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived
ON ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived.[BaseManagedEntityId] = ManagedEntityGenericView.[Id] OPTION (OPTIMIZE FOR(@ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived = NULL))

 

 

Does anyone else have this as the top resource consuming query ? And can I optimize or avoid it in any way ?

 

Nick

2

Hello Nick,

Have you find a solution for this issue?
If so, what was the solution?

Kind Regards,

JM

3

Hello,

I know this topic is old, but we also have this same issue, on SCOM 2016 with SquaredUp 4.8.1.
This seems to be caused by SquaredUp, doing an issreset on both of our webservers brings the CPU back to normal for a while.
Does anyone have any information on this?

Regards,
Markus