[BUG] index_object_stats collector times out (30s) as a single all‑or‑nothing cross‑database sweep - yields no index data #1135

Component

Lite

Performance Monitor Version

3.0.0

SQL Server Version

Microsoft SQL Server 2022 (RTM-CU19) (KB5054531) - 16.0.4195.2 (X64)

Windows Version

Windows Server 2019 Standard

Describe the Bug

The new index_object_stats collector (v3.0.0) runs its entire multi‑database collection as one SQL command with the hard‑coded 30‑second CommandTimeout. On a server with sizable/many databases, sys.dm_db_index_operational_stats across all indexes/partitions blows past 30s, the command fails with SQL #-2 Execution Timeout Expired, and because it's a single sweep it returns zero index data — one slow database nukes the whole result. The collector is enabled by default and runs on first connect, so it fails immediately after upgrade.

Steps to Reproduce

1. close the Lite Dashboard app
2. update to 3.0.0
3. open the Lite Dashboard app

Expected Behavior

Do collect the stats

Actual Behavior

Do not collect the stats

Error Messages / Log Output

[ERROR] [Collector] [RMVNSQL01\INST1] index_object_stats SQL Error #-2: Execution Timeout Expired.
        The timeout period elapsed prior to completion of the operation or the server is not responding.
[WARN ] [RemoteCollectorService] Collector 'index_object_stats' transient SQL error #-2 ...

Screenshots

No response

Additional Context

Root cause

• CollectIndexObjectStatsAsync (Lite/Services/RemoteCollectorService.IndexObjectStats.cs) builds one batch that cursors over every online database, cross‑DB sp_executesql INSERTs each DB's per‑table/per‑index stats into a single #ios temp table (size from sys.partitions/sys.allocation_units, usage from sys.dm_db_index_usage_stats, locking/latch counters from sys.dm_db_index_operational_stats), then one final SELECT. This whole thing is sent as a single SqlCommand.

• That command uses the global CommandTimeoutSeconds = 30 (Lite/Services/RemoteCollectorService.cs:127), which is not user‑configurable.

• sys.dm_db_index_operational_stats is expensive across all indexes/partitions; one large/busy database pushes the sweep over 30s.

• Because it's all‑or‑nothing, a timeout discards results from every database, not just the slow one.

• Enabled by default at ScheduleManager.cs:758 (FrequencyMinutes = 1440), and "never‑run = due immediately," so it fires (and fails) right after upgrade/first connect; the #-2 timeout is treated as transient and keeps retrying.

Impact

• The new Index Objects / unused‑index / contention feature shows no data on any server where one DB makes the sweep exceed 30s — i.e., exactly the larger estates the feature is most useful for.
• Repeated 30s timeouts add load each time it runs.

Suggested fixes (any/all)

• Collect per database in separate commands instead of one cross‑DB sweep, so a slow database only fails itself and the rest still persist. (Biggest win — removes the all‑or‑nothing failure mode.)
• Give this heavy collector a higher / dedicated / configurable CommandTimeout (the global 30s is too low for dm_db_index_operational_stats over all DBs). Expose a per‑collector or global timeout setting.
• Ship it opt‑in / disabled by default, given its cost (matches how a capacity/FinOps collector should behave).
• Optionally reduce dm_db_index_operational_stats cost (scope per object/partition, skip very large DBs, or apply a per‑DB time budget) and honor the existing exclusion filter for skipping heavy DBs.