We have upgraded our project based on Tridion 2013 and .Net DD4T 2 to SDL Web 8.5 and DD4T 2.2. We are using CIL and micro services to get data from broker. Once 10-15 concurrent user started browsing the website, the database CPU reached to 100%. We have verified that a particular type of query running on the broker database is taking time to execute and also increasing CPU. Here is the query:
(@P0 int,@P1 int,@P2 int,@P3 int,@P4 int,@P5 nvarchar(4000),@P6 float,@P7 int,@P8 int,@P9 nvarchar(4000))select distinct TOP(@P0) itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_, itemmeta0_.LAST_PUBLISHED_DATE as col_3_0_ from ITEMS itemmeta0_ cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ cross join TAXFACETS taxonomyit2_ cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey3_ cross join TAXFACETS taxonomyit4_ cross join COMPONENT componentm5_ inner join ITEMS componentm5_1_ on componentm5_.PUBLICATION_ID=componentm5_1_.PUBLICATION_ID and componentm5_.NAMESPACE_ID=componentm5_1_.NAMESPACE_ID and componentm5_.ITEM_REFERENCE_ID=componentm5_1_.ITEM_REFERENCE_ID cross join CUSTOM_META custommeta6_ where itemmeta0_.PUBLICATION_ID=@P1 and itemmeta0_.ITEM_REFERENCE_ID=componentm5_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm5_.PUBLICATION_ID and componentm5_.SCHEMA_ID=@P2 and itemmeta0_.ITEM_REFERENCE_ID=relatedkey1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=relatedkey1_.PUBLICATION_ID and relatedkey1_.KEYWORD_ID=taxonomyit2_.FACET_ID and relatedkey1_.PUBLICATION_ID=taxonomyit2_.PUBLICATION_ID and relatedkey1_.PUBLICATION_ID=@P3 and taxonomyit2_.TAXONOMY_ID=@P4 and taxonomyit2_.FACET_KEY=@P5 and itemmeta0_.PUBLICATION_ID=custommeta6_.PUBLICATION_ID and itemmeta0_.ITEM_REFERENCE_ID=custommeta6_.ITEM_ID and itemmeta0_.ITEM_TYPE=custommeta6_.ITEM_TYPE and custommeta6_.KEY_FLOAT_VALUE=@P6 and itemmeta0_.ITEM_REFERENCE_ID=relatedkey3_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=relatedkey3_.PUBLICATION_ID and relatedkey3_.KEYWORD_ID=taxonomyit4_.FACET_ID and relatedkey3_.PUBLICATION_ID=taxonomyit4_.PUBLICATION_ID and relatedkey3_.PUBLICATION_ID=@P7 and taxonomyit4_.TAXONOMY_ID=@P8 and taxonomyit4_.FACET_KEY=@P9 order by itemmeta0_.LAST_PUBLISHED_DATE DESC
I believe application is generating this query while executing broker query but while upgrading the project we haven't changed anything in the code. When we run the same page in old environment it is generating some different query and that is smaller and faster in execution on DB server. I think CIL/CIS is generating this query which seems to be non optimized. Can anyone share if faced similar issue earlier?