Jira on MS SQL requires clearing plan cache regularly Edited

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?

2 answers

0 vote

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.

0 vote

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.

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...

@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.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

169 views 4 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you