High CPU on the monitored host caused by database agents (4226246)

When a database agent is activated the monitored the collections running cause high CPU on the monitored database instance.

When the Foglight database agent is turned off the CPU activity on the host returns to normal.

Some Queries may appear to create a performance impact on a monitored Database Instance when SQL PI is enabled.

Instances with little to no activity (e.g. newly built instances with no production databases), or during periods of low activity on a production database may exhibit high Foglight monitoring activity. 

High Foglight monitoring activity from SQL PI monitoring may appear to to using Instance resources as the collections run every 1 second/1000ms. This is normal and a default behavior.

This may not be noticed on a normally operating Database Instance with some levels of workload. 

Cause 1

Deadlock collection using Extended Events in SQL Server 

Cause 2

Foglight environment sizing or antivirus requirements are not met. 

Cause 3

SQL PI "get_active_waits" query for SQL Server monitoring

Cause 4

If a firewall kills/interrupts a database session, the database session itself does not realize that the session has gone. The database keeps the session open. Foglight will create a new session.

Cause 5 

Connectivity issue to an Oracle host

Cause 6

Database maintenance not up to date.

Cause 7

Disk performance

Cause 8

Resource contention

Cause 9

SQL Server Error Log collection OS DCOM retrieval of the SQL Server Error Log.

Resolution 1

** This is the most common solution issue SQL Server agents **
Refer to KB 4228897 with an immediate suggestion to change the Deadlocks collection in SQL Server from Extended Events to Trace.

Resolution 2

Please see the current Foglight for Databases deployment guide check the SQL PI environment sizing and requirements.

Resolution 3

Please see KB 4226229 to reduce the collection workload from SQL PI (for SQL Server hosts)

Resolution 4

Please refer to KB 4295218 for more details on this issue

Resolution 5

Please refer to KB 4295218 for more details on this issue (for Oracle hosts)

Resolution 6

Optimize table indexes, Update Statistics, run regular maintenance tasks etc to improve Instance performance.

Resolution 7

Disk performance and physical file locations should be reviewed and confirmed to not be contributing to instance performance. 

Resolution 8

Investigate Instance Memory tuning. Ensure the instance memory is not using all resources and there is enough left for base OS and other third party app operations. Other Third Party monitoring tools run in conjunction with Foglight and may be competing for available resources. 

Resolution 9

Change the SQL Server Error Log collection for the database agent from OS or FALLBACK to JDBC as described in KB 4302796