Very High CPU on SQL Server when function triggers

[tafs7]

We implemented a new Azure Function using the SQL Trigger and have enabled Change Tracking on the SQL Server database and tables we want (SQL Server VM, not Azure SQL).

When we enable the Azure Function and it starts the polling mechanism for changes, it causes the CPU to spike up and stay on high on the SQL Server, to the point where it makes that production database server unusable. This impacts the operations of the main application that relies on that server. So we've had to stop the Azure Function, which immediately drops the CPU back to normal levels on the SQL Server.

Can you provide insight on what might cause this behavior? What can we do to avoid the high CPU usage on the database server?

[Charles-Gagnon]

Sorry to hear you're having issues @tafs7! If you could get some more information for us that will help track down what exactly might be causing this :

1. Is this function the only one running against this server?
2. Do you have any other SQL bindings (input/output) or is the trigger the only one?
3. Have you changed the polling interval or batch size at all?

Additionally, you can go through the steps listed here to see if there's anything suggested. The main one we'd be interested in to start with is step 2 (identify queries) - but the rest are probably worth looking at as well

https://learn.microsoft.com/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues

[Charles-Gagnon]

Closing due to no response, if you run into this again and can provide more information please let us know!