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.
