Question

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>

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

December 29, 2017 - 7:57am

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.

December 29, 2017 - 9:29am
Response to SudhakarReddy

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.