Question
Anthem
US
Last activity: 29 Dec 2017 9:29 EST
Recommendations to resolve SP sppr_purge_table related long running queries.
Hi Team,
Could you please help fixing the below mentioned System Cleaner agent purge activity related long running queries.
Database operation took more than the threshold of 500 ms: 35,400 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pr_log_reportstats> <pxCreateDateTime>
Database operation took more than the threshold of 500 ms: 11,427 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pr_perf_stats> <pxSnapshotTime>
Database operation took more than the threshold of 500 ms: 5,951 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pr_sys_statusdetails> <pxUpdateDateTime>
Database operation took more than the threshold of 500 ms: 3,669 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pc_events> <pxupdatedatetime>
Database operation took more than the threshold of 500 ms: 2,755 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pr4_log_rule_usage_details> <pxUpdateDateTime>
Database operation took more than the threshold of 500 ms: 2,697 ms SQL: {call DBO.sppr_purge_table (?,?,?)} inserts: <DBO.pr4_log_rule_usage> <pxUpdateDateTime>
JPMorgan Chase & Company
US
Hi ,
Please use below setting.
<env name="usage/retentionperiod " value="nnn" />
Trimming the pr_perf_stats table
In a production setting, thepr_perf_statstable can grow to contain millions of rows. By default, the system automatically purges records older than 30 days. If you do not need older rows for performance analysis, debugging, or other reporting, you can purge them by date (known as trimming) without affecting other system capabilities.
Anthem
US
Thank you for your recommendations.
The "retentionperiod " DSS setting can resolve the issue with pr_perf_stats.
Could you please provide settings/recommendations for other tables as well.