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:

<!-- language: lang-sql -->

    (@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?