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 votes

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 votes

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


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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,083 views 13 18
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot