Sitecore – Marketing automation performance improvements part 3

The Sitecore Marketing Automation has serveral performance issues when trying to use it with a large number of contacts, which causes heavy database usage, or causing the plans to not function at all. In the upcoming blog posts about this subject I’ll share some tips on how to improve the performance. A full list of posts on this topic can be found here.

Heavy memory usage because of not re-using the SqlDataRecord instance(s)

All SQL commands that are used within the Marketing Automation Engine, such as the SetTimeoutCommand, CheckoutCommand, etc make use of the Sitecore.Xdb.MarketingAutomation.SqlServer.TvpConverter class to convert model classes to SQL parameter classes.

An example of the method that is called to convert the ActivityEnrollmentTimeout class to the SQL Parameters representation it needs is as follows

    public SqlDataRecord ToActivityEnrollmentTimeoutsTable(ActivityEnrollmentTimeout enrollmentTimeout)
    {
      Condition.Requires<ActivityEnrollmentTimeout>(enrollmentTimeout, nameof (enrollmentTimeout)).IsNotNull<ActivityEnrollmentTimeout>();
      List<SqlMetaData> sqlMetaDataList = new List<SqlMetaData>();
      sqlMetaDataList.AddRange((IEnumerable<SqlMetaData>) TvpConverter.BuildActivityEnrollmentKeyMetaData());
      sqlMetaDataList.Add(new SqlMetaData("TimeoutDate", SqlDbType.DateTime2, (byte) 0, (byte) 0));
      SqlDataRecord sqlDataRecord = new SqlDataRecord(sqlMetaDataList.ToArray());
      TvpConverter.SetDataRecordValues(sqlDataRecord, enrollmentTimeout.EnrollmentKey);
      sqlDataRecord.SetDateTime(4, DateTime.UtcNow.Add(enrollmentTimeout.Timeout));
      return sqlDataRecord;
    }

As you can see in the example above it creates an SqlDataRecord, and it’s passing the parameters for the stored procedure to it’s constructor. The documentation about the SqlDataRecord clearly states the following:

“When writing common language runtime (CLR) applications, you should re-use existing SqlDataRecord objects instead of creating new ones every time. Creating many new SqlDataRecord objects could severely deplete memory and adversely affect performance.“

Unfortunately the Marketing Automation Engine creates a completely new SqlDataRecord everytime that it needs to convert a model class, leading to high memory usage. An example dump of the Marketing Automation Engine leads to the following memory consumption graph:

As you can see in the above image there’s high memory usage of objects of the type SmiExtendedMetaData, SqlMetaData and SqlRecordBuffer which can be decreased by simply reusing the object.

This is registered as public reference 418402, is broken since 9.0+ and unfortunately still isn’t fixed in 10.1.1