High CPU usage when using the automatic tuning feature

Question

High CPU usage when using the automatic tuning feature

Marcin Dolinski 0

I noticed a few times since the automatic tuning has been enabled on different databases (two different SQL Server instances) that CPU consumption has hit 100%.

The first occurrence ended up with lots of errors "A time out occurred while waiting to optimize the query. Rerun the query." without any specific reason for the issue start.

The second one happened just after backing up the transaction logs

In both cases, the high CPU usage lasted unit I disabled the automatic tuning for the database.

What is also common for both occurrences is also the error I saw in logs:

"The Service Broker endpoint is in disabled or stopped state."

and also some errors about denied access to some .NET DLLs.

Do you know if I'm missing any configuration? Is that a known issue? How to avoid it?

1 answer

Your answer

Answer 1

High CPU usage when using the automatic tuning feature in SQL Server can occur due to several factors, including the nature of the queries being optimized and the system's overall workload. Here are some considerations and steps you can take to address the issue:

Query Optimization: Automatic tuning may lead to changes in query execution plans that could inadvertently increase CPU usage. If the automatic tuning feature is causing performance issues, you might want to review the execution plans of the queries that are timing out or consuming high CPU.
Service Broker Endpoint: The error message regarding the Service Broker endpoint being in a disabled or stopped state suggests that there might be issues with messaging or background processing that could affect performance. Ensure that the Service Broker is properly configured and enabled if your application relies on it.
Access Denied Errors: The errors about denied access to .NET DLLs could indicate permission issues that might affect the execution of certain queries or processes. Verify that the SQL Server service account has the necessary permissions to access these resources.
Monitoring and Logging: Utilize SQL Server's monitoring tools to log CPU usage and query performance over time. This can help identify patterns or specific queries that are causing spikes in CPU usage.
Disabling Automatic Tuning: If disabling automatic tuning resolves the CPU issues, consider managing index tuning and query optimization manually. This allows for more control over how changes are applied to your database.
Consult Documentation: Check the official Microsoft documentation for any known issues related to automatic tuning and high CPU usage. There may be specific configurations or updates that address your situation.

If these issues persist, it may be beneficial to engage with Microsoft support or consult the SQL Server community for further insights.

References:

Shruti Dhruv 250 Reputation points Microsoft External Staff Moderator

2025-11-21T09:55:23.0733333+00:00

Hi Marcin Dolinski,

Just wanted to check whether the explanation provided above, helped you resolve your issue?
Marcin Dolinski 0 Reputation points

2025-11-21T09:58:56.0133333+00:00

Unfortunately not. The root cause is still unknown and I still don't know how to avoid such an issue. According to query store, the CPU consumption was not caused by any particular query. It looks like the SQL Server engine was using too much CPU.
Shruti Dhruv 250 Reputation points Microsoft External Staff Moderator

2025-11-21T10:59:11.64+00:00

Did you check if service broker was disabled?
Apparently , if service broker is off, tuning operations cannot communicate properly.
SQL Server may retry operations repeatedly, causing CPU loops.

To confirm this -

SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabase';

If is_broker_enabled = 0, then enable it.
Also check sys.dm_exec_requests during the spike:

SELECT session_id, status, command, cpu_time, wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50; -- ignore system sessions.

If you see commands like BACKGROUND, TASK, or QUERY STORE with high CPU, it’s because of internal engine tasks.

Hope this helps!

Share via

High CPU usage when using the automatic tuning feature

1 answer

Your answer