Question

Load Test dead lock queries @ pc_assign_worklist.

Hi Team,

Deadlock queries has been obtained on regular basis @ pc_assign_worklist during the load test.

PFB for the list of deadlock queries.

1) Database update took more than the threshold of 500 ms: 2,422 ms SQL: delete from dbo.pc_assign_worklist where pzInsKey = ? inserts:

2) Database update took more than the threshold of 500 ms: 2,232 ms SQL: INSERT INTO dbo.pc_assign_worklist (pzInsKey , pxCommitDateTime , "ElapsedWL" , "Level" , "Market" , "MethodOfReceipt" , "ParentCaseID" , "Priority" , "ReceivedDate" , "RequestType" , "Type" , "UrgencyLevel" , "WOID" , "WOStatus" , "pxApplication" , "pxAssignedOperatorID" , "pxAssignedOrg" , "pxAssignedOrgDiv" , "pxAssignedOrgUnit" , "pxAssignedUserName" , "pxCreateDateTime" , "pxCreateOpName" , "pxCreateOperator" , "pxCreateSystemID" , "pxDeadlineExecute" , "pxDeadlineTime" , "pxFlowInsKey" , "pxFlowName" , "pxGoalExecute" , "pxGoalTime" , "pxInsName" , "pxLateTime" , "pxObjClass" , "pxRefObjectClass" , "pxRefObjectInsName" , "pxRefObjectKey" , "pxRefQueueKey" , "pxSaveDateTime" , "pxServiceLevelName" , "pxStageID" , "pxStageLabel" , "pxSystemFlow" , "pxTaskLabel" , "pxTaskName" , "pxTimeFlowStarted" , "pxUpdateDateTime" , "pxUpdateOpName" , "pxUpdateOperator" , "pxUpdateSystemID" , "pxUrgency" , "pxUrgencyAssign" , "pxWorkGroup" , "pyActionTime" , "pyAssignmentStatus" , "pyEffortActual" , "pyErrorAssignment" , "pyErrorMessage" , "pyErrorStep" , "pyFlowInError" , "pyFlowType" , "pyInstructions" , "pyInterestPageClass" , "pyLabel" , "pyProcessOnNode" , "pyResolutionCost" , pzPVStream) VALUES (? , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) inserts: > > > > > > > > > > > > 2018-12-22 19:19:12.734> > > > > > > 2018-12-22 19:19:12.739> > > > Auth Review> 2018-12-22 19:16:40.631> > > > > > 10> > > > > > > > Auth Requirements> > > [B@4f88f0d2>

3) Database operation took more than the threshold of 500 ms: 2,796 ms SQL: SELECT "PC0"."pxUrgencyAssign" AS "pxUrgencyAssign" , "PC0"."pxRefObjectInsName" AS "pxRefObjectInsName" , "PC0"."pyLabel" AS "pyLabel" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pyInstructions" AS "pyInstructions" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pxDeadlineTime" AS "pxDeadlineTime" , "PC0"."pxAssignedOperatorID" AS "pxAssignedOperatorID" , "PC0"."pxUpdateOperator" AS "pxUpdateOperator" , "PC0"."pxUpdateDateTime" AS "pxUpdateDateTime" , "PC0"."pxGoalTime" AS "pxGoalTime" , "PC0"."pxCreateOpName" AS "pxCreateOpName" , "PC0"."pxCreateDateTime" AS "pxCreateDateTime" , "PC0"."pxTaskLabel" AS "pxTaskLabel" , "PC0"."pxAssignedUserName" AS "pxAssignedUserName" , "PC0"."pxRefObjectClass" AS "pxRefObjectClass" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pzInsKey" AS "pzInsKey" FROM dbo.pc_assign_worklist "PC0" WHERE ( ( "PC0"."pxAssignedOperatorID" = ? AND ( "PC0"."pxRefObjectClass" = ? OR "PC0"."pxRefObjectClass" = ? OR "PC0"."pxRefObjectClass" = ? ) ) OR ( "PC0"."pxAssignedOperatorID" = ? AND "PC0"."pyAssignmentStatus"

Please suggest.

Comments

Keep up to date on this post and subscribe to comments

Pega
December 24, 2018 - 10:29pm

Hi 

From the queries posted I could see any deadlock error message, If you mean to say that above queries are involved in a deadlock scenario, Please refer to the article which might help you if you are MSSQL Database:

https://community.pega.com/support/support-articles/users-are-experiencing-extreme-slowness-after-pega-7-upgrade

December 26, 2018 - 3:41am

Yes, also can you work with your DBA on those SQL statements? He might be able to help you to increase performance if need be.

December 26, 2018 - 9:30am
Response to MarcLasserre_GCS

Hi,

As these are PEGA OOTB TABLES,DBA's expect recommendations from the PEGA SME's.

Thank you.

 

December 28, 2018 - 5:24am
Response to PradeepChowdaryP

Yes, we do have OOTB tables but those tables have to be controlled by DBA. Especially on a Production system, depending on your application and the use of those tables, you may have to add an index sometimes in order to increase performance.

Not too much work for a DBA to run the query on the database side and get the execution plan to give you recommendations.