Background processes and SQL PI queries are filtered from SQL PI (4369626)

Database related background processes and SQL PI's own internal queries are filtered out by default SQL PI.

How can the background processes and PI internal queries be viewed within the PI SQL statements for SQL Server, Azure SQL, MySQL, and PostgreSQL?

Monitor using SQL Server, Azure SQL, MySQL or PostgreSQL agents and enable PI

Check the Agent Status Property parameters paecFilterDmvRequest and paecDmvCollectBackgroundProcess in the database agent log

To see the PI related background processes, paecFilterDmvRequest should be 0 and paecDmvCollectBackgroundProcess should be 1.

• filterDmvRequest disables and enables the filtering of SPI monitoring SQLs from the SQL PI ui
• paecDmvCollectBackgroundProcess disables and enables the background processes data collection within the SQL PI ui 

Note: 

Resolution 1

For SQL Server and Oracle agents, enable background process monitoring by 

1). Select the database agent in the Global View database dashboard and then clicking Settings | Administration.

2). Choose SQL Performance Investigator in the menu

3). Select the agent checkbox

4). Click the Settings button

5). Click the checkbox to Enable background processes data collection

6). Click the Set button

Resolution 2

The following groovy script run in the Script Console can be used to display the background processes for the SQL Server, Azure SQL, MySQL and PostgreSQL PI agents. Replace AGENTNAME with the name of the agent.

NOTE: A similar script is not available for Oracle agents. 

*** Foglight Cloud customers should contact Quest Support to request this functionality be enabled in their environment. 

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("AGENTNAME");
setAspParams(agents, "paecFilterDmvRequest", "false");
setAspParams(agents, "paecDmvCollectBackgroundProcess", "true");

return true;