SA-29971

Summary

A systems administrator has observed low performance on PegaRULES standard tables PC_ASSIGN_WORKBASKET and PC_ASSIGN_WORKLIST.  

The systems administrator has requested assistance improving the performance of SQL imposed on these tables.

Error Messages

No error messages are displayed on screen; no applicable error messages were found in the Pega Logfile.  

Some PEGA0005 ALERT messages are present related to these tables.

Steps to Reproduce

Unknown.  No specific steps were found to reproduce the issue.  The issue appears as users return to their WorkLists to select WORK.

Root Cause

Low performance is attributed to poor database performance.

Resolution

Identify the standard seven INDEXes defined on both WorkBasket and WorkList tables.  These standard INDEXes are:

CREATE INDEX RefObjectWorkBasket ON pc_assign_workbasket (pxRefObjectKey ASC)
CREATE INDEX GetNextWorkFromBasket ON pc_assign_workbasket (pzInsKey ASC, pyErrorAssignment ASC, pxAssignedOperatorID ASC, pxUrgencyAssign DESC)
CREATE INDEX GetNextWorkInWorkGroup ON pc_assign_workbasket (pzInsKey ASC, pyErrorAssignment ASC, pxWorkGroup ASC, pxUrgencyAssign DESC)
CREATE INDEX BulkProcessFromBasket ON pc_assign_workbasket (pxAssignedOperatorID ASC, pxRefObjectKey ASC, pxRefQueueKey ASC)
CREATE INDEX RefObjectWorkList ON pc_assign_worklist (pxRefObjectKey ASC)
CREATE INDEX GetNextWorkFromList ON pc_assign_worklist (pxObjClass ASC, pxAssignedOperatorID ASC, pyErrorAssignment ASC, pxUrgencyAssign DESC)
CREATE INDEX BulkProcessFromList ON pc_assign_worklist (pxAssignedOperatorID ASC, pxRefObjectKey ASC, pxRefQueueKey ASC)

Administrator is advised to validate these INDEXes and ensure that they do exist; create them if necessary.

Also advised to RECOMPUTE STATISTICS for these INDEXes to ensure freshness. And to create standard overnight database maintenance jobs as necessary to maintain performance on these tables, as these tables are high-usage (High UPDATE/READ/WRITE/DELETE) tables.

Note: Custom Stored Procedures are also defined and are performing poorly (as per the ALERT log).  However, unable to determine if these stored procedures are in the same schema as the PegaRULES tables.  Therefore, if these custom stored procedures are in the same schema as the PegaRULES database, then the administrator must tune these stored procedures to perform better as they may be causing full table scans (FTS).