9

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?

Improve this question
4
  • 1
    Not directly related to your question, but probably worth looking into your cache config in both CIS and CIL.
    – Nuno Linhares ♦
    Commented Sep 27, 2017 at 7:23
  • Yes, as Nuno says, I suggest you look at your caching strategy. I've had to optimise the caching on one of my (Web 8.1) projects recently as the Content Service was was maxing out the CPU. Caching (incl. doing more caching in the browser of assets) helped to solve this.
    – Jonathan Williams
    Commented Sep 27, 2017 at 7:59
  • Thanks guys, the caching of micro-services looks good, the issue is only occurring during the First Load and when hitting multiple concurrent requests - the subsequent load of request is quite fast. Further we run profiler, regardless of the caching, the above mentioned query took time in minutes to execute and respond
    – Pankaj Gaur
    Commented Sep 27, 2017 at 8:54
  • 1
    @NunoLinhares Jonathan-Williams, Thanks for your suggestion. CIS and CIL is properly configured and working fine, as we can see drastic decrease in time for page reload. When we checked query execution plan for above query and similar queries it is suggesting to create some indexes. So I am not sure if we are missing any indexes during upgrade. Also, I have checked that statistics is updating and re-indexing is happening daily.
    – Mukesh Kumar
    Commented Sep 28, 2017 at 6:37

1 Answer 1

Reset to default
2

After a lot of debugging, we noticed that some queries (especially ones regarding taxonomy queries) were executed very slow, resulting in a 'queue' of sql commands to be executed. The SQL Server Activity monitor showed us all these expensive queries, and the query execution plan learned us that adding a number of indexes would help improve the execution time of these queries.

After sending the proposed indexes to SDL Support to request if we could add those, we have added them, which has drastically improved cpu load and execution time.

Improve this answer
2
  • Hey Harald, could you explain what you mean with "adding a number of indexes" ?
    – jarnohenneman
    Commented Oct 5, 2017 at 2:38
  • we added indexes on some tables of the broker database. These indexes were suggested by the SQL Server tool that inspects execution plans of the different queries that the Broker generates.
    – Harald Hoffelinck
    Commented Oct 5, 2017 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.