Extreme poor performance of "Clear Thumbnail Properties" Schedule Job

Vote:
 

Any Help, how this can improve? Does anyone else have the same issue?

It might be just coincidence but a few months back we upgraded from

CMS 11.8.0 to CMS 11.11.2,
Forms 4.1.20 to 4.23.0
DBLocalizationProvider 4.3.3 to 5.3.0

When Editors started having an issue On Loading EPiServer Editor Area after the upgrade. Editor Area was inaccessible after a week or more of Application Recycle/Release and was not accessible until we recycle application again, this was a recurring issue from a couple of months now.

I have investigated this issue from the last few days and collected the following figures

Queries mentioned here was taking 45% and more DTU
I run SQL Profiler and saw following SP is called editDeletePageCheck, during this job run and that SP calls internally another SP 'editDeletePageCheckInternal'
CPU Usage on DB was more than 80% with the current DB Plan
Sometimes CPU Usages was not coming down even after a few hours when the job is finished

This behaviour and figures can be reproduced all the time by running this job.

What time this job is taking for us on Production is as below

DateDurationStatusServerMessage

7/24/2019 2:00:10 AM 300m:35s Succeeded RD0003FF55A5FA Cleared 63039 thumbnail properties
7/23/2019 2:00:11 AM 268m:58s Succeeded RD0003FF5598AB Cleared 62995 thumbnail properties
7/22/2019 2:00:00 AM 263m:19s Succeeded RD0003FF557B8D Cleared 62992 thumbnail properties
7/21/2019 2:00:01 AM 268m:13s Succeeded RD0003FF5598AB Cleared 62980 thumbnail properties
7/20/2019 2:00:10 AM 276m:26s Succeeded RD0003FF557B8D Cleared 62963 thumbnail properties
7/19/2019 2:00:10 AM 293m:6s Succeeded RD0003FF557B8D Cleared 62943 thumbnail properties
7/18/2019 2:00:10 AM 238m:16s Succeeded RD0003FF4A1B2A Cleared 53964 thumbnail properties
7/17/2019 2:00:10 AM 187m:40s Succeeded RD0003FF4A1B2A Cleared 41402 thumbnail properties
7/16/2019 2:00:10 AM 199m:40s Succeeded RD0003FF597EBB Cleared 41389 thumbnail properties
7/15/2019 2:00:10 AM 128m:53s Succeeded RD0003FF59889B Cleared 24654 thumbnail properties
7/14/2019 2:00:10 AM 132m:21s Succeeded RD0003FF597EBB Cleared 24068 thumbnail properties
7/13/2019 2:00:10 AM 127m:54s Succeeded RD0003FF59AD51 Cleared 24054 thumbnail properties
7/12/2019 2:00:10 AM 134m:59s Succeeded RD0003FF59AD51 Cleared 24022 thumbnail properties
7/11/2019 2:00:00 AM 119m:26s Succeeded RD0003FF59889B Cleared 24004 thumbnail properties
7/10/2019 2:00:10 AM 175m:12s Succeeded RD0003FF55DE3A Cleared 23995 thumbnail properties
7/9/2019 2:00:10 AM 164m:42s Succeeded RD0003FF55DE3A Cleared 23973 thumbnail properties
7/8/2019 2:00:10 AM 161m:1s Succeeded RD0003FF55DE3A Cleared 23936 thumbnail properties
7/7/2019 2:00:10 AM 153m:0s Succeeded RD0003FF5590D7 Cleared 23919 thumbnail properties
7/6/2019 2:00:10 AM 155m:34s Succeeded RD0003FF55DE3A Cleared 23901 thumbnail properties
7/5/2019 2:00:10 AM 152m:10s Succeeded RD0003FF55C920 Cleared 23892 thumbnail properties

#205824
Jul 24, 2019 16:33
Vote:
 

For starter this job should be run manually, not automatically, only if you have problems like if you update a product's image but its thumbnail is not refreshed. 

#205829
Jul 24, 2019 16:47
Vote:
 

(most probably it will be out contents but) any other reason in your knowledge for this kind of bad performance? is 63039 a lot?

#205834
Jul 24, 2019 17:36
Vote:
 

It will simply iterate over your assets and clear any thumbnails on them, then patch the asset to trigger creation of new thumbnails. So if you have many assets and multiple thumbnail properties on your media content types, then yes that will be a lot to clear and yes it will be slow

#205844
Jul 24, 2019 18:11
Vote:
 

Make sense, do you see the involvement of localization provider also during this process? as I have seen a rise in queries to DB Localization provider also.

#205845
Jul 24, 2019 18:15
Vote:
 

It also patches the content so it might have something to do with the localization. 

#205906
Jul 28, 2019 18:13
* 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.