Answered by:
CRM 2011 - Query execution time of 30.0 seconds exceeded the threshold of 10 seconds warning on CRM server

Question
-
Our CRM server was throwing these warnings this morning and the client expressed interest as to the cause and how to proceed with troubleshooting. From what I've researched this may be caused by a plugin or a workflow in a stuck state:
Warning 1:
Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 85; Database: ourdatabase_MSCRM; Server:RHEA; 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 WITH (READPAST,READCOMMITTEDLOCK,UPDLOCK)
set
ModifiedOn = '02/20/2013 13:41:04',
ModifiedBy = CreatedBy
where
StateCode = 2
and HostId = 'server.MSCRMAsyncService.737ea3a3-ca4d-4500-a0b0-f4b94f00e442'
EXEC @res = sp_releaseapplock
@Resource = 'AsyncService_app_lock'
END
COMMIT.Warning 2:
Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 8; Database: ourdatabase_MSCRM; Server:RHEA; Query: select
"systemuser0".QueueId as "queueid"
, "systemuser0".CreatedBy as "createdby"
, "systemuser0".PreferredAddressCode as "preferredaddresscode"
, "systemuser0".NickName as "nickname"
, "systemuser0".InternalEMailAddress as "internalemailaddress"
, "systemuser0".PreferredPhoneCode as "preferredphonecode"
, "systemuser0".Title as "title"
, "systemuser0".SetupUser as "setupuser"
, "systemuser0".EmployeeId as "employeeid"
, "systemuser0".TerritoryId as "territoryid"
, "systemuser0".JobTitle as "jobtitle"
, "systemuser0".CalendarId as "calendarid"
, "systemuser0".IsIntegrationUser as "isintegrationuser"
, "systemuser0".WindowsLiveID as "windowsliveid"
, "systemuser0".PersonalEMailAddress as "personalemailaddress"
, "systemuser0".CreatedOnBehalfBy as "createdonbehalfby"
, "systemuser0".YomiFirstName as "yomifirstname"
, "systemuser0".BusinessUnitId as "businessunitid"
, "systemuser0".ExchangeRate as "exchangerate"
, "systemuser0".YomiMiddleName as "yomimiddlename"
, "systemuser0".DefaultFiltersPopulated as "defaultfilterspopulated"
, "systemuser0".SystemUserId as "systemuserid"
, "systemuser0".AccessMode as "accessmode"
, "systemuser0".YomiLastName as "yomilastname"
, "systemuser0".CALType as "caltype"
, "systemuser0".LastName as "lastname"
, "systemuser0".MiddleName as "middlename"
, "systemuser0".FirstName as "firstname"
, "systemuser0".YomiFullName as "yomifullname"
, "systemuser0".PhotoUrl as "photourl"
, "systemuser0".SiteId as "siteid"
, "systemuser0".TransactionCurrencyId as "transactioncurrencyid"
, "systemuser0".IsDisabled as "isdisabled"
, "systemuser0".DomainName as "domainname"
, "systemuser0".EmailRouterAccessApproval as "emailrouteraccessapproval"
, "systemuser0".ParentSystemUserId as "parentsystemuserid"
, "systemuser0".FullName as "fullname"
, "systemuser0".Salutation as "salutation"
, "systemuser0".MobilePhone as "mobilephone"
, "systemuser0".PassportHi as "passporthi"
, "systemuser0".CreatedOn as "createdon"
, "systemuser0".InviteStatusCode as "invitestatuscode"
, "systemuser0".IsLicensed as "islicensed"
, "systemuser0".GovernmentId as "governmentid"
, "systemuser0".ModifiedOnBehalfBy as "modifiedonbehalfby"
, "systemuser0".IsSyncWithDirectory as "issyncwithdirectory"
, "systemuser0".UTCConversionTimeZoneCode as "utcconversiontimezonecode"
, "systemuser0".ModifiedOn as "modifiedon"
, "systemuser0".PassportLo as "passportlo"
, "systemuser0".DisabledReason as "disabledreason"
, "systemuser0".ModifiedBy as "modifiedby"
, "systemuser0".HomePhone as "homephone"
, "systemuser0".Skills as "skills"
, "systemuser0".OverriddenCreatedOn as "overriddencreatedon"
, "systemuser0".ImportSequenceNumber as "importsequencenumber"
, "systemuser0".OutgoingEmailDeliveryMethod as "outgoingemaildeliverymethod"
, "systemuser0".MobileAlertEMail as "mobilealertemail"
, "systemuser0".PreferredEmailCode as "preferredemailcode"
, "systemuser0".OrganizationId as "organizationid"
, "systemuser0".IncomingEmailDeliveryMethod as "incomingemaildeliverymethod"
, "systemuser0".DisplayInServiceViews as "displayinserviceviews"
, "systemuser0".TimeZoneRuleVersionNumber as "timezoneruleversionnumber"
, "systemuser0".ActiveDirectoryGuid as "activedirectoryguid"
, "systemuser0".BusinessUnitIdName as "businessunitidname"
from
SystemUser as "systemuser0"
where
("systemuser0".SystemUserId = '811bc1cc-14d0-e111-9914-005056bf000b').Does anyone have any ideas? Thanks!
Wednesday, February 20, 2013 3:19 PM
Answers
-
In general this is just an indication for an area that could be improved - indexes added to the database for example.
Have a look at these links:
https://community.dynamics.com/product/crm/f/117/p/64580/148927.aspx
http://www.dynamicscrmpros.com/2012/09/microsoft-dynamics-crm-2011-performance-tuning-part-2/
Jason Lattimer
My Blog - Follow me on Twitter - LinkedIn- Marked as answer by wikky2007 Wednesday, March 6, 2013 1:16 PM
Wednesday, February 20, 2013 3:49 PMModerator
All replies
-
In general this is just an indication for an area that could be improved - indexes added to the database for example.
Have a look at these links:
https://community.dynamics.com/product/crm/f/117/p/64580/148927.aspx
http://www.dynamicscrmpros.com/2012/09/microsoft-dynamics-crm-2011-performance-tuning-part-2/
Jason Lattimer
My Blog - Follow me on Twitter - LinkedIn- Marked as answer by wikky2007 Wednesday, March 6, 2013 1:16 PM
Wednesday, February 20, 2013 3:49 PMModerator -
We started experiencing the same issues after moving our database from SQL 2008 to SQL 2012 running in compatibility mode. We can point to the exact date and time users started coming online in volume. We have tried everything from re-indexing to defragmentation nothing helps. ANY other ideas will be greatly appreciated!!Sunday, July 26, 2015 4:23 PM