How to find the issues with most issue links or comments in the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
It's very common to look for ticket outliers that can impact system performance such as issues with thousands of links or comments that might've been added by faulty automation or simply kept growing overtime. It is not unusual for issues with lots of associated links or comments cause threads to hang or connection to timeout before all the data is fetched.
Solution
We recommend testing these queries in your staging environment. They were created on PostgreSQL and may need tuning depending on your database. These will help you to identify issues that can be expensive when fetching their information from the database, so you can review them and potentially take actions to remediate the problem (e.g. deleting or archiving those issues, deleting excessive comments, links or attachments).
Issues with most comments
- Query tested on PostgreSQL, MySQL, and Oracle.
SELECT
count(*),
ji.id,
p.pkey || '-' || ji.issuenum AS issuekey
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
jiraaction ja
JOIN jiraissue ji ON ja.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
ji.id,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;Issues with most attachments
- Query tested on PostgreSQL, MySQL, and Oracle.
SELECT
issueid,
count(*)
FROM
fileattachment
GROUP BY
issueid
ORDER BY
count(issueid)
DESC
LIMIT 5;- Query tested on Microsoft SQL Server.
SELECT TOP 5
issueid,
count(*)
FROM
fileattachment
GROUP BY
issueid
ORDER BY
count(issueid)
DESC;Issues with most INWARD links
- Query tested on PostgreSQL.
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,p.pkey||'-'||ji.issuenum,p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.destination=ji.id
join project p on ji.project=p.id
GROUP BY
il.destination,
ji.id,CONCAT(p.pkey,'-',ji.issuenum),p.pname
ORDER BY
count(*)
DESC;Issues with most OUTWARD links
- Query tested on PostgreSQL.
SELECT
count(*),
ji.id,
p.pkey||'-'||ji.issuenum as issuekey
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
p.pkey||'-'||ji.issuenum,
p.pname
ORDER BY
count(*)
DESC
LIMIT 50;- Query tested on Microsoft SQL Server.
SELECT TOP 50
count(*),
ji.id,
CONCAT(p.pkey,'-',ji.issuenum)
FROM
issuelink il
join jiraissue ji on il.source=ji.id
join project p on ji.project=p.id
GROUP BY
il.source,
ji.id,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(*)
DESC;