High usage of DB CPU

[gabs1 (Gab S.)]

Hi community!
IdentityIQ 8.1p6 running on a mssql .
2 UI servers
2 Task servers

From some times we have noticed that the CPU of DB is going up to 100% and found out that what is causing it is a query created by a filter used in one of the dynamic scope that we use.

Randomly, when someone log in and try to request a new access, so dynamic scope start the filters and rules, looks like that the query is created , start and goes to “suspended” infinitely. The only way to stop it is to kill the process or restart the UI Apache tomcat. Also not too many users are connected at the same time, it’s happening even if only one use it.

We tried to also optimise the filter but nothing changed…

Have you never had some kind of similar issue with the DB where iiq is installed???

I can share the query also if needed.

[brian_weigel]

Any additional info you can share would definitely help! Often times, issues like this generally come from filters on un-indexed attributes or that are having to process non-searchable attributes (which involves parsing data stored as XML text in the DB).

[gabs1 (Gab S.)]

select distinct count(distinct identity0_.id) as col_0_0_ from spt_identity identity0_ left outer join spt_link links1_ on identity0_.id=links1_.identity_id inner join spt_application applicatio2_ on links1_.application=applicatio2_.id left outer join spt_link links3_ on identity0_.id=links3_.identity_id inner join spt_application applicatio4_ on links3_.application=applicatio4_.id left outer join spt_link links5_ on identity0_.id=links5_.identity_id left outer join spt_application applicatio6_ on links5_.application=applicatio6_.id where (identity0_.issiam_market=@P0 and identity0_.issiam_iam_status<>@P1 and identity0_.issiam_iam_status<>@P2 and identity0_.issiam_iam_status<>@P3 and identity0_.issiam_iam_status<>@P4 and (applicatio2_.name in (@P5 , @P6)) or identity0_.manager=@P7 and identity0_.issiam_iam_status<>@P8 and identity0_.issiam_iam_status<>@P9 and identity0_.issiam_iam_status<>@P10 and identity0_.issiam_iam_status<>@P11 and (applicatio4_.name in (@P12 , @P13)) or identity0_.issiam_market=@P14 or applicatio6_.name=@P15) and identity0_.id<>@P16 and identity0_.workgroup<>@P17

This is the query created by this filter quicklink:

((issIAM_iamStatus != "DATE_PRUNE" && issIAM_iamStatus != "LEAVER_MAIN" && issIAM_iamStatus != "LEAVER_POST" && issIAM_iamStatus != "LEAVER_MAIN_WEEK_TO_POST") && (issIAM_market == "something" || issIAM_market == "something") && links.application.name == "application")

[brian_weigel]

I’d be curious how that would work if you removed && links.application.name == "application" from that filter. That tends to be a pretty inefficient filter condition for IIQ to evaluate. And if removing that results in the performance issue being resolved, it will effectively confirm that filter element as the root issue, and discuss possible alternative solutions to accomplish your goal for the original filter.

[gabs1 (Gab S.)]

Hi,
Sorry for late answer. We tried to remove it and looks that I’m not having anymore stuck transactions in the dB and high CPU usage :).

Thanks !