How to purge SQL PI data from the PI repository (4376337)

How to purge SQL PI data from the PI repository

Presently SQL Performance Investigator (SQL PI) data is purged after two years. A user has a requirement to delete Performance Investigator data from the PI repository that is more that 90 days old. Can this easily be done?

Enhancement Request

Workaround 

None

Status

Beginning in the 7.1.5 release of Foglight for Databases and Foglight Cloud 7.2.3 enhancement request FOG-7893 has been added to give the PI mechanism the ability to purge data that is more than X days old.

SQL PI maintains two different types of information: statistics and dimensions (like SQL text, plans etc). To control data deletion of each, there are two different parameters 

• spirmcOlapPurgeRetention → for Dim tables only
• spirmcRollupForceDeleteTime → for the PI statistics tables only

NOTE: The values of both parameters are in days

Purging dimension table data

To purge dim table data from PI, change the PI repository agent 'spirmcOlapPurgeRetention' parameter in the Agent Status Properties. The default value for this parameter is 90 days, so as an example the value could be set in the third line from the bottom to 60 days.

Purging PI statistics tables

To purge the PI statistics tables - change the PI repository agent 'spirmcRollupForceDeleteTime' parameter in the Agent Status Properties. The default value is -1 days, which means the mechanism is not active.

For example: if the spirmcRollupForceDeleteTime is set to 100 days in the second last line of the script below, and last rollup is 30.06.24 11:30AM then:

30.06.24 11:30AM - 100 days
= 22.03.2024 11:30 AM

which means that all data aggregated for time before 22.03.2024 11:30 AM will be purged.
 

agentService = server.get("AgentService");
configService = server.get("ConfigService");
def setAspParams(agents, aspName, aspValue) {
	for (agent in agents) {
		def primaryASP = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
		primaryASP.setValueByString(aspName, aspValue);		
		configService.saveConfig(primaryASP);
	}
}
def agents = agentService.findByName("<agent name>");
setAspParams(agents, "spirmcOlapPurgeRetention", "<days to keep dimension data>");
setAspParams(agents, "spirmcRollupForceDeleteTime", "<days to keep stats>");
return true;

IMPORTANT: This script should be used to maintain data for less time then the default time pyramid (refer to Knowledgebase article 4307427 for details on the time pyramid). It is not reasonable to keep data for less than three (3) days.  

• Foglight for Databases customers can run the script above in Script Console (refer to Knowledgebase article 4309796 for details on running a script using the Script Console).
• Foglight Cloud customers should contact Support to arrange for this script to be run in their Cloud environment. In the request, include the desired number of days to maintain data for the two variables spirmcOlapPurgeRetention and spirmcRollupForceDeleteTime.

 

FOG-7893