Can we create indexes for Sonar Enterprise edition database

SonarQube Server / Community Build
1
  • Enterprise Edition Version 9.9.4 (build 87374), Scanner, 4.8.1.3023 with no Support
  • how is SonarQube deployed: zip
  • what are you trying to achieve - Our sonar db is having high Data IO and causing the DTU to spike. Recommendation from the DBA - to create a few critical indexes on the DB which should alleviate the IO situation by helping SQL engine fetch data more precisely. Is this allowed? Can we do it? See examples of indexes recommended below.
  • what have you tried so far to achieve this - nothing yet

Examples of indexes DBA suggested to create:

CREATE INDEX Index_name ON [dbname].[dbo].[project_branches] ([need_issue_sync]) INCLUDE ([project_uuid])
CREATE INDEX Index_name2 ON [dbname].[dbo].[active_rules] ([profile_uuid]) INCLUDE ([failure_level], [inheritance], [created_at], [updated_at], [rule_uuid])
CREATE INDEX Index_name3 ON [dbname].[dbo].[project_branches] ([project_uuid], [need_issue_sync])
CREATE INDEX Index_name4 ON [dbname].[dbo].[components] ([enabled], [branch_uuid]) INCLUDE ([uuid])
2

Hey there.

This is not advised, as these are untested and also can mess up future SonarQube upgrades (for example, if SonarQube tries to drop a column that you added an index on).

3

Thanks Colin.

To address the high Data IO issue, do you recommend resizing the DB? Would that be effective?

4

Personally, I don’t have any specific advice to share here. Your DBAs should be involved to make sure your database is properly sized based on the observed workload.

5

Additionally, if you have identified slow queries, you can send us the queries for review? Extra bonus point if you manage to also provide an explain plan :+1: (including buffers, statistics, estimations, actual values, etc)