Performance issue – SQL select

[Katarína Geľo]

Couple weeks ago we started having performance issues with JIRA Software and Service Desk when actions (saving issue after edit, issue transitions even displaying ticket) can take up to 10-20 seconds in peak times.

When investigating through JaveMelody plugin we noticed that the biggest culprit is one specific select in our SQL database:

SELECT DISTINCT ID FROM dbo.rundetails WHERE JOB_ID=?

This is a statistic for last month:

Does anybody know if it's associated with specific action in JIRA or if it's possible to find out what could cause it to take longer to execute over time?

The mean time of the select started to take longer about 7 weeks ago and it's continually rising.

Thanks.

[Andy Heinzer]

Hi Juraj,

That specific SQL query is something that Jira will run a lot.  My understanding is that it is running that query to get back details on scheduled jobs within Jira.  You can get a glympse of these kinds of jobs if you are a Jira administrator and by navigating to Cog Icon -> System -> Scheduler details or by going to /secure/admin/SchedulerAdmin.jspa

However this query should be rather lightweight.  It is running on a rather short interval if memory serves, but all it should be returning are the unique id values from that table at any given moment in time.   It is possible that some plugins to Jira could be loading up lots of different jobs to run within Jira, but even then I would not expect this specific query to cause performance problems. 

Rather if you are seeing this query performing badly, it might be more of an indication that either the Jira database is growing very large, or perhaps that the SQL server itself is under-performing in terms of resources.   I would be interested to see if we can learn more about your environment, such as Jira version, database version, database size, system resource monitor from both the database server and the Jira server, etc.   Perhaps we can learn more about this problem that way.

Andy