Frequent updates of the Sitecore Content Search indexes may lead to performance issues on both Content Management and Content Delivery servers.
The symptoms of experiencing this issue may be the following:
ManagedPoolThread #5 04:44:59 INFO Job started: Sitecore.Tasks.CleanupEventQueue Heartbeat 04:48:00 ERROR Exception in alarm clock event subscriber. Exception: System.Exception Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Source: Sitecore.Kernel at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader() at Sitecore.Data.DataProviders.Sql.DataProviderReader..ctor(DataProviderCommand command) at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object parameters) at Sitecore.Data.DataProviders.Sql.SqlDataApi.<CreateObjectReader>d__0`1.MoveNext() at Sitecore.Eventing.EventQueue.ProcessEvents(Action`2 handler) at Sitecore.Eventing.EventProvider.RaiseQueuedEvents() at Sitecore.Services.AlarmClock.Heartbeat_Beat(Object sender, EventArgs e)
SELECT COUNT(*) as 'Number of entries' FROM [EventQueue] WHERE InstanceType='Sitecore.Data.Eventing.Remote.PropertyChangedRemoteEvent, Sitecore.Kernel, Version=22.214.171.124, Culture=neutral, PublicKeyToken=null' AND InstanceData LIKE '%last_updated%'
The issue may occur due to the excessive population of the EventQueue database table with the PropertyChangedRemoteEvent records by the Sitecore Content Search functionality.
This happens because Sitecore Content Search uses the Properties database table for storing instance specific search indexes metadata. For example, it stores the time of the last index update.
Each change to the Properties table leads to PropertyChangedRemoteEvent being recorded to the EventQueue table to notify other connected Sitecore instances to update their caching layer around the Properties table.
If the index updates are frequent enough, the aforementioned behavior may lead to excessive cache repopulation, and subsequent performance issues on both Content Management and Content Delivery servers.
As one of the options to address the issue, it is possible to implement the following solution:
Because the information recorded by Sitecore Content Search using the Properties table is instance-specific, there may be no need in sharing the property change events among other Sitecore instances. The current patch uses EventDisabler to prevent excessive population of the EventQueue table.
You can implement the following solution as an additional option to address the issue:
The patch makes Sitecore Content Search use the file-based property and do not record remote events to the EventQueue table. The related information is stored as files in the /<datafolder>/indexes/properties folder.