-
Title
Performance Investigator (SQL PI) retention policy, growth and purging data -
Description
- Is it possible to change the retention policy of Performance Investigator (SQL PI)?
- Is there a way to monitor SQL PI repository growth?
- Can SQL PI repository database data be purged?
-
Cause
SQL PI maintains its own Repository database that keeps the collection information gathered when monitoring the instance/s. The Retention Policy is the same for both SQL Server and Oracle PI, and cannot be controlled by the user. -
Resolution
When PI is not enabled, what is the retention policy for database agent metrics?
When PI is not enabled, the database agent metrics are stored in the FMS repository, which follows the policy in the FMS.
In this example, for Blocking History, the agent is 3 days.
What is the retention policy for SQL PI database agent metrics when PI is enabled?
When Pi is enabled, PI metrics are sampled by PI engine and stored in the PI repository, so it does not follow the FMS policy.
In this way the retention policy for blocking history metrics (as an example) should follow this table:
Granularity Age 1 minute 6 hours 15 minutes 3 days 1 hour 2 weeks 6 hours 30 days 1 day 90 days 1 week 2 years So theoretically the oldest data could be kept in repository for 2 years, then purged by our manager agent
Is it possible to change the retention policy of SQL Server Performance Investigator and Oracle Performance Investigator (SQL PI)?On Foglight for Databases releases earlier than version 7.1.5, it is not possible to change the retention policy of SQL PI.
On Foglight for Databases version 7.1.5, a new feature was included that allows to reduce the retention policy as explained in KB 4376337.
Is it possible to purge data from the SQL PI repository?It is not possible to purge data from the SQL PI repository from the Foglight FMS UI.
Data can be manually removed from the PI repository based on the instance ID (i.e. agents) and dates however this process is not supported. Professional Services (PSO) may be able to assist with this process.
Is there a way to monitor SQL PI repository growth & can SQL PI repository database data be purged?There is no out-of-the-box monitoring of the PI repository. Options may include using a SQL Server agent (this may require additional license costs).