DTU shooting up after application start in Azure PAAS Sitecore 9.0.2

Question

We are observing that DTUs are shooting up while the application startsup in Azure PAAS environment, however the frequency is not consistent- sometime we restart the webapp explicitly and sometime it's get restarted via Microsoft patches.

This is the plan we had for SQL:

Two pools of 100 DTU each and databases are distributed, we changed this recently and allocated dedicated memory for web DB.

With this latest change we observed some improvements and site was coming up after restart but sometimes it was still failing.

I also observed that there is Sync process which runs once in a day and process several items into Sitecore from third part system, with this when we checked the PublishQueue and Event Queue table for all the database this is what we see:

Master DB : Publish queue: 893778 (count is increasing)
Master DB : Event queue: 160772 (count is increasing)

Core DB : Publish queue: 0
Core DB : Event queue: 467790 (count is increasing)

Web DB : Publish queue: 0
Web DB : Event queue: 138448

Following are the Agent details from Master:

<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
<DaysToKeep>30</DaysToKeep>
</agent>
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
<DaysToKeep>1</DaysToKeep>
</agent>

Following are the Agent details from Web:

<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
<DaysToKeep>30</DaysToKeep>
</agent>
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
<DaysToKeep>1/DaysToKeep>
</agent>

So- overall looking into the Event and PublishQueue records it looks that we really need to update the settings for both Event and PublishQueque so that we can limit the records on both the tables-and to start with here is what i am thinking to update:

For PublishQueue

<DaysToKeep>2</DaysToKeep>

For EventQueue

<IntervalToKeep>04:00:00</IntervalToKeep>

Other than this- we have very limited prefetch cache for master and core DBs so don't think this should be a problem.
What you guys think could be the possible root cause of the error apart from the points mentioned above?

We can also try clearing the Event/Publish table and leave top 1000 records(as per CMS fine tuning guide) and than on top of that we will have the patch to update the agent values.

Sitecore version- 9.0.2

Answer 1

I was suffering from a similar issue for the SQL server for different databases. Below are the details.

Cloud Environment: Sitecore Managed Cloud XP-Small and XP-Medium

Sitecore Version: Sitecore 9.1 Update 1

Cause of the issue: By default from Sitecore Managed Cloud Team indexing strategy was set to "onPublishEndAsyncSingleInstance" in CD role. And Hotfix which was missing for High DTU for Messaging database in Sitecore XP 9.1 on Azure.

After investigating 10 days with the Sitecore support and Product services team we found that the indexing strategy was set to "onPublishEndAsyncSingleInstance" which was causing excessive DTU usage. This was set for below.

1. sitecore_web_index
2. sitecore_fxm_web_index
3. sitecore_marketing_asset_index_web
4. sitecore_marketingdefinitions_web

Solution: To resolve this issue in Sitecore XP 9.1 Initial Release and 9.1 Update-1, install the following hotfix on all web apps: SC Hotfix 325852-2 Sitecore.Messaging 2.0.1, We also created a patch file for setting indexing strategy to Manual for all above from CD role. As all these are getting rebuild properly from CM role.

Patch file: Patch file has below configurations. Note this has to be added only on the CD role.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <contentSearch>
      <configuration>
        <indexes>
          <index id="sitecore_web_index">
            <strategies>
              <strategy>
                <patch:delete />
              </strategy>
              <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/manual" />
            </strategies>
          </index>
          <index id="sitecore_fxm_web_index">
            <strategies>
              <strategy>
                <patch:delete />
              </strategy>
              <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/manual" />
            </strategies>
          </index>
          <index id="sitecore_marketing_asset_index_web">
            <strategies>
              <strategy>
                <patch:delete />
              </strategy>
              <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/manual" />
            </strategies>
          </index>
          <index id="sitecore_marketingdefinitions_web">
            <strategies>
              <strategy>
                <patch:delete />
              </strategy>
              <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/manual" />
            </strategies>
          </index>
        </indexes>
      </configuration>
    </contentSearch>
  </sitecore>
</configuration>

Note in Your Case: In your case you need to check with Sitecore support team for exact Hotfix for your version but I think you can start with Patch file and then after getting the exact hotfix apply that also to resolve the issue.

Hope this answer will help you.