Increasing the retention period of pr_perf_stats table
we have a requirement from the Client IT Ops team to increase the retention period of the pr_perf_stats table from the current default value of 30 days to 180 days. As far as I know this table is much like the History table for the application work entities, and it grows exponentially in case of single/multi node systems. Is there any possible impact of such a change on the eventual performance of the Database and/or the application like transaction logs getting full or system performance getting degraded?
Thanks for the quick response. Unfortunately we don't have AES installed in our current production set up so the impact with AES is discarded for now. As for the reports run against this table, the main motive is to monitor this table and generate reports against it for some performance metrics. But I think 180 days in place of the default 30 days as retention parameter for our system where we have 4 nodes active is a bit too much in terms of occupying DB space for this table.
Is there any recommended option for such changes in production based on the available hardware/memory and other parameters?
There are no specific recommendations available based on the environment (hardware / memory or other parameters). In general a large table which had inserts and deletes causes fragmentation and poor performance. De-fragmentation on a regular basis should help there. Depending on how your reports are configured, partitioning might be a good idea.