locked
Really expensive discovery insertion - how can I trace it back? RRS feed

  • Question

  • Hello again folks. 

    Weve had some pretty grim Ops DB performance over the last few days causing console slowdowns, los off data, the works.

    Our DBA has identified the following query causing the locks for several minutes at a time.

    CREATE PROCEDURE [dbo].[p_EntityTransactionLogBegin]

    (

        @DiscoverySourceId uniqueidentifier,

        @ContextGenerated nvarchar(255) = NULL,

        @TransactionId bigint = NULL OUTPUT

    )

    AS

    BEGIN

        SET NOCOUNT ON;

        DECLARE @Err int;

        DECLARE @LastModified datetime;


        UPDATE [dbo].[DiscoverySource]

        SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]

        WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'

        SELECT @Err = @@ERROR;

        IF (@Err <> 0) 

           GOTO Error_Exit;

          

        SET @LastModified = GETUTCDATE();

        INSERT INTO dbo.[EntityTransactionLog]

        (

            [DiscoverySourceId],

            [ContextGenerated],

            [LastModified],

            [TimeAdded],

            [IsCommitted]

        )

        VALUES

        (

            @DiscoverySourceId,

            @ContextGenerated,

            @LastModified,

            @LastModified,

            0

        );

        SELECT @Err = @@ERROR;

        SELECT @TransactionId = @@IDENTITY;

        IF (@Err <> 0)

            GOTO Error_Exit;

        IF OBJECT_ID('tempdb..#EntityTransaction') IS NOT NULL

        BEGIN

            INSERT #EntityTransaction

            (TransactionId)

            VALUES

            (@TransactionId);

        END

        RETURN 0;

    Error_Exit:

        RETURN 1;

    END

    GO

    This seems to be trying to insert half a million rows a time into the DB - no idea why?

    Does anyone know how I can locate that discovery ID in the SCOM DB? Ive tried get-scommonitoring object -id without success.

    Thursday, January 23, 2014 2:50 PM

Answers

  • Hi,

    My fault :)

    Execute following SQL Query:

    SELECT * FROM [SCOM].[dbo].[DiscoverySource] WHERE DiscoverySourceId = 'YourID'

    And execute the Powershell Code with the Discovery Rule ID from the Result

    Cheers

    Update:

    I think this issue the same you have.

    http://nocentdocent.wordpress.com/2012/05/25/the-road-to-operations-manager-2012-sites-and-gateways/

    See following Section there:

    Option 1 should be clear by now, to implement option 2 we need to unlink the site from the discovery rule, this way Operations Manager “knows” it must remove the managed entity (the site itself) and replicate the removal to the data warehouse. To do this we must use the p_RemoveEntityFromDiscoverySourceScope stored procedure and pass the TypedManagedEntityId and DiscoverySOurceId for our site. Incidentally the DiscoverySourceId is sdk generated so you won’t find any actual discovery rule associated (Discovery table join on DiscoveryId field), this is appears to be a constant 85AB926D-6E0F-4B36-A951-77CCD4399681, getting the TypeManagedEntityId is then just a matter of proper table joining


    Christoph Maresch | My blogs: blog.cmaresch.at | XING: Christoph Maresch | Linkedin: Christoph Maresch




    • Edited by ChristophMaresch Thursday, January 23, 2014 3:33 PM
    • Marked as answer by Niki Han Wednesday, February 5, 2014 3:01 AM
    Thursday, January 23, 2014 3:23 PM

All replies

  • Hi,

    Try following:

    Get-SCOMDiscovery -Id 85AB926D-6E0F-4B36-A951-77CCD4399681

    Cheers


    Christoph Maresch | My blogs: blog.cmaresch.at | XING: Christoph Maresch | Linkedin: Christoph Maresch

    Thursday, January 23, 2014 2:56 PM
  • I get no results I'm afraid. Is it possible that this is a temporary GUID?
    Thursday, January 23, 2014 3:08 PM
  • Hi,

    My fault :)

    Execute following SQL Query:

    SELECT * FROM [SCOM].[dbo].[DiscoverySource] WHERE DiscoverySourceId = 'YourID'

    And execute the Powershell Code with the Discovery Rule ID from the Result

    Cheers

    Update:

    I think this issue the same you have.

    http://nocentdocent.wordpress.com/2012/05/25/the-road-to-operations-manager-2012-sites-and-gateways/

    See following Section there:

    Option 1 should be clear by now, to implement option 2 we need to unlink the site from the discovery rule, this way Operations Manager “knows” it must remove the managed entity (the site itself) and replicate the removal to the data warehouse. To do this we must use the p_RemoveEntityFromDiscoverySourceScope stored procedure and pass the TypedManagedEntityId and DiscoverySOurceId for our site. Incidentally the DiscoverySourceId is sdk generated so you won’t find any actual discovery rule associated (Discovery table join on DiscoveryId field), this is appears to be a constant 85AB926D-6E0F-4B36-A951-77CCD4399681, getting the TypeManagedEntityId is then just a matter of proper table joining


    Christoph Maresch | My blogs: blog.cmaresch.at | XING: Christoph Maresch | Linkedin: Christoph Maresch




    • Edited by ChristophMaresch Thursday, January 23, 2014 3:33 PM
    • Marked as answer by Niki Han Wednesday, February 5, 2014 3:01 AM
    Thursday, January 23, 2014 3:23 PM
  • Looks complicated.............but I'll give it a try. I dont think we have an orphaned site issue, but that GUID does indeed pop up.
    Thursday, January 23, 2014 4:14 PM
  •    UPDATE [dbo].[DiscoverySource]

       SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]

       WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'

    The query updates TimeGereratedOfLastSnapshot with itself. Why?

    Same in p_EntityChangeLogSnapshot. The p_EntityChangeLogSnapshot procedure executes 21.500 times every hour, 93% of the time it takes to execute its locked. For me the update of TimeGeneratedOfLastSnapshot inside p_EntityChangeLogSnapshot seems meaningless.

    Thursday, February 20, 2014 2:08 PM
  • Hi,

    Just out of curiosity, have you installed UR5 by any chance?

    /Per

    Friday, March 20, 2015 1:18 PM
  • Hi,

    I have the same exact issue in my environment. We don't have any orphaned sites as we didn't create any.

    We are running OPSMGR 2012 R2 UR4, have not upgraded to UR 5.

    The above mentioned  sproc constantly blocks UPDATE to the OPSDB. The console becomes very very slow, even the webconsole.

    We dont have the issue in our LAB environment, which is a copy of production.

    Any feedback will be appreciated.

    Regards

     

    Monday, March 23, 2015 5:55 AM
  • I am also seeing the very same issue.  
    Monday, April 6, 2015 7:38 PM
  • I have recently upgraded to UR5 and also having the same issue here. I have currently an ongoing case with Premiere support
    Tuesday, April 7, 2015 1:04 PM
  • I am having the same issue.  Constant blocking being caused by p_EntityTransactionLogBegin.  I also see a ton of executions from p_EntityChangeLogSnapshot.

    Speaking with Microsoft support, they have no solution.

    Has anyone come up with a solution within their environment for this?

    Wednesday, June 24, 2015 3:47 PM
  • I am having the same problem, Any update on this just move to the latest CU ??

    Wednesday, April 20, 2016 12:58 AM
  • I know this is a Necro-thread but having same issue in SCOM v1801.

    No gateway servers involved in this environment.

    Did anyone ever get a resolution?

    Seeing multiple blocks against:

    UPDATE [dbo].[DiscoverySource]      
    SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]      
    WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'

    No results returned when querying that GUID.



    Wednesday, August 19, 2020 4:34 PM