We had this issue on Jira 7.13.8 and we applied the indexes. This has made a huge difference to the database usage. We are on Azure SQL PaaS.

-- 1st Index 
CREATE UNIQUE INDEX chggroup_issue_id
 ON dbo.changegroup
(issueid, ID)

-- 2nd index 

CREATE INDEX chgitem_group_field
 ON dbo.changeitem
 (groupid, FIELD)

 -- update statistics on both tables where index was added

  UPDATE STATISTICS dbo.changegroup

  UPDATE STATISTICS dbo.changeitem

Hello!

Thanks, we are waiting this improvement for our setup from out-of-the-box
Also, please, add affected version the 7.6.10 release.

Cheers,
Gonchik Tsymzhitov

Hello, everyone! In an effort to keep bug reports up to date, we have verified the behavior indicated here and have updated the affected versions as well as cleaned up the format of the summary to increase its SEO and to be aligned with our current guidelines. In case there is any additional technical information that you consider important for this case, feel free to add a comment with it.

Best regards,
René Chiquete - JAC Champion.

We experience this problem in production where the query above last over seconds. Creating the described indexes improved performance dramatically. The query is no longer listed in active expensive queries.

tony_chu
I'm happy to hear that.
Just keep in mind that after we publish fix, 
it will be nice to perform xml import on clean database,
so no redundant indexes get left behind,
OR you will need to remove them before update.

Hi psuwala,

This is an update about our finding (on our staging server).
To conduct the test, we use the "re-index" feature. This is because we know it will generally take a long time to traverse all issues and update the data (from SQL Server) to its own indexes. We have many Scripted Fields which generate their result on-the-fly, many of which are using the data from change history. So, re-indexing will actually pull the data from change history quite intensively, ideal for our test purpose. The following is the result: (the time is our local time GMT+8)

Original: Keeping the original indexes without adding new ones

1. (Background re-index)
   • Start: 12:44:00
   • Stop: 12:55:09
   • Elapsed: 11:09
   • CPU: 30%~40%
2. (Lock JIRA and rebuild index)
   • Start: 12:56:00
   • Stop: 12:58:05
   • Elapsed: 2:05
   • CPU: 80%~90%
3. (Lock JIRA and rebuild index)
   • Start: 12:59:00
   • Stop: 13:01:05
   • Elapsed: 2:05
   • CPU: 80%~90%

Use the suggestion: Add the 2 new indexes and disable INDEX chgitem_field ON dbo.changeitem

1. (Background re-index)
   • Start: 13:09:00
   • Stop: 13:13:15
   • Elapsed: 4:15
   • CPU: 10%~20%
2. (Lock JIRA and rebuild index)
   • Start: 13:14:00
   • Stop: 13:15:17
   • Elapsed: 1:17
   • CPU: 40%~60%
3. (Lock JIRA and rebuild index)
   • Start: 13:16:00
   • Stop: 13:17:14
   • Elapsed: 1:14
   • CPU: 50%~70%

Although we don't have many data on staging server (only 3,300+ of them), the effect of applying the new indexes (and disabling the old one) is still quite prominent nevertheless. Thus. we will adopt the new indexes, but we also will keep the old one just for safety measure.
Thank you!

Hi Piotr,

I'm guessing you want us to add the 2 indexes again (because we dropped them the other day when we discovered they didn't help):

CREATE NONCLUSTERED INDEX [changegroup_issueid_ID] ON [dbo].[changegroup] (
[issueid] ASC, [ID] ASC
);

CREATE NONCLUSTERED INDEX [changeitem_groupid_FIELD] ON [dbo].[changeitem] (
[groupid] ASC, [FIELD] ASC
);

And then disable this one which is from default JIRA installation:

CREATE NONCLUSTERED INDEX [chgitem_field] ON [dbo].[changeitem](
[FIELD] ASC
);

It would be nice if we can try your idea, which is a good QA approach, by the way. However, we cannot do that.

• This is a production system. Many of our support teams (around the globe from different regions) face the pressure from our customers so they cannot afford the system down time. Namely, if it (ever) happens again in a short period of time (like, in just a week), my team is to blame. Currently we prefer to avoid any risk.
• We have asked IT team to raise the virtual processors from 4 to 8. Now the CPU chart is really low (around 5%, give or take). I have a feeling that switching the use of the new index might not make any noticeable difference. Unless we downgrade the system to use 4 virtual processors again... But, I'm afraid it's not my call. (guess who will not approve it?)

The only possible way to test is do it on our test/staging server, which still has only 2 virtual processors. However, this server has much less issues data on it, so I'm not sure it can re-create the symptom. I will keep you posted if we discover anything.

tony_chu  zafer

These two new indexes were made in fact to prefer nested loops over hash join.
You can try to disable temporarly field index on changeitem table to see if it helps,
but only if you created these two indexes beforehand.
It could be a great help for us, because as we know hash join on small instances is still faster choice and we are having argument if removing field index is surely good path to go.

Thanks Tony for additional info.

We've exact same problem and looking for workarounds. It's good to know that increasing CPU "solved" this problem. We'll try this.

Hi Zafer,

By the way, it is running on top of a VM (VMWare), originally had 4 virtual processors. Yesterday our IT team added 4 more virtual processors (so it's totally 8 now) and the symptom is gone. Total memory: 24GB. It generally uses 18GB of them.

Hi Zafer,

Yes, we use MS SQL Server 2014 Enterprise edition + Service Pack 2 with KB4130489.

Hi Tony Chu,

do you use mssql server? I think mssql is affected from this bug more than other databases and I want to ask you to.

Hi,

We encountered the exact same problem in the past couple of days. The situation was so bad that we needed to restart Jira multiple times when the DB server reached 100% CPU usage. Unfortunately, the addition of those 2 suggested indexes from the original post didn't improve a bit. In fact, I have studied the existing (default) indexes from those DB tables, and my understanding is that the problematic SQL query is already covered by the indexes chgitem_chggrp/chgitem_field on table changeitem and index chggroup_issue on table changegroup.

Please fix this problem ASAP because we have 3,000+ users (customer supports / software engineers / managers) depending on it. During its down time, my team received lots of complaints, and it surely impacts our quality of work and in the long run it's bad for our customers.

Hello,
ticket is now in review and should be resolved soon.
We conducted tests on reindex and no impact was observed.
We are also removing index for field as it is no longer needed for join and sometimes induced devastating hash join.
Remember to monitor your database memory consumption and stay hydrated.

Sincerely,
Piotr Suwala, Bugfix team.

Hello,
We are currently blocked by index performance degradation and are conducting tests on mssql 2012 to get to know the reason for it.
Have you noticed degradation on other databases?
What index did you use?

Sincerely
Piotr Suwala
Bugfix Dev

Hello, We heavily affected by this bug. Waiting for a fix. Thanks for your work.

unix_app
Which index did you create?
One with included [created] or one without?

Seems to be affecting 7.3.6 also.

This creates a lot of issues for two of our customers, one of them currently in 7.1.1 who we are upgrading but this will not solve this issue.  Our customer holds currently an unlimited license and more than 9k users are affected by this.

We need to share with them a due date for this issue.

Hi,

This issue exists in version 7.1.10.

We've since upgrade to 7.5.1 where the issue exists there as well.

Thanks,

Leonard

We had the same effect on Jira 7.3.4 and MS SQL SERVER 2012.
In our opinion the design plan off the SQL Server cause to this problem.

Hi,

One of my customer has a similar issue, and gets "Communication Breakdown" error messages randomly. Do you think that could be the root cause?

Regards,
Nicolas.

Same effect today on Jira 7.5.2 and SQL Server - really annoying.

In JIRA 7.2.1 We created both indexes (MS SQL SERVER 2012) and no performance improvement was noticed.

When we upgraded to JIRA 7.5.0, indeed, the lock re-indexation performance decreased from 2h 30 m to more than 1 day.

The performance came back as soon as we removed both indexes.

We ran in the same issue and issue assignment wasn't possible anymore.

2017-09-19 16:38:02,607 http-nio-8085-exec-111 A0F35C7 995x393425x4 1fz4ajj /secure/AssignIssue!default.jspa 162675ms "SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM changegroup CG INNER JOIN changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid='2238418' AND CI.FIELD='assignee' ORDER BY CG.CREATED ASC, CI.ID ASC"
2017-09-19 16:54:38,838 http-nio-8085-exec-25 A0F35C7 1009x409500x4 1fz4ajj /secure/AssignIssue!default.jspa 311523ms "SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM changegroup CG INNER JOIN changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid='2238418' AND CI.FIELD='assignee' ORDER BY CG.CREATED ASC, CI.ID ASC"
2017-09-19 16:54:38,839 http-nio-8085-exec-32 A0F35C7 1009x409440x6 1fz4ajj /secure/AssignIssue!default.jspa 316271ms "SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM changegroup CG INNER JOIN changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid='2238418' AND CI.FIELD='assignee' ORDER BY CG.CREATED ASC, CI.ID ASC"

After setting the index "JIRA"."CHGITEM_FIELD" to invisible everything

2017-09-19 17:03:11,855 http-nio-8085-exec-65 A0F35C7 1023x425252x4 1fz4ajj /secure/AssignIssue!default.jspa 3ms "SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM changegroup CG INNER JOIN changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid='2238418' AND CI.FIELD='assignee' ORDER BY CG.CREATED ASC, CI.ID ASC"

Seems to be affecting JIRA 7.3.1