I’ve run into an issue where the “custom metrics query” has seemed to get stuck on a repetitive loop and runs away with the server CPU. This has happened twice now, on two different servers.
Ex: The query found in the example config file in the docs
SELECT DB_NAME(database_id) AS db_name,
COUNT_BIG() * (81024) AS buffer_pool_size
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 – ResourceDB
AND DB_NAME(database_id) NOT IN (‘master’, ‘tempdb’, ‘msdb’, ‘model’, ‘rdsadmin’, ‘distribution’)
GROUP BY database_id
What’s a bit more disturbing is that Stopping the SQL Integration Windows Service doesn’t stop the behavior. Nor does stopping the Infrastructure agent. The only way to stop the query from using the CPU once it’s started was to do a full uninstall of both services.
Once I uninstalled both - the graph above dropped back to the baseline (around 8 - 9 %)
Reinstalling the Infrastructure agent completes correctly and doesn’t cause the cycle to start up again (that functionality is still desired).
But I won’t be installing the SQL Integration agent again.
Edit: Forgot to post system details:
OS: Win2019 Datacenter
SQL: MS SQL 2016 Ent