Jira on MS SQL requires clearing plan cache regularly

[Justin Andrews]

Hello, we've had 4 cases open with Atlassian over the last 2 years regarding slow re-index times. The slowness was intermittent at first where sometimes we could 'lock Jira and re-index' successfully in 16 minutes, and other times taking around 5-6 hours. It has become more regular, and after all of the performance logs and SQL logging everything always looks fine with the Jira app. The slowness comes from the DB. Recently, our DBA discovered if he clears the 'processor cache' or 'plan cache' then Jira will all of the sudden become fast again. The problem is, you almost have to run this command on the DB every few minutes, because it will become slow again 20 minutes later. The command in Microsoft SQL he is executing is "DBCC FREEPROCCACHE"

Has anyone seen this - any ideas for a permanent solution?

[Andy Heinzer]

In my experience the time it takes to index is most effected by the number of issues and the number of custom fields in your environment.  I'm not sure I understand why this SQL command would improve performance here, but honestly, I'm suspicious as to what other side-effects the frequent use of this SQL command might cause.

Could you tell us more about your environment?  Specifically I'd like to know:

• What version of MS SQL this is?
• How many issues you have in Jira?
• How many custom fields exist in this instance?
• How many comments exist?

There are two different ways to gather all this information accurately from Jira.

1. If you recently restarted Jira, then you should be able to go to the $JIRAHOME/log/atlassian-jira.log file and find all this information.  You can search for the term 'Database Statistics'
2. You can create a support zip, in that zip in the application-properties/application.xml you can find this by searching for 'database-statistics'

With this information, I would be interested to then learn more about the hardware for both Jira and the SQL server to better understand if your system is meeting the expected hardware requirements.

[Justin Andrews]

Hello, thank you for taking the time to respond.

MS SQL 2014

124,500 issues

321 custom fields

326,500 comments

The DB server hosts a few other DBs for our other Atlassian products, but that's it.

The Jira server hosts Jira only.

We thought we may have something by following this VMWare article. But, not totally sure this has resolved anything...

[prakashmurugesan]

@Justin Andrews did you get any permanent solution for this issue?

We are using JIRA 7.5.2

We are using MSSQL 2012

0.4 million issues

20 custom fields

DB and application hosted in different servers.

[Gregory Van Den Ham]

I've always known reindexing to take hours not minutes.  I have about a 7gb database right now - I can reindex locked in 4 hours.   I typically though do a regular index that allows access that will run 5-6 hours on a Friday night, when I need to.

I'm on SQL 2014.