Deadlock Query Using Extended Events Causes High CPU and Memory Pressure on SQL Server Host (4228897)

When Foglight monitors a SQL Server or SQL Managed Instance, the deadlock collection using Extended Events can cause performance issues. The query runs frequently, consumes high CPU, and leads to memory pressure in the default Resource Pool. Additionally, Foglight generates multiple sessions that are killed almost immediately, contributing to instability and session churn.

Foglight’s deadlock collection reads .xel files using fn_xe_telemetry_blob_target_read_file, which:

• Runs every few minutes.
• Parses large XML blobs in memory.
• Consumes CPU for 11–25 seconds per execution.
• Allocates memory from the default Resource Pool.

When SQL Server is under resource pressure:

• Queries time out.
• Foglight kills the session and retries.
• This creates a loop of short-lived sessions (KILLED/ROLLBACK), increasing load and instability.

Other collections (e.g., Error Log) may fail due to memory exhaustion, even though they are not the root cause.

RESOLUTION 1

A new deadlock collection method has been added to the 8.1.0.20 and higher SQL Server cartridges to address issues with high cpu consumption caused by the extended events system_helth deadlock collection. This new method collects deadlocks using an extended event dedicated session created by the SQL Server agent. The dedicated session will hold only deadlock events that have a 10mb. file size with up to five rollover files. When an agent is deleted or another method is selected then the dedicated extended event session will be dropped by agent. 

Steps

1). The method can be selected via agent Agent Status Properties by choosing the "DedicatedEvents" option in the pulldown.

2). Restart the SQL Server agent after making the change

Important notes:

• The default method will remain ExtendedEvents (system_health).
• This new method does not support Azure Managed Instances..
• Regular SQL Server instances, AWS RDS, and GCP is supported.

RESOLUTION 2

Change Deadlock Working Mode to Trace in Foglight Agent Properties.
This uses the legacy SQL Trace method, which is lighter on CPU and memory.

Path:
Foglight -> Agent Status -> Edit Properties -> Deadlock Collection -> Set to Trace

Steps

1). The method can be selected via agent Agent Status Properties by choosing the "Trace" option in the pulldown.

2). Restart the SQL Server agent after making the change

Note: This is an older setting and has been used successfully on many systems in the past.

RESOLUTION 3

Move old system_health*.xel files from the SQL Server log folder to another location.
This reduces the size of blob reads and lowers resource usage.

Example Path:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log
Note: Files in use cannot be moved.