High CPU usage on Microsoft SQL Server by TeamCity queries

Symptoms

• TeamCity database runs on a Microsoft SQL Server.
• There are long-running queries submitted by TeamCity.
• CPU usage on the SQL Server is high.

Cause

• Outdated SQL Server statistics.
• Suboptimal queries from TeamCity.

Troubleshooting

While the issue is occurring, collect this data and provide it to JetBrains support:

1. Multiple server thread dumps. Create a few server thread dumps via Administration > Diagnostics > Troubleshooting > Save Thread Dump, and then save the entire <teamcity_server_logs>/threadDumps-<date> directory under Administration > Diagnostics > Server Logs.
2. All teamcity-server.* logs (Administration > Diagnostics > Server Logs).
3. Query plans of the long-running queries. In SQL Server Management Studio, those can be saved via Active Expensive Queries:

Additional information

It is strongly recommended to ensure SQL Server updates statistics regularly. Outdated statistics can cause performance problems even with optimized queries. More information from 3rd-party resources:

• https://www.sqlshack.com/inaccurate-sql-server-statistics-sql-query-performance-killer-basics/
• https://www.sqlshack.com/sql-server-update-statistics-using-database-maintenance-plans/