none
OpsDB: updating relationships, two internal SCOM queries blocking each other RRS feed

  • Question

  • For quite come time now, we have intermittent severe performance issues in SCOM 2012R2 (we're on UR12). The issue usually arises when we deploy a dev environment in VMM (which means create about 15 VM's, with the obvious things like CPU/mem/disks and so on).

    Research has shown that 2 database queries are blocking each other by running concurrently and forcing HOLDLOCK,UPDATELOCK several times on the same table: RecursiveMembership. One is an UPDATE, the other an INSERT.  To be honest, I can't understand why nobody on the SCOM team thought of a way to prevent these queries to run concurrently - it seems like very poor thinking on the design.

    Are we the only ones using SCOM and VMM, and running into this problem? I can hardly imagine we are...
    Friday, July 21, 2017 11:56 AM

All replies

  • Hello,

    Do you mean that when you are integrating VMM with SCOM, you encounter this issue?  Would you please look into the operations manager event log to check is there any related errors or warnings. 

    Regards,

    Yan Li


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, July 24, 2017 6:45 AM
    Moderator
  • Dear Mr Li,

    In one of hours SCOM application Servers we saw the following error message;

    Also the 3 queries that caused deadlocks;

    INSERT INTO dbo.[RecursiveMembership]
                    ([ContainerEntityId], [ContainedEntityId], [Depth], [PathCount])
                SELECT DISTINCT SC.[ContainerEntityId], TC.[ContainedEntityId], SC.[Depth] + TC.[Depth] + 1, SC.[PathCount] * TC.[PathCount]
                -- What does the target contain?
                FROM dbo.[RecursiveMembership] TC WITH (HOLDLOCK,UPDLOCK)
                -- Who contains the source?
                JOIN dbo.[RecursiveMembership] SC WITH (HOLDLOCK,UPDLOCK)
                    ON SC.[ContainedEntityId] = @SourceEntityId
                WHERE NOT EXISTS (SELECT * FROM [RecursiveMembership] WITH (HOLDLOCK,UPDLOCK)
                        WHERE [ContainerEntityId] = SC.[ContainerEntityId] AND
                            [ContainedEntityId] = TC.[ContainedEntityId])
                AND TC.[ContainerEntityId] = @TargetEntityId;
                 
        
        
               <?query --
    UPDATE [dbo].[DiscoverySource]
        SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]
        WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'
     
     INSERT #ContainerManagedEntity (
               ContainerRowId
              ,ContainerManagedEntityRowId
              ,ContainedManagedEntityRowId
              ,[Level]
            )
            SELECT DISTINCT
               c.ContainerRowId
              ,c.ContainerManagedEntityRowId
              ,r.TargetManagedEntityRowId
              ,@Level
            FROM #ContainerManagedEntity c
                  JOIN CS.HostingRelationship r ON (r.SourceManagedEntityRowId = c.ContainedManagedEntityRowId)
            WHERE (c.[Level] = @Level - 1)
              AND (r.DeletedInd = 0)
         

    An exception was thrown while processing GetDataWarehouseStoredProcedureResult for session ID uuid:5846e064-e6b9-42cf-9ddb-c410b7447266;id=82385.

    Exception message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Duplicate key was ignored.

    Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData

    Full Exception: Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.SqlTimeoutException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Duplicate key was ignored.

    Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Duplicate key was ignored. ---> System.ComponentModel.Win32Exception: The wait operation timed out

       --- End of inner exception stack trace ---

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

       at System.Data.SqlClient.SqlDataReader.get_MetaData()

       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

       at System.Data.SqlClient.SqlCommand.ExecuteReader()

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.DataWarehouseSqlCommandExecutionEngine.ExecuteReader(SqlCommand command)

       --- End of inner exception stack trace ---

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.DataWarehouseSqlCommandExecutionEngine.ExecuteReader(SqlCommand command)

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.DwProcedureCommand.Execute()

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.DataWarehouseSingleCommand.ExecutionStep.Execute(DataWarehouseCommandExecutionContext context)

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.ConnectionOpenCloseExecutionStep.Execute(DataWarehouseCommandExecutionContext context)

       at Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.ConnectionLifetimeManagementExecutionStep.Execute(DataWarehouseCommandExecutionContext context)

       at Microsoft.EnterpriseManagement.ServiceDataLayer.MonitoringDataWarehouseService.GetDataWarehouseStoredProcedureResult(String storedProcedureName, StoredProcedureParameterCollection param, IEnumerable`1 columnFilter, Int32 timeout)

    Thursday, July 27, 2017 9:57 AM
  • I am also having same issue after integration with SCVMM and if we remove the connector all works fine without any issue.
    Thursday, December 20, 2018 3:03 PM
  • Hi,

    Did you ever find a resolution for this I am seeing the same behaviour with SCOM 2019.

    Thanks,

    Emma

    Thursday, November 14, 2019 3:42 PM