Support Article
Poor performance with large PR_SYS_QUEUE_SLA table
SA-16797
Summary
PEGA0005 (Query time exceeds limit) [1] alerts with kpiValues of around 20 minutes is observed.
The alert is triggered by the ‘sppr_sys_reservequeueitem_b’ Stored Procedure.
The ‘PR_SYS_QUEUE_SLA’ table contains around one million rows.
Error Messages
2015-08-30 04:03:11,728 GMT*6*PEGA0005*1102123*500*cdb5138dff3c67eb0da52576d2bd7863*BF214E7B27119B808FBA32F591CE7369B*System*XXX-XX-XX-Work*xxxxx:01.01.01*cb02d787eeb0e0c9a14cdafbac486c1f*N*0*1177*WorkManager.PRPCWorkManager : 40*STANDARD*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl**System-Queue-ServiceLevel.ProcessEvent*pxRDBIOCount=4;pxTotalReqCPU=0.00;pxRDBRowWithoutStreamCount=3;pxAlertCount=1;pxRDBIOElapsed=1,102.13;pxTotalReqTime=1,102.13;pxRDBWithoutStreamCount=3;*NA*NA*NA*NA*initial Executable;0 additional frames in stack;*NA*Database operation took more than the threshold of 500 ms: 1,102,123 ms
Steps to Reproduce
Generate 1 million or more items in the PR_SYS_QUEUE_SLA then trigger execution of the sppr_sys_reservequeueitem_b stored procedure for one of the SLAs.
Root Cause
Refer to http://pdn.pega.com/node/1467. The PR_SYS_QUEUES table is not designed to be used for high data volumes.
Resolution
The following 2 indexes improve performance of SLA processing:
CREATE INDEX pr_sys_queue_tidx2 ON pr_sys_queues (pyitemid, pyitemstatus, pxprocessingnodeid);
CREATE INDEX pr_sys_queue_sla_tidx2 ON pr_sys_queue_sla (pyitemid, pyitemstatus, pxprocessingnodeid);
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.