Which operation of SonarQube causes database server busy (CPU usage 100%)

SonarQube Server / Community Build
1

Hi,
I am writing this to get your help about SonarQube.
We are experiencing that the database server hit 100% CPU usage for about one hour. This symptom occurs aperiodically.
The MS SQL Server reports a query from the SonarQube database consumed the most of the CPU usage. I want to know which feature or operation of SonarQube uses the query and how to disable the feature.
The query is:
select i2.issue_type as ruleType, i2.severity as severity, i2.resolution as resolution, i2.status as status, sum(i2.effort) as effort, count(i2.issue_type) as “count”, i2.inLeak as inLeak
from (
select i.issue_type, i.severity, i.resolution, i.status, i.effort,
case when i.issue_creation_date > @ P0 then 1
else 0 end as inLeak from issues i inner join projects p on p.uuid = i.component_uuid and p.project_uuid = i.project_uuid
where i.status != ‘CLOSED’
and i.project_uuid = @ P1
and (p.uuid_path like @ P2 escape ‘/’ or p.uuid = @ p3) i2
group by i2.issue_type, i2.severity, i2.resolution, i2.status, i2.inLeank

SonarQube version: Community edition 7.9.1
MS SQL Server: 2016

Many thanks,
Alvin

3

Hello @alvin.hwang,

The reported query is used for the computation of measure. You can’t disable that feature.

Do you happen to have a huge “LIVE_MEASUREs” table? Could you share its size?
Could you share the query plan of this query?

This query being critical, it’s been optimized and table “LIVE_MEASURES” table should have the required indices to have it run efficiently.

Cheers,

4

Hi Sébastien,

The count of LIVE_MEASURES is 13,413,226. Could you share the index statement of LIVE_MEASURES? And is it ok to delete all data in table LIVE_MEASURES?

Many thanks,
Alvin

5

Hello @alvin.hwang,

This is not a small size for this table but it’s not huge either.
We have instances with 10 times that many rows working perfectly fine. Hardware and database is setup to handle it though. Could it be that’s not the case for you?

To save back and forth, let’s do it the other way around. What about you post the DDL of PROJECT_MEASURES table in your DB and I’ll tell you if anything is wrong with it? (many tools out there can generate it for you)

SQ’s database must be considered as a blackbox and if you take any action on it, you shouldn’t expect much support from SonarSource (and probably not much from the community either) and pretty much consider you’ll be on your own with dealing with the consequences.

Now, on taking this action in particular, if you want to shrink that table (and the whole DB in the process), you can go to SonarQube’s UI and clean old projects and branches.

Cheers,

6

Hi Sébastien,
Please review the DDL of our project_measures:

    CREATE TABLE [dbo].[project_measures](
        [id] [bigint] IDENTITY(1,1) NOT NULL,
        [value] [decimal](38, 20) NULL,
        [metric_id] [int] NOT NULL,
        [text_value] [nvarchar](4000) NULL,
        [alert_status] [nvarchar](5) NULL,
        [alert_text] [nvarchar](4000) NULL,
        [description] [nvarchar](4000) NULL,
        [person_id] [int] NULL,
        [variation_value_1] [decimal](38, 20) NULL,
        [variation_value_2] [decimal](38, 20) NULL,
        [variation_value_3] [decimal](38, 20) NULL,
        [variation_value_4] [decimal](38, 20) NULL,
        [variation_value_5] [decimal](38, 20) NULL,
        [measure_data] [varbinary](max) NULL,
        [component_uuid] [nvarchar](50) NOT NULL,
        [analysis_uuid] [nvarchar](50) NOT NULL,
CONSTRAINT [pk_project_measures] PRIMARY KEY CLUSTERED
(
        [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Many thanks,
Terry

7

Hello @alvin.hwang,

We’ve been discussing table LIVE_MEASURES so far and you’ve provided the DDL of table PROJECT_MEASURES.

Please have a look at file schema-h2.ddl. It describes the expected schema for SQ 7.9.1.

This file targets H2 database, so the syntax is not appropriate for MsSQL but it does describe the expected columns and indices.

You can use this file to verify your schema complies with the one expected by SQ.

Also, I realize I’ve been focusing on table LIVE_MEASURES so far and table ISSUES should actually the one to focus on primarily. I suggest you verify this table first.

Cheers,

8

Hi Sébastien,

Please review DDL of LIVE_MEASURES. There is no index in our schema.
Is it ok to create index as follow?
CREATE INDEX “LIVE_MEASURES_PROJECT” ON “LIVE_MEASURES” (“PROJECT_UUID”);
CREATE UNIQUE INDEX “LIVE_MEASURES_COMPONENT” ON “LIVE_MEASURES” (“COMPONENT_UUID”, “METRIC_ID”);

Many thanks,
Alvin

Table schema:
CREATE TABLE [dbo].[live_measures](
[uuid] nvarchar NOT NULL,
[project_uuid] nvarchar NOT NULL,
[component_uuid] nvarchar NOT NULL,
[metric_id] [int] NOT NULL,
[value] [decimal](38, 20) NULL,
[text_value] nvarchar NULL,
[variation] [decimal](38, 20) NULL,
[measure_data] varbinary NULL,
[update_marker] nvarchar NULL,
[created_at] [bigint] NOT NULL,
[updated_at] [bigint] NOT NULL,
CONSTRAINT [pk_live_measures] PRIMARY KEY CLUSTERED
(
[uuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

9

Hi Alvin,

Creating that missing index is recommended. As Sébastien mentioned, you may want to check the DDL on the ISSUES table as well and make sure it matches the expected schema.

Brian

10

Hi Brian,

I will try to create index of LIVE_MEASURES and ISSUES table.

Many thanks,
Alvin