How to check the depth of group nesting in Jira Server and Data Center

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.

Purpose

Too many levels of group nesting in your Jira Server and Data Center instance can cause instance instability — including outages and noticeable performance drops under heavy load — and increase the time for directory synchronization and user authentication.

If you’re experiencing performance issues or want to make sure that you don’t exceed the recommended guardrails, check how deeply groups are nested in your system.

Solution

To check the depth of group nesting in your system, run the appropriate query against your database:

Run the following query if you're using PostgreSQL or MySQL:

with recursive group_hierarchy as (
select
    child_id,
    parent_id,
    parent_name,
           1 as depth_of_nested_groups
from
    cwd_membership
where
    cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
    cwd_membership.child_id,
    cwd_membership.parent_id,
           cwd_membership.parent_name,
           depth_of_nested_groups + 1
from
    cwd_membership
join group_hierarchy on
    group_hierarchy.parent_id = cwd_membership.child_id
where
    cwd_membership.membership_type = 'GROUP_GROUP'
)

select
    *
from
    group_hierarchy

Run the following query if you're using Oracle Database:

with group_hierarchy(child_id, parent_id, parent_name, depth) as (
select
    child_id,
    parent_id,
    parent_name,
           1 as depth
from
    cwd_membership
where
    cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
    cwd_membership.child_id,
    cwd_membership.parent_id,
    cwd_membership.parent_name,
           depth + 1
from
    cwd_membership
join group_hierarchy on
    group_hierarchy.parent_id = cwd_membership.child_id
where
    cwd_membership.membership_type = 'GROUP_GROUP'
)

select
    *
from
    group_hierarchy

Run the following query if you're using Microsoft SQL Server:

with recursive group_hierarchy as (
select
    child_id,
    parent_id,
    parent_name,
           1 as depth_of_nested_groups
from
    cwd_membership
where
    cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
    cwd_membership.child_id,
    cwd_membership.parent_id,
    cwd_membership.parent_name,
           depth_of_nested_groups + 1
from
    cwd_membership
join group_hierarchy on
    group_hierarchy.parent_id = cwd_membership.child_id
where
    cwd_membership.membership_type = 'GROUP_GROUP'
)

select
    *
from
    group_hierarchy
DescriptionSQL queries to get to check the depth of group nesting in Jira Server and Data Center.
ProductJira, Jira Software, Jira Service Management
Last modified on Dec 21, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.