TeamCity heartbeat? putting load on sql server

We are using TeamCity 2017.1.1 (build 46654) on Windows, with MS SQL Server Express 2014.

About once a week now, it will get the machine into a state where the sql server process is consuming ~ 25% cpu. As this is running on an EC2 t2 class instance, it then starts pulling our cpu credits down until it reaches 0. The only fix we have is to stop the team city process, wait for our cpu credits to rebuild a little (so we are not cpu limited), and then restart the team city process, which will then run at a good baseline for another few days until repeating all over again.

Profiling the sql server process when it is in this high cpu usage state shows 1 relatively heavy query happening 1 - 2 times per second. Looks to be related to a heartbeat?

declare @p1 int
set @p1=6013265
exec sp_prepexec @p1 output,N'@P0 varchar(8000)',N'select COLUMN_NAME, coalesce(case when DATA_TYPE like ''%char%'' or DATA_TYPE like ''%text%'' then CHARACTER_MAXIMUM_LENGTH else NUMERIC_PRECISION end, 0) as LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = schema_name() and TABLE_NAME = @P0 ','db_heartbeat'
select @p1

I'm assuming this is not supposed to take 50 - 100ms, and to execute every 500ms?

Any ideas?

1 comment
Comment actions Permalink
Official comment

Hi Tyson,

This is the heartbeat SQL query which indeed needs to be run often. It extracts the metadata (from INFORMATION_SCHEMA.COLUMNS) of one of the tables TeamCity uses internally (DB_HEARTBEAT). Unless you have zillions of database schemas in your MS SQL Server instance, the query shouldn't be that expensive.

Generally, running a database in a virtualised infrastructure is not recommended, as you don't have any control over the resources (particularly, physical disk and CPU affinity), and it is unclear which resource quotas are applied by your cloud service provider (Amazon in our case). Additionally, your performance may be impacted by other EC2 users who just happen to have their services being run on the same physical host at this very instant.

  • What is the approximate amount of data for this MS SQL Server instance?
  • Does it serve only TeamCity or maybe some other applications, too?
  • Do you have TeamCity running on the same or on a different host?
  • What is the query cost (in milliseconds) if you migrate your data to a commodity hardware (bare metal)?

Regards,
Andrey.

Please sign in to leave a comment.