sqlserver probe: query timed out | failed to execute alarms

Products

DX Unified Infrastructure Management (Nimsoft / UIM) Unified Infrastructure Management for Mainframe CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Symptoms:

The sqlserver probe generates alarms if the query times out or a profile fails to execute in the scheduled time interval.
- Code=0x80004005 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver]Timeout expired
- query timed out! alarms

Examples:
Profile xxxxxxx, instance xxxxxxx, checkpoint 'free_space' - query timed out!
Profile <Database Server>, failed to execute in scheduled time interval, delayed by <seconds> seconds and the subkey is on "XXDB.delay_alarm"

This means that your

Customer enables a large number of QOS checkpoints on a SQL Server profile. QOS data stops appearing in OC/UMP for some or all checkpoints. Disabling most checkpoints makes QOS reappear, but the customer needs the full checkpoint set for business reasons.

Environment

DX UIM 23.4.*
sqlserver

Cause

Possible Causes:

Timeout configuration: profile is taking more time to get executed than the described heartbeat interval.

Expensive checkpoints on large instances:

The sqlserver probe processes checkpoints for a profile **serially** (one at a time - no thread/concurrency setting exists at the profile level).

If any single checkpoint is expensive on a large instance, it can consume most or all of the polling interval budget by itself, leaving no time for the remaining checkpoints to complete that round - causing them to be skipped/delayed and QOS to stop populating. Two common offenders on instances with many databases are:

- **`av_fragmentation`** — runs `sp_MSforeachdb` + `sys.dm_db_index_physical_stats(...)` across every database on the instance in one call.

and

- **`table_space`** — per-database reserved-space scan.

Either one, alone, can exceed a typical 15–30 min `sql_timeout`/interval on a ~30-database instance, hitting `SQL timeout [Nsec] reached, checkpoint skipped` on every cycle and
starving every checkpoint scheduled after it that round. This is **not** a "the probe can't handle many checkpoints" limitation, it's two specific heavy, per-database checkpoints not fitting the interval budget on large instances. All other checkpoints run cleanly once these two are handled separately.

Resolution

Review Timeouts and probe configuration:

Listed below is a complete explanation describing all of the sqlserver profile timeout fields in sqlserver profiles which can help eliminate any timeout alarms/query timeouts/failures:

1. Heartbeat - Defines the interval at which all profile checkpoint schedules will be tested and trigger eventual checkpoint execution.
This number should be a common denominator to all used check interval values.
The higher the value, the lower the profile overhead.

2. Check Interval - Default value for check interval in the profile.
This will be used if nothing else is defined in the checkpoint and overwrites the default checkpoint list setting.

3. Profile Timeout - Defines the maximum processing time for all checkpoints in the profile.
If this timeout is reached, the interval processing is finished and the probe waits for the next heartbeat to evaluate any checkpoint schedules. The alarm message is issued.

4. SQL Timeout - Every checkpoint query runs asynchronously.
In case the query reaches the SQL timeout, the checkpoint processing will be terminated and the next checkpoint will be started. An alarm is issued.

5. Delay Threshold - Timeout threshold for the profile delay alarm.

Example: If you are getting alarms for "Profile , failed to execute in the scheduled time interval, delayed by seconds", it means that your profile is taking more time to get executed then the described heartbeat interval. For example, if the profile is configured as follows:

1. Heartbeat - 60 Seconds
2. Checkinterval - 2 Mins - (Checkpoint execution)
3. Profile Timeout - 10 mins
4. delay_threshold = 15 Sec

You are getting the alarm because the profile would be getting executed say every 2 mins (1 min more than scheduled interval i.e Heartbeat), which is within the profile timeout limit, but more than "delay_threshold" limit.

- Heartbeat is set to start the profile execution
- Check interval is set for checkpoint execution
- Profile timeout is the value within which the profile execution should be completed else a new run for the profile would start
- Delay threshold is the value within which the next scheduled run of the profile should start, else an alarm will be generated

***The query timed out! alarm comes when the SQL Timeout in the profile is less than how long the query takes so increase this value to some appropriate value.***

If offending checkpoints are identified:
1. Move the heavy checkpoint(s) into a separate profile pointed at the same connection, with a longer interval (60–120 min) and matching `sql_timeout`. Keeps them out of the way of the rest of the checkpoints' normal cadence. (Preferred: no staleness impact on the other checkpoints.)
2. Raise the checkpoint's timeout and the profile's interval in place**, if a separate profile isn't wanted. Tradeoff: that checkpoint's data becomes less current.
3. Exclude the worst offending database(s)** via the checkpoint's per-DB `use_exclude`/`use_include` config, if one DB is confirmed to be the actual bottleneck rather than uniform slowness across all databases.

Additional Information

How to confirm the issue:

Grep the probe log for `checkpoint skipped`: look for a specific checkpoint name timing out every cycle at exactly the configured `sql_timeout`.
Grep for `RemoveRequest` / task-queue lines to confirm serialized (`0 in queue, 0 active`) execution - rules out a stuck/blocked scheduler as the cause.
Disable the suspect checkpoint(s) only (change nothing else) and confirm zero skips over a clean multi-hour window, cross-checked against the OC QOS report for the same window.