JIRA Agile performs slowly when using Microsoft SQL Server

Summary

Ranking operations are taking a huge delay in Microsoft SQL 2012, when executed in huge instances (reproduced in an instance with more than 1.500.000 issues).
Due to the delay, the ranking operation times out and fails very frequently. The message below is displayed in the UI:
"JIRA Agile cannot execute the rank operation at this time. Other users may be ranking the issues that you are trying to rank. Please try again later"

Environment

• Tested with Microsoft SQL 2008 and 2012
• JIRA Agile 6.6.80
• 1.500.000 issues

Steps to Reproduce

1. In a JIRA Instance connected with MSSQL, generate at least 1.000.000 issues
2. Create a test board and add some hundred of issues to its backlog
3. Try to rank the issues
4. This can be reproduced by either ranking a single issue, or ranking multiple issues at the same time

Expected Results

The issues should be ranked successfully

Actual Results

The following error will be thrown:

Sometimes it appears that the operation worked, but after refreshing the page the issues are back to their original position in the backlog.

Workaround

When affected by this issue, there is one change that have to be implemented to actually workaround this problem:

1 - Add sendStringParametersAsUnicode=false to the JDBC connection string

1. Open the $JIRA_HOME/dbconfig.xml file
2. Append the following option the connection string ;sendStringParametersAsUnicode=false to the jdbc-datasource/url tag value
   • Connection string will look like the following (url might have extra parameters at the end):

     jdbc:jtds:sqlserver://<SERVER_URL>:<SERVER_PORT>/<DB_NAME>;sendStringParametersAsUnicode=false
     

3. Restart JIRA