-
Suggestion
-
Resolution: Unresolved
-
None
-
None
-
6
-
Problem Definition
SQL queries called by pull request view are causing frequent high CPU load on database. Investigate options to optimise the SQL queries causing high CPU load on database server.
The following MS SQL queries are identified to be causing the high load on database host.
Query #1
(@P0 bigint)select participan0_.pr_id as pr_id6_119_0_, participan0_.id as id1_119_0_, participan0_.id as id1_119_1_, participan0_.user_id as user_id5_119_1_, participan0_.last_reviewed_commit as last_rev2_119_1_, participan0_.pr_id as pr_id6_119_1_, participan0_.pr_role as pr_role3_119_1_, participan0_.participant_status as particip4_119_1_ from sta_pr_participant participan0_ where participan0_.pr_id=@P0
Query #2
(@P0 bigint,@P1 bigint,@P2 bigint,@P3 bigint,@P4 bigint,@P5 bigint,@P6 bigint,@P7 bigint,@P8 bigint,@P9 bigint,@P10 bigint,@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint,@P16 bigint,@P17 bigint,@P18 bigint,@P19 bigint,@P20 bigint,@P21 bigint,@P22 bigint,@P23 bigint,@P24 bigint,@P25 bigint,@P26 bigint,@P27 bigint,@P28 bigint,@P29 bigint,@P30 bigint,@P31 bigint,@P32 bigint,@P33 bigint,@P34 bigint,@P35 bigint,@P36 bigint,@P37 bigint,@P38 bigint,@P39 bigint,@P40 bigint,@P41 bigint,@P42 bigint,@P43 bigint,@P44 bigint,@P45 bigint,@P46 bigint,@P47 bigint,@P48 bigint,@P49 bigint,@P50 bigint,@P51 bigint,@P52 bigint,@P53 bigint,@P54 bigint,@P55 bigint,@P56 bigint,@P57 bigint,@P58 bigint,@P59 bigint,@P60 bigint,@P61 bigint,@P62 bigint,@P63 bigint,@P64 bigint,@P65 bigint,@P66 bigint,@P67 bigint,@P68 bigint,@P69 bigint,@P70 bigint,@P71 bigint,@P72 bigint,@P73 bigint,@P74 bigint,@P75 bigint,@P76 bigint,@P77 bigint,@P78 bigint,@P79 bigint,@P80 bigint,@P81 bigint,@P82 bigint,@P83 bigint,@P84 bigint,@P85 bigint,@P86 bigint,@P87 bigint,@P88 bigint,@P89 bigint,@P90 bigint,@P91 bigint,@P92 bigint,@P93 bigint,@P94 bigint,@P95 bigint,@P96 bigint,@P97 bigint,@P98 bigint,@P99 bigint,@P100 int,@P101 int)select sta_pull_request.scoped_id, sta_pull_request.to_repository_id, count(sta_pr_participant.id) from sta_pull_request sta_pull_request left join sta_pr_participant sta_pr_participant on sta_pr_participant.pr_id = sta_pull_request.id where (sta_pull_request.id in (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26, @P27, @P28, @P29, @P30, @P31, @P32, @P33, @P34, @P35, @P36, @P37, @P38, @P39, @P40, @P41, @P42, @P43, @P44, @P45, @P46, @P47, @P48, @P49, @P50, @P51, @P52, @P53, @P54, @P55, @P56, @P57, @P58, @P59, @P60, @P61, @P62, @P63, @P64, @P65, @P66, @P67, @P68, @P69, @P70, @P71, @P72, @P73, @P74, @P75, @P76, @P77, @P78, @P79, @P80, @P81, @P82, @P83, @P84, @P85, @P86, @P87, @P88, @P89, @P90, @P91, @P92, @P93, @P94, @P95, @P96, @P97, @P98, @P99)) and (sta_pr_participant.pr_role = @P100 and sta_pr_participant.participant_status = @P101) group by sta_pull_request.scoped_id, sta_pull_request.to_repository_id
Suggested workaround
Adding two new indices as suggested by SQL server should alleviate the load:
SQL server suggests you create indexes on the following columns: 1)Table: [bitbucket].[dbo].[sta_pr_participant]; Impact: 74.25 Column name: [pr_role]; Usage: EQUALITY Column name: [user_id]; Usage: EQUALITY Column name: [participant_status]; Usage: EQUALITY Column name: [pr_id]; Usage: INCLUDE 2)Table: [bitbucket].[dbo].[sta_pr_participant]; Impact: 74.06 Column name: [pr_role]; Usage: EQUALITY Column name: [user_id]; Usage: EQUALITY Column name: [participant_status]; Usage: INEQUALITY Column name: [pr_id]; Usage: INCLUDE