Performance issues when CompactClientDataAgent is started

  • If the Core.ClientData SQL table contains a large amount of data, CompactClientDataAgent is not able to clean all of it at once. This is because SQL Server calls a Table Lock when the ClientDataStore.CompactData method tries to delete more than 5000 records. This leads to accumulating of obsolete entities in the table, which causes Sitecore XP instance performance issues.

    The following exception appears in Sitecore XP logs:

    Exception: System.Data.DataException
    Message: Error executing SQL command: SELECT [Data] FROM [ClientData] WHERE [SessionKey] = @sessionKey
    Nested Exception
    Exception: System.Data.SqlClient.SqlException
    Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    Source: .Net SqlClient Data Provider
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
    at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
    
  • To fix the issue, download the relevant patch for the affected Sitecore XP version and follow the instructions in the readme file inside the archive:

    If there is no patch for your Sitecore XP version, contact Sitecore Support.

Applies to:

CMS 8.2 Initial Release+

October 08, 2019
October 08, 2019

Reference number:

176278