SA-22532

Summary

PEGA0005 alerts reported in Alert log file with Pega 7.1.6:

• Database operation took more than the threshold of 500 ms: 1,639 ms SQL: {call ID2XNPGA.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
• Database operation took more than the threshold of 500 ms: 1,430 ms SQL: {call ID2XNPGA.sppr_purge_table (?,?,?)}
• Database update took more than threshold of 500 ms: 652 ms SQL: update ID2XNPGA.pr_sys_statusnodes set pyLastPulseDateTime = ? where pzInsKey = ?

Error Messages

Not Applicable.
 

Steps to Reproduce

It was observed in Pega Alert log file in a Development system. 

Root Cause

The cause is related to both the composition of the stored procedure and missing indexes on various standard PegaRULES database tables.

Resolution

Perform the following local-change: 

 Install additional needed INDEXes that were not included in the original PRPC Database Schema Installation script.
 
a) Add the following Indexes:
 
CREATE  INDEX
  pr_sys_queue_sla_index1
ON
  pr_sys_queue_sla (
  pyMinimumDateTimeForProcessing ASC,
  pzInsKey ASC,
  pyItemStatus ASC,
  pyAgentName ASC,
  pyItemId ASC
);
 
CREATE INDEX
  pr_sys_workindexer_index1
ON
  pr_sys_workindexer_queue (
    pyMinimumDateTimeForProcessing ASC,
    pzInsKey ASC,
    pyItemStatus ASC,
    pxObjClass ASC,
    pyAgentName ASC
);
 
CREATE INDEX
  queuesla_update
ON
  pr_sys_queue_sla (
    pyItemId ASC,
    pyItemStatus ASC
);

CREATE INDEX
  queue_reserve
ON
  pr_sys_queues (
    pyMinimumDateTimeForProcessing ASC,
    pyItemStatus ASC
);
 
CREATE INDEX
  queuesla_reserve
ON
  pr_sys_queue_sla (
    pyMinimumDateTimeForProcessing ASC,
    pyItemStatus ASC
);
 
CREATE INDEX
  queuesvc_reserve
ON
  pr_sys_queue_execreq_svc (
    pyMinimumDateTimeForProcessing ASC, pyItemStatus ASC
);
 
CREATE INDEX
  queueconn_reserve
ON
  pr_sys_queue_execreq_conn (
    pyMinimumDateTimeForProcessing ASC,
    pyItemStatus ASC
);
 
2. After making these changes to the PegaRULES database, shutdown Process Commander, and restart.