locked
Facing performance slow issue due to AsyncOperationBase table. RRS feed

  • General discussion

  • facing below warning messages in event log:

    Query execution time of 36.4 seconds exceeded the threshold of 30 seconds. Thread: 7; Database: iym_MSCRM; Server:crm-db; Query: 
    if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)
    begin
    update AsyncOperationBase
    set
    StateCode = 0,
    StatusCode = 0,
    ModifiedOn = '12/24/2014 06:01:39',
    ModifiedBy = CreatedBy
    where
    StateCode = 1
    and RetryCount < 10
    and AsyncOperationId in (select AsyncOperationId from WorkflowWaitSubscriptionBase where IsModified = 1)
    end.

    Query execution time of 101.7 seconds exceeded the threshold of 30 seconds. Thread: 81; Database: iym_MSCRM; Server:crm-db; Query: 
    SET DEADLOCK_PRIORITY LOW
    BEGIN TRANSACTION
    DECLARE @res INT
    EXEC @res = sp_getapplock                 
    @Resource = 'AsyncService_app_lock',
    @LockMode = 'Exclusive'
    IF @res NOT IN (0, 1)
    BEGIN
    RAISERROR ( 'Unable to acquire App Lock', 16, 1 )
    END 
    ELSE
    BEGIN

    update AsyncOperationBase
    set
    HostId = 'CRM.MSCRMAsyncService.2c4a83fe-3d81-49e8-a8c0-5ffb206cf86e',
    StateCode = 2,
    StatusCode = 20,
    StartedOn = '12/24/2014 06:00:30',
    ModifiedOn = '12/24/2014 06:00:30',
    ModifiedBy = CreatedBy,
    RetryCount = case coalesce(ErrorCode, 0)
    when 0 then coalesce(RetryCount, 0)
    else coalesce(RetryCount, 0)+1
    end
    output
    INSERTED.AsyncOperationId,
    INSERTED.OperationType,
    INSERTED.Data,
    INSERTED.DependencyToken,
    INSERTED.RegardingObjectId,
    INSERTED.RegardingObjectIdName,
    INSERTED.RegardingObjectTypeCode,
    INSERTED.OwningBusinessUnit,
    INSERTED.OwningExtensionId,
    INSERTED.OwningExtensionIdName,
    INSERTED.OwningExtensionTypeCode,
    INSERTED.OwnerId,
    INSERTED.RetryCount,
    INSERTED.MessageName,
    INSERTED.RecurrencePattern,
    INSERTED.RecurrenceStartTime,
    INSERTED.CorrelationId,
    INSERTED.CorrelationUpdatedTime,
    INSERTED.Depth,
    INSERTED.PrimaryEntityType,
    INSERTED.RequestId,
    INSERTED.CreatedOn,
    INSERTED.ExecutionTimeSpan
    where
    AsyncOperationId in
    (select top(2000) AsyncOperationId from AsyncOperationBase WITH (READPAST, READCOMMITTEDLOCK, UPDLOCK) 
    where StateCode = 0 
    and OperationType IS NOT null 
    order by Sequence asc)
    EXEC @res = sp_releaseapplock 
    @Resource = 'AsyncService_app_lock'
    END    
    COMMIT.


    Wednesday, December 24, 2014 8:11 AM

All replies