Stored procedure maxes out DTU in Azure

Vote:
 

We're having the same issue as Johan describes in the following post: https://world.episerver.com/forum/developer-forum/-Episerver-75-CMS/Thread-Container/2017/5/what-function-is-making-this-sql-call/DTU maxes out and the query seems to be running for about five hours (in production). The query stands for about 95% of our CPU load. We've added the index, marked as a solution in above post, but without success. I've talked with other developers that are experiencing the same issues with their customers so it doesn't seem isolated to our solution. We're running the latest EPiServer version with Find.

Also, the following post suggests deactivation of "Clear Thumbnail Properties" Schedule Job. But we don't even have it active. 
https://world.episerver.com/forum/developer-forum/-Episerver-75-CMS/Thread-Container/2019/7/extreme-poor-performance-of-clear-thumbnail-properties-schedule-job/

Any suggestions on where to take this further?

Regards,
Michael

#220934
Apr 08, 2020 14:07
Vote:
 

CMS Core team is working on this SP, and an improved version hopefully will be released soon

#220935
Apr 08, 2020 14:16
Vote:
 

Thanks a lot for your fast feedback Quan! Sounds really good! Do you have any knowledge on the time-line, like a couple of months or more?

Regards,
Michael

#220971
Apr 09, 2020 9:03
Vote:
 

Hi - a fix is under review, so nothing is set in stone yet. it looks like the new version will be 2 weeks or so from now, but it can be sooner or later. 

#220973
Apr 09, 2020 11:08
Vote:
 

Quan do you have a link or reference to the fix so we know when it's going live?

#221331
Apr 16, 2020 11:43
Vote:
 

The bug is CMS-15675 which is now fixed, but it was not made public. I think, but can't guarantee, of course, it will be out before the end of this month.

#221333
Apr 16, 2020 12:23
Vote:
 

I cannot find CMS-15675 anywhere, so I guess it's not been released? Will it be presented under Core: https://world.episerver.com/documentation/Release-Notes/?packageFilter=EPiServer.CMS.Core&typeFilter=All once finished or where can I get this information?

Regards,
Michael

#222468
May 06, 2020 9:00
Vote:
 

https://world.episerver.com/support/Bug-list/bug/CMS-15675

It is fixed and closed but not yet released 

#222470
May 06, 2020 9:12
Vote:
 

Great, I'll keep my eyes open for the release then!

Regarding this issue though, we've only experienced this in production (as previously mentioned) until now. However, due to another (unrelated) bug being fixed, we activated EPiServer Search & Navigation / Find developer index in TST and ACC environments (temporarily) to verify our bugfix, which went fine. This resulted however in both environments to spike DTUs in SQL CPU usage, with the same long running query stated above. And after removing configuration for the index in theese environments DTU for the SQL DB went back to normal again.

Do you have any idé how this could be related to the EPiServer Search & Navigation / Find implementation / configuration? 

#222704
May 11, 2020 12:40
Vote:
 

I can't say anything for sure, but one possibility is that the developer index was empty and somehow the Find content indexing job was triggered. Of course that is a wild guess and without knowing which queries were called, we will never know for sure.

#222705
May 11, 2020 13:00
Vote:
 

Sure that could be the case if the index was empty. But then the load should decrease once the indexing job is complete. The job doesn't take too long, about an hour and we run it once a week. But that's not what we're seeing in these environments. The load keeps staying at 100% DTU for several days while Find is enabled due to this long running query and goes to about 0.2% once we disable Find.

Even if we deploy the fix or optimization for CMS-15675 we probably still will see this heavy load and it feels strange that it's even related to EPiServer Find being enabled or not.

I've started to look at this on local dev setup, and from what I've found now is that this query shows up here as well (with enabled Find index) as very CPU expensive. For our EPi database in SQL Management studio -> Query Store -> Overall Resource Consumption -> CPU Time: the stats for this query for a specific day is 2763553,14ms compared to the second most demanding query 13971ms.

#222706
Edited, May 11, 2020 13:48
Vote:
 

We've found the cause to this issue which caused 100% DTU usage. In short, it was due to an infinite recursive loop caused by IndexInContentAreas attribute being added to both our base class for blocks AND pages. Where the latter one was unnecessary. 

In depth; for every Content, Find automatically generates a property named "SearchText" which contains values of the content's searchable properties. If that content has an item in ContentArea and that item is marked with IndexInContentAreas, then those searchable properties of that item will be added into SearchText as well.

To generate SearchText, the recursive function GetSearchableProperties is utilized. This function iterates all searchable properties of the content, then gets all [IndexInContentAreas = true] items in ContentArea and calls itself to get searchable properties of those items. Here we can imagine some circumstances where endless loop can happen if IndexInContentAreas is enabled for the PageBase. If there are Page-types used in ContentAreas, there are other ways to solve this.

Inside this recursive loop (caused by above) we called:

var references = ContentRepository.GetReferencesToContent(content.ContentLink, false);

Where GetReferencesToContent calls editDeletePageCheck which in turn executes the stored procedure editDeletePageCheckInternalwhich was what we saw in our profiling. However, it was the symptom and not the root cause to our problem.

#223522
May 29, 2020 8:41
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.