Sonar DB Content Profiling

The Sonar DB is the heart of any Sonar instance and managing the content of the DB to prevent it from growing too quickly is the goal of the House Cleaning mechanism. Sometimes, when developing a new Sonar plugin for instance, it can be useful to "profile" the content of this Sonar DB to understand what happens under the hood.

This page contains several SQL requests which allow to get answer to common profiling questions. All those requests can take very long time to be executed and should not be run in a production environment.

Which metrics generate the greatest number of rows in the 'project_measures' table ?

select metrics.id, metrics.name, count(*) as rows from project_measures, metrics where project_measures.metric_id = metrics.id group by metric_id order by rows desc

Ratio of rows on past snapshots in the 'project_measures' table

First get the total number of rows in the project_measures :

select count(*) from project_measures

Then get the total number of rows on past snapshots and divide the number by the previous one :

select count(*) from project_measures, snapshots where project_measures.snapshot_id = snapshots.id and snapshots.islast!=1

Which metrics generate the greatest number of rows in the 'project_measures' table on past snapshots ?

select metrics.id, metrics.name, count(*) as rows from project_measures, metrics, snapshots where project_measures.metric_id = metrics.id and project_measures.snapshot_id = snapshots.id and snapshots.islast!=1 group by metric_id order by rows desc

Ratio of rows on last snapshots in the 'project_measures' table

First get the total number of rows in the project_measures :

select count(*) from project_measures

Then get the total number of rows on last snapshots and divide the number by the previous one :

select count(*) from project_measures, snapshots where project_measures.snapshot_id = snapshots.id and snapshots.islast==1

Which metrics generate the greatest number of rows in the 'project_measures' table on last snapshots ?

select metrics.id, metrics.name, count(*) as rows from project_measures, metrics, snapshots where project_measures.metric_id = metrics.id and project_measures.snapshot_id = snapshots.id and snapshots.islast==1 group by metric_id order by rows desc

Distribution of measures by resource type on past snapshots

select snapshots.scope, count(*) from project_measures, snapshots where project_measures.snapshot_id = snapshots.id and snapshots.islast!=1 group by snapshots.scope