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

If you are still facing this issue post-JIRA 8.0, it is most likely that additional indices IX_changegroup$issueid$iD$created and IX_changeitem$FIELD$GROUPID which were set up previously and not removed before upgrading to JIRA 8.00 or later. This will interfere with change introduced in this bug fix JRASERVER-63002.

Resolution

Ensure that IX_changegroup$issueid$iD$created and IX_changeitem$FIELD$GROUPID are dropped.

Thanks to kkolonko for this.

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.