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

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

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. 

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

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.

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

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

It is fixed and closed but not yet released 

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? 

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.

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.

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 editDeletePageCheckInternal, which was what we saw in our profiling. However, it was the symptom and not the root cause to our problem.