Problem Statement

Debugging the JIRA application performance issues is not a trivial task and it can be very complex because of the various factors like configurations and schemes. In this article, you will find the recommended steps along with important SQL queries to help you improve the performance.

Recommended Steps to Improve JIRA Performance

There are different approaches to solve the problem based on each instance or company. However, below are the recommended best practices or focus areas if you are seeing JIRA performance problems.

Customfields

The ability to set custom fields makes Jira a powerful workflow management tool, allowing you to create highly specific objects for modeling the way your organizations work. However, like all good things, too much can be detrimental to Jira's performance and administration - Atlassian 

As mentioned in the Atlassian documents itself, too many custom fields can definitely slow down the performance of the applications. Therefore, it is always recommended to maintain a low number of custom fields.

To find all the duplicate customfields, run this SQL/ API query.

select * from customfield;

If you need optimal customfields, run the "Customfields Optimizer" under the Admin section to optimize the customfields.

Low usage customfields and unused customfields are a liability to the performance of any application. To find any unused and low usage customfields, run the below SQL queries (1).

select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfieldvalue.stringvalue is null 
and customfieldvalue.numbervalue is null 
and customfieldvalue.textvalue is null
and customfieldvalue.datevalue is null
group by customfield.id, customfield.cfname, customfield.description;

select customfield.id, count (*)
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
group by customfield.id having count (*) < 5
order by count (*) desc

The output of these queries is the low usage and unused custom fields. These fields can be removed from screens and deleted after a few weeks.

Another helpful tip regarding custom fields is to put governance in place. It will validate and evaluate any new custom field requests. It is also advisable to reuse existing fields whenever possible to maintain the stable performance of the application.

Project Archival

By archiving a project, you can remove it from Jira, and preserve the data it contains in case you need it later. It’s good practice to archive inactive or completed projects, so they don’t clutter your Jira instance. Fewer projects may also mean better performance.- Atlassian

You never know when you will need data from an old project. Project archival is a feature that enables you to preserve data of inactive or completed projects. It is recommended you archive old projects, so that they will not jumble the JIRA instance, which leads to better performance.

You can start by creating a policy to archive projects that have not been updated for the last three months. Use the query below to get a list of projects, as well as the last created and updated dates for each project (2).

SELECT p.pkey, p.pname, p.lead, p.pcounter AS "last issue id", 
MAX(i.UPDATED) AS "last update", MAX(i.CREATED) AS "last create" FROM 
jiraissue i, project p WHERE i.project = p.id 
GROUP BY p.pname, p.pkey, p.lead
ORDER BY MAX(i.UPDATED) DESC

The below query provides a list of projects, along with the last created and updated date by selected issuetype. For example, the query mentioned below is for issue type "Story".  This can be modified as per the requirement of your need (3).

SELECT p.pkey, p.pname, p.lead, p.pcounter AS "last issue id", 
MAX(i.UPDATED) AS "last update", MAX(i.CREATED) AS "last create" FROM 
jiraissue i, project p WHERE i.project = p.id 
AND issuetype = (select id from issuetype where pname = 'Story')
GROUP BY p.pname, p.pkey, p.lead
ORDER BY MAX(i.UPDATED) DESC

You need to consider the associated project leads and admin before you archive a project. By communicating with the relevant parties, you can proceed with the project archival process. The below example query exhibits how to list the admins of JIRA projects.

select project.pname, projectroleactor.roletypeparameter
from projectroleactor, projectrole, project
where projectrole.id = projectroleactor.projectroleid and projectroleactor.pid =project.id and projectrole.name = 'Administrators';

Issues

Archiving an issue in JIRA is one of the important features Atlassian has recently added in the latest datacenter version.

By archiving an issue, you can hide it in Jira, but preserve the data it contains in case you need it later. It’s good practice to archive Done or Resolved issues, or those whose resolution due date has passed so that they don’t clutter your Jira instance. For example, many customers archive issues that haven't been updated for the last 2 years. - Atlassian

To accomplish this, you can construct a governance policy that requires you to archive issues according to your organization's desired dates. As mentioned earlier in the previous section, you always have to communicate with relevant users connected to an issue before archiving. You can advise them of the importance and the impact of the issue archival as well.

For example, there might be dashboards and filters created by users, including old data. That is why it is important to get the alignment. For the detailed instructions and steps to archive, please refer to this link.

Statuses

Cleaning up statuses is a difficult task, but it can be accomplished with careful planning. To begin, run the SQL query below to get a list of statuses. This can also be done using the JIRA user interface. Navigate to this URL: https://yourjira.com/jira/secure/admin/ViewStatuses.jspa

Or you an run this query:

select * from issuestatus;

After you run this query, you will end up with a list of issue statuses. Find and merge the duplicate statuses (A manual check might be needed in this step along with excel filters) by coordinating with relevant project teams.

Then, you can remove all the unused statuses by following this method.

Create a JQL filter and add it to the dashboard to see the number of issues associated with each status.

“status is not EMPTY”

Workflows

Too many workflows clutter the JIRA instance and administrators can lose the track of them. Inactive or unused JIRA workflows, as well as associated configuration schemes, should be deleted from the application. That is the recommendation. However, double-check that the validators, conditions, and postfunctions are not configured with any fields or other configurations.

You can find unused workflows under the inactive section of the workflow page. Please note that to access this view, you are required to have JIRA admin access. The URL is: https://yourjira.com/jira/secure/admin/workflows/ListWorkflows.jspa

Plugins and Upgrades

For improved JIRA performance, make sure all the plugins and the application are upgraded to the latest version. Whenever there is a need to install a plugin to your application, it is advisable to check customer feedback and reviews before installing the plugin.

There are several testing available to test an application. Install any plugins necessary in DEV or QA environments first to perform the testing.

• Performance testing
• Functional testing
• Tail logs while the plugin is in use.

If there are no issues after testing in the DEV and QA environments, you can proceed to install plugins in the PROD environment.

Other helpful SQL queries

Here are some other queries that I found helpful when handling a JIRA instance.

To obtain the project issue count, run this:

SELECT pname, count(*) AS "total issues" FROM jiraissue i, project p WHERE
i.project = p.id GROUP BY p.pname, i.project

To get a list of sprints, use this query:

select ID,NAME from AO_60DB71_SPRINT;

Get the last login date of the users (This query is for oracle SQL ):

SELECT d.directory_name,
    u.user_name,
        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM DBUSER.CWD_USER u
JOIN (
    SELECT DISTINCT child_name
    FROM DBUSER.CWD_MEMBERSHIP m
    JOIN DBUSER.LICENSEROLESGROUP gp ON m.parent_name = gp.GROUP_ID
    ) m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM DBUSER.CWD_USER_ATTRIBUTES ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) a ON a.user_id = u.ID
JOIN  DBUSER.CWD_DIRECTORY d ON u.directory_id = d.ID
order by last_login_date desc;

A Word of Caution

Please make sure to test the queries recommended in this article in your dev or qa environments before running them in production.

Conclusion

The objective of this chapter is to explain to you how to keep your Jira application safe and clean. There is no one clear solution or a magic switch that will work in all situations.

I hope you learned something valuable from this article to safeguard your JIRA. Happy learning!