Jira Data Center underperforms when using SQL Server Always-on in sync mode
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In some cases, Jira Data Center might underperform when SQL Server is configured to use Always On availability groups.
This mostly affects updates/deletes, and is particularly bad for bulk updates or imports.
Environment
Jira Data Center, with Microsoft SQL Server as database
Cause
Always On is set to Synchronous-commit mode.
As detailed in Differences between availability modes for an Always On availability group, sync mode waits for the transactions to be committed to the secondary database, increasing database response times.
When Jira needs to perform any database INSERT, UPDATE or DELETE, these operations take longer, causing the operations in the application (such as imports or issue updates) take longer as well.
Solution
Set the Availability group to Asynchronous-commit mode.
To better understand the differences between the SQL Server availability modes, please check its documentation at Differences between availability modes for an Always On availability group.