locked
Performance: High Read I/O on CRM Database RRS feed

  • Question

  • Hi!

    We recognized a very high Disk Read Load every evening on our CRM Database.

    I cloud isolate the SQL-Statement which is executed in that case:

    BEGIN


    SETNOCOUNTON


    SETXACT_ABORTON


    DECLARE@RecordCountToDelete int=1

    DECLARE@ActivationsToDelete TABLE

    (


    WorkflowId

    UNIQUEIDENTIFIER


    )


    WHILE@RecordCountToDelete >0

    BEGIN


    BEGINtran


    INSERT@ActivationsToDelete

    SELECTTOP5000 WorkflowId

    FROMWorkflowBase

    WHERE


    Type=2

    ANDCategory =0

    ANDStateCode =0

    ANDWorkflowId NOTIN


    (SELECTOwningExtensionId FROMAsyncOperationBase

    WHEREOperationType =10

    ANDOwningExtensionId ISNOTNULL)


    SELECT@RecordCountToDelete =@@ROWCOUNT


    IF@RecordCountToDelete >0

    BEGIN


    DELETEFROMWorkflowDependencyBase WHEREWorkflowId IN


    (SELECTWorkflowId from@ActivationsToDelete)


    DELETEFROMDependencyNodeBase WHERE

    ComponentType

    =29 AND


    ObjectId

    IN


    (SELECTWorkflowId from@ActivationsToDelete)


    DELETEFROMWorkflowBase WHEREWorkflowId IN


    (SELECTWorkflowId from@ActivationsToDelete)


    DELETE@ActivationsToDelete

    END


    COMMITtran


    END


    END

    go

    Could somebody tell me, what the Statements above do?

    Thanx in advance!

    Greetings,

    Karsten




    • Edited by JuneauDT Monday, July 25, 2016 11:01 AM
    Monday, July 25, 2016 11:00 AM

All replies

  • This is clearing out data about completed asynchronous workflows

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, July 26, 2016 5:46 AM
    Moderator