3158552 - User and inverse user CMS restrictions can generate a query that leads to 100% DTU on the database when using the B2B storefront

Symptom

"Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental."

You have some functionality requiring CMS restrictions such as user or inverse user restrictions. These are typically used to determine if a component or slot should be visible to a user depending on whether they are logged in or not.

When these CMS restrictions are in place, the database reaches hight CPU/DTU percentages with the below query being the main contributor:

(@P0 nvarchar(4000),@P1 bigint,@P6 bigint,@P5 bigint,@P4 bigint,@P3 bigint,@P2 bigint,@P10 bigint,@P9 bigint,@P8 bigint,@P7 bigint,@P11 bigint,@P12 bigint,@P18 bigint,@P17 bigint,@P16 bigint,@P15 bigint,@P14 bigint,@P13 bigint,@P29 bigint,@P28 bigint,@P27 bigint,@P26 bigint,@P25 bigint,@P24 bigint,@P23 bigint,@P22 bigint,@P21 bigint,@P20 bigint,@P19 bigint,@P39 bigint,@P38 bigint,@P37 bigint,@P36 bigint,@P35 bigint,@P34 bigint,@P33 bigint,@P32 bigint,@P31 bigint,@P30 bigint,@P40 bigint,@P46 bigint,@P45 bigint,@P44 bigint,@P43 bigint,@P42 bigint,@P41 bigint,@P57 bigint,@P56 bigint,@P55 bigint,@P54 bigint,@P53 bigint,@P52 bigint,@P51 bigint,@P50 bigint,@P49 bigint,@P48 bigint,@P47 bigint,@P66 bigint,@P65 bigint,@P64 bigint,@P63 bigint,@P62 bigint,@P61 bigint,@P60 bigint,@P59 bigint,@P58 bigint,@P67 bigint,@P73 bigint,@P72 bigint,@P71 bigint,@P70 bigint,@P69 bigint,@P68 bigint,@P84 bigint,@P83 bigint,@P82 bigint,@P81 bigint,@P80 bigint,@P79 bigint,@P78 bigint,@P77 bigint,@P76 bigint,@P75 bigint,@P74 bigint,@P93 bigint,@P92 bigint,@P91 bigint,@P90 bigint,@P89 bigint,@P88 bigint,@P87 bigint,@P86 bigint,@P85 bigint,@P94 bigint,@P100 bigint,@P99 bigint,@P98 bigint,@P97 bigint,@P96 bigint,@P95 bigint,@P111 bigint,@P110 bigint,@P109 bigint,@P108 bigint,@P107 bigint,@P106 bigint,@P105 bigint,@P104 bigint,@P103 bigint,@P102 bigint,@P101 bigint,@P121 bigint,@P120 bigint,@P119 bigint,@P118 bigint,@P117 bigint,@P116 bigint,@P115 bigint,@P114 bigint,@P113 bigint,@P112 bigint,@P122 bigint,@P128 bigint,@P127 bigint,@P126 bigint,@P125 bigint,@P124 bigint,@P123 bigint,@P139 bigint,@P138 bigint,@P137 bigint,@P136 bigint,@P135 bigint,@P134 bigint,@P133 bigint,@P132 bigint,@P131 bigint,@P130 bigint,@P129 bigint)SELECT item_t1.PK FROM users4restriction item_t0 JOIN users item_t1 ON item_t0.TargetPK = item_t1.PK WHERE ( item_t0.Qualifier = @P0 AND item_t0.SourcePK = @P1 AND item_t0.languagepk IS NULL) AND (item_t1.TypePkString IN (@P2,@P3,@P4,@P5,@P6) AND ((item_t1.TypePkString IN ( @P7,@P8,@P9,@P10 ) OR (EXISTS (SELECT item_t2.PK FROM pgrels item_t2 WHERE ( item_t2.SourcePK = item_t1.PK AND item_t2.TargetPK IN ( @P11 )) AND (item_t2.TypePkString=@P12 ) ) AND ( item_t1.p_active = 1 OR EXISTS ( SELECT item_t3.PK FROM usergroups item_t3 WHERE ( item_t3.PK IN (@P13,@P14,@P15,@P16,@P17,@P18) and item_t3.p_uid = 'b2badmingroup') AND (item_t3.TypePkString IN (@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29) AND ((item_t3.TypePkString IN ( @P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39 ) OR ( item_t3.p_unit IN (@P40) and EXISTS ( SELECT item_t4.PK FROM usergroups item_t4 WHERE ( item_t4.PK IN (@P41,@P42,@P43,@P44,@P45,@P46) and item_t4.p_uid = 'b2badmingroup') AND (item_t4.TypePkString IN (@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57) AND ((item_t4.TypePkString IN ( @P58,@P59,@P60,@P61,@P62,@P63,@P64,@P65,@P66 ) OR ( item_t4.PK IN (@P67) and ( item_t4.p_active = 1 OR EXISTS ( SELECT item_t5.PK FROM usergroups item_t5 WHERE ( item_t5.PK IN (@P68,@P69,@P70,@P71,@P72,@P73) and item_t5.p_uid = 'b2badmingroup') AND (item_t5.TypePkString IN (@P74,@P75,@P76,@P77,@P78,@P79,@P80,@P81,@P82,@P83,@P84) ) ))))) ) ))) AND (item_t3.TypePkString IN ( @P85,@P86,@P87,@P88,@P89,@P90,@P91,@P92,@P93 ) OR ( item_t3.PK IN (@P94) and ( item_t3.p_active = 1 OR EXISTS ( SELECT item_t6.PK FROM usergroups item_t6 WHERE ( item_t6.PK IN (@P95,@P96,@P97,@P98,@P99,@P100) and item_t6.p_uid = 'b2badmingroup') AND (item_t6.TypePkString IN (@P101,@P102,@P103,@P104,@P105,@P106,@P107,@P108,@P109,@P110,@P111) AND ((item_t6.TypePkString IN ( @P112,@P113,@P114,@P115,@P116,@P117,@P118,@P119,@P120,@P121 ) OR ( item_t6.p_unit IN (@P122) and EXISTS ( SELECT item_t7.PK FROM usergroups item_t7 WHERE ( item_t7.PK IN (@P123,@P124,@P125,@P126,@P127,@P128) and item_t7.p_uid = 'b2badmingroup') AND (item_t7.TypePkString IN (@P129,@P130,@P131,@P132,@P133,@P134,@P135,@P136,@P137,@P138,@P139) ) )))) ) ))))) ) ))))) ) order by item_t0.PK ASC

As a result, all requests involving this query and even the database in general will be affected, potentially causing a system-wide outage.

Environment

B2B Storefront used with SAP Commerce in the Public Cloud and SQL Server.

Product

SAP Commerce Cloud all versions

Keywords

hybris,cms,restriction,personalization , KBA , CEC-SCC-COM-WCMS , Web Content Management System , CEC-COM-ACC , B2C/B2B Accelerator , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.