Sitecore Content Search may cause performance issues due of excessive updates of the EventQueue table


Description

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:

  1. The following messages appear in the Sitecore log file: 
    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)
  2. Executing the following query against your Core database returns a significant value in the 'Number of entries' column: 
    SELECT COUNT(*) as 'Number of entries'
FROM [EventQueue]
WHERE InstanceType='Sitecore.Data.Eventing.Remote.PropertyChangedRemoteEvent, Sitecore.Kernel, Version=7.0.0.0, Culture=neutral, PublicKeyToken=null'
AND InstanceData LIKE '%last_updated%'

Technical Details

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.

Solution 1

As one of the options to address the issue, it is possible to implement the following solution:

  1. Apply the patch from the Modification of the Properties table does not respect EventDisabler article.
  2. Place the Sitecore.Support.417664.dll assembly to the /bin folder.
  3. Place the Sitecore.Support.DatabasePropertyStore.417664.config  file to the /App_config/Include folder.

Patch details:

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.

Solution 2

You can implement the following solution as an additional option to address the issue:

  1. Place the Sitecore.Support.417664.dll assembly to the /bin folder.
  2. Place the Sitecore.Support.FileBasedPropertyStore.417664.config file to the /App_config/Include folder.

Patch details:

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.