SAP Knowledge Base Article - Preview

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


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.



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


SAP Commerce Cloud all versions


hybris,cms,restriction,personalization , KBA , CEC-COM-CPS-WEB , Store frontend, Web CMS, Accelerators , 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.