Performance: SQL Query timeout when editing and saving work items

Issue Summary

Some JA users experience 2-3 min spinning and then "Whoops..." error while editing work item fields or saving work items. However, changes are still saved even with the error.

"Query Timeout expiration" is generated in logs:

• ASP page addEpics.asp (also occurring for several other pages)
• SQL query:

  { [-]
     ASP_function: OOFunctionsSql.SetRSParamsCursor
     correlationid: NFD+Gc35r1xkI5e57z706ZY5la1wAiK9evasUIdItIU=
     customer_db: <customer>
     error_description: Query timeout expired
     error_id: 34035
     error_number: -2147217871
     error_source: Microsoft SQL Server Native Client 11.0
     log_type: exception
     logfrom_location: Website-ASP
     sql:
      SELECT DISTINCT t7.Uniq,
       t7.UID
      From sysmemberpermssionteam t1
      INNER JOIN sysmemberTeamProduct t2 ON t1.TeamID = t2.TeamID
      INNER JOIN tblLifeCycleStateProgramSetRelation t3 ON t3.ProgramID = t2.ProductID
      INNER JOIN tblLifeCycleState t4 ON t4.LifeCycleProgramSetID = t3.LifeCycleProgramSetID
      INNER JOIN sysmemberTeamRole t6 ON t6.TeamID = t1.TeamID
      INNER JOIN sysmemberpermssion t7 ON t7.Uniq = t6.Uniq
      INNER JOIN tblLifeCycleRAM t5 ON t4.LifeCycleStateID = t5.LifeCycleStateID
       and t5.AssignmentID = t6.RoleID
      INNER JOIN sysmemberroles t8 ON t8.RoleID = t5.AssignmentID
       WHERE t4.LifeCycleStateID = ?
        AND t5.AssignmentType = ?
        AND t8.RoleID <> ?
        AND t7.Uniq not in (
      select val
       from fnSplitStringBySeparator(?, ',')
        )
       AND t1.Active = 1
       AND t7.Locked <> - 1
     timestamp: 2022-01-07T14:16:48.496000-05:00
     url: https://<instance>.jiraalign.com:443/addEpics.asp
     urlverb: POST
     user: <id>
     version: 10.103.2.22128

Steps to Reproduce

• Unable to reproduce in labs but easily found in Splunk logs

Expected Results

• The SQL query does not time out

Actual Results

• The SQL query times out

Workaround

• Currently there is no known workaround for this behavior. A workaround will be added here when available.