Description

Increased resource consumption on Sitecore instances with the Indexing sub-role might be encountered when the size of the EventQueue table has grown considerably because of the high rate of item changes. When Sitecore attempts to clean up the EventQueue, the task might fail with a timeout.

Solution

To mitigate the issue, take these steps:

1. Execute the following query against the EventQueue table of the Master (or Web) database:

   TRUNCATE TABLE [EventQueue]

2. Rebuild the search indexes specific to the Master (or Web) database.

To prevent the issue from occurring in the future, take these steps:

1. Do not save large text values (1MB) in Sitecore item fields.
2. Configure the EventQueueCleanUp task to run more frequently keeping only the most recent events, for example, every hour instead of 4 hours by default.
3. Configure the InstanceName for the CM instance so that underlying machine name changes have no effect on Sitecore.

Take steps 2 and 3 by creating a configuration patch file in the \App_Config\Include\zzz folder. For example:

<?xml version="1.0" encoding="utf-8"?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:set="http://www.sitecore.net/xmlconfig/set/" xmlns:role="http://www.sitecore.net/xmlconfig/role/">
    <sitecore>
        <settings>
            <setting name="InstanceName" value="CM" role:require="ContentManagement"/>
        </settings>
        <scheduling>
            <agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" set:interval="01:00:00" role:require="ContentManagement">
                <DaysToKeep>
                    <patch:delete/>
                </DaysToKeep>
                <IntervalToKeep>04:00:00</IntervalToKeep>
            </agent>
        </scheduling>
    </sitecore>
</configuration>