Skip to main content

Support Article

PEGA0005 Alerts in the ALERT logs

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.
Suggest Edit

Published April 30, 2016 - Updated October 8, 2020

Did you find this content helpful?

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us