Fix Email Campaign Pausing: Sitecore Email Experience Manager 3.x Retry Data Provider

Background

My company uses Email Experience Manager (EXM) to send several million emails a day, and we have been facing issues where our large campaigns would pause mid-send.

We have a scaled EXM environment with 2 dedicated dispatch servers, and a separate SQL Server, all with appropriate resources so the hardware was not an issue. We also ensured that databases were kept in tiptop condition (proper maintenance plans with stats being updated), and configurations where optimal for our environment.

The causing of the pausing

After digging in, I discovered that the pausing was caused by SQL deadlocks due to the massive amount of records and CRUD activity on the EXM SQL databases.

Sample Exception:

 ERROR Transaction (Process ID 116) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
 Exception: System.Data.SqlClient.SqlException  
 Message: Transaction (Process ID 116) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
 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.TryHasMoreRows(Boolean& moreRows)  
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)  
   at System.Data.SqlClient.SqlDataReader.Read()  
   at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)  
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()  
   at Sitecore.Modules.EmailCampaign.Core.Data.SqlDbEcmDataProvider.CountRecipientsInDispatchQueue(Guid messageId, RecipientQueue[] queueStates)  
   at Sitecore.Modules.EmailCampaign.Core.Gateways.DefaultEcmDataGateway.CountRecipientsInDispatchQueue(Guid messageId, RecipientQueue[] queueStates)  
   at Sitecore.Modules.EmailCampaign.Core.Analytics.MessageStatistics.get_Unprocessed()  
   at Sitecore.Modules.EmailCampaign.Core.Analytics.MessageStatistics.get_Processed()  
   at Sitecore.Modules.EmailCampaign.Core.MessageStateInfo.InitializeSendingState()  
   at Sitecore.Modules.EmailCampaign.Core.MessageStateInfo.InitializeMessageStateInfo()  
   at Sitecore.Modules.EmailCampaign.Factory.GetMessageStateInfo(String messageItemId, String contextLanguage)  
   at Sitecore.EmailCampaign.Server.Services.MessageInfoService.Get(String messageId, String contextLanguage)  
   at Sitecore.EmailCampaign.Server.Controllers.MessageInfo.MessageInfoController.MessageInfo(MessageInfoContext data)  
   

How does this new data provider fix the problem?

The new data provider introduces efficient SQL deadlock handling. When a deadlock is detected, it will wait 5 seconds and then retry the transaction. The code will try to execute a deadlocked transaction 3 times.

Configuration

Defaults are set to wait 5 seconds for the retry, and the max retry attempts is 3. The DelaySeconds and RetryCount settings can be modified to suit your needs.

 <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">  
  <sitecore>  
   <ecmDataProvider defaultProvider="sqlretry">  
    <providers>  
     <clear/>  
     <add name="sqlretry" type="Sitecore.EmailCampaign.RetryDataProvider.RetrySqlDbEcmDataProvider, Sitecore.EmailCampaign.RetryDataProvider" connectionStringName="exm.master">  
      <Logger type="Sitecore.ExM.Framework.Diagnostics.Logger, Sitecore.ExM.Framework" factoryMethod="get_Instance"/>  
      <DelaySeconds>5</DelaySeconds>  
      <RetryCount>3</RetryCount>  
     </add>  
     <add name="sqlbase" type="Sitecore.Modules.EmailCampaign.Core.Data.SqlDbEcmDataProvider, Sitecore.EmailCampaign" connectionStringName="exm.master">  
      <Logger type="Sitecore.ExM.Framework.Diagnostics.Logger, Sitecore.ExM.Framework" factoryMethod="get_Instance"/>  
     </add>  
    </providers>  
   </ecmDataProvider>  
  </sitecore>  
 </configuration>  

Source Code and Documentation

Full source code, documentation and package download is available from my GitHub repository:

https://github.com/martinrayenglish/Sitecore-EXM-3.x-Retry-Data-Provider

Read More

Sitecore xDB - Extending xConnect To Reduce xDB Index Sizes And Rebuild Times

Background

In my previous post, I write about how you can extend the xConnect Data Adapter Provider to prevent unwanted data from being written to your shard databases.

But, what if you already have a lot of analytics and contact data in your shards, have an xDB index issue, and are struggling to rebuild your index?  

Or, what if you want to keep the data in your databases just in case you may need it some time in the future but want to lighten the load on your xDB index to improve performance?

In this post, I will show you how to filter data from your xDB index to reduce the index size and growth rate. This will also improve your rebuild times tremendously.

You can also skip ahead and check out the full source code for my example on my GitHub repo: https://github.com/martinrayenglish/Test.SCExtensions

Business Scenario

In a 9.1 project, I was faced with a very slow rebuild times, where it was literally taking weeks to rebuild our xDB index. I was very concerned with the amount of data we were ingesting into the xDB index from the shard databases, specifically the rate at which interactions where being added. I have an older post this talks about this topic, so give it a read if you have not already.

Prior to 9.2, there is no true supported way to purge old interaction data, and so I was faced with the question of how I could reduce the amount of interaction data that our xDB index contained, and preventing our business from being paralyzed for weeks waiting for a rebuild to complete in the future.

Solving The Problem

As sheer quantity of interactions was the problem, I wanted to be able to filter both new and existing interactions from being brought in from the shard databases during a rebuild or after the session end data aggregation process.

Round 1

In my initial exploration, I focused on customizing the index writer code as that seemed to be the sweet spot.  The code below shows an example of where I customized it to remove ALL interactions from making their way to the xDB index (see line 28 where I simply new up a new blank list of InteractionDataRecord objects):

To apply the updated writer, I put the new assembly into
{xconnect}\bin
and
{xconnect}\App_data\jobs\continuous\IndexWorker

 and then updated the XML files to use new writer in {xconnect}\App_data\jobs\continuous\IndexWorker\App_data\Config\Sitecore\SearchIndexer\sc.Xdb.Collection.IndexWriter.Solr.xml
and
{xconnect}\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexWriter.Solr.xml

 <IIndexWriter>  
         <Type>Test.SCExtensions.Xdb.Collection.Search.Solr, Test.SCExtensions</Type>  
         <As>Sitecore.Xdb.Collection.Indexing.IIndexWriter, Sitecore.Xdb.Collection</As>  
         <LifeTime>Singleton</LifeTime>  
  </IIndexWriter>  

This worked well for new interactions coming in from the shard databases after session end, but did not work for rebuild operations so I needed to explore further.

Round 2

The problem that I discovered after my initial try, was that the SolrWriter type specified above is not used by the rebuilder because of the dependency injection implementation details. so writing the custom implementation of the rebuilder class was not a valid option.

I started looking into other options to override the related logic.

After investigating further, I discovered that a custom implementation of both the IndexRebuilderFilter and IndexWriterFilter would take care of both new interactions from sessions and existing interactions coming in via a rebuild operation.  

I created 2 new classes to perform these tasks that I used to decorate the related interface. 

Again, in these examples, I am removing ALL interactions from my xDB index. In a real world scenario, it would be best to filter based on analytics data that is valuable to your business so that it will be available via the xDB search API. 

I show you how to do this further down in my post: More Bang - Selective xDB Index Filtering.

Next, I created decorator XML configs to allow xConnect to use my new writer extension code.

Rebuilder writer config location: {xconnect}\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.Indexing.IndexRebuilderFilterDecorator.xml

 <?xml version="1.0" encoding="utf-8"?>  
 <Settings>  
   <Sitecore>  
     <XConnect>  
       <SearchIndexer>  
         <Services>  
           <IIndexRebuilderFilterDecorator>  
             <Type>Sitecore.XConnect.Configuration.ServiceDecorator`2[[Sitecore.Xdb.Collection.Indexing.IIndexRebuilder, Sitecore.Xdb.Collection], [Test.SCExtensions.Xdb.Collection.Search.Solr.IndexRebuilderFilterDecorator, Test.SCExtensions]], Sitecore.XConnect.Configuration</Type>  
             <LifeTime>Singleton</LifeTime>  
           </IIndexRebuilderFilterDecorator>  
         </Services>  
       </SearchIndexer>  
     </XConnect>  
   </Sitecore>  
 </Settings>  

Writer config location: {xconnect}\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.Indexing.IndexWriterFilterDecorator.xml

 <?xml version="1.0" encoding="utf-8"?>  
 <Settings>  
   <Sitecore>  
     <XConnect>  
       <SearchIndexer>  
         <Services>  
           <IIndexWriterFilterDecorator>  
             <Type>Sitecore.XConnect.Configuration.ServiceDecorator`2[[Sitecore.Xdb.Collection.Indexing.IIndexWriter, Sitecore.Xdb.Collection], [Test.SCExtensions.Xdb.Collection.Search.Solr.IndexWriterFilterDecorator, Test.SCExtensions]], Sitecore.XConnect.Configuration</Type>  
             <LifeTime>Singleton</LifeTime>  
           </IIndexWriterFilterDecorator>  
         </Services>  
       </SearchIndexer>  
     </XConnect>  
   </Sitecore>  
 </Settings>  

After applying these updates, I was able to successful filter interactions coming from the shard databases to my xDB index during both rebuilds and session ends!

More Bang - Selective xDB Index Filtering

As you don't necessarily want to remove all interactions from the xDB index unless you are in dire straights, I added a new class for filtering interactions based on specific events. In other words, if an interaction has an event in it that we care about, we allow it to go into the index.

By using this example, you can lighten your index, and still include interactions that are important to you for personalization etc. 

This full source code for this example can be found on my GitHub repo: https://github.com/martinrayenglish/Test.SCExtensions

Read More

Sitecore xDB - How To Fix Right To Be Forgotten Breaking EXM Campaigns

Background

Our email manager faced an Email Experience Manager (EXM) issue where when starting or resuming an email campaign, it will start initializing and then it will go directly to a paused state.

Errors

The logs of our Content Management Server revealed the following errors: 

Cause

After digging in, I discovered that this was caused by contacts in our lists that where missing an Alias identifier. This is a dependency for EXM in the way it pulls in the necessary contact data during dispatch. 

What had happened was that our email manager had used the Anonymize feature in the Experience Profile dashboard that executes the right to be forgotten xConnect API under the covers. This process removes the Alias identifier from the contact record in our key xDB shard database tables. 

Checking the xDB Database

If you have SQL experience, you will be pleasantly surprised to find that the xDB databases are not that complicated to work with it all.

Executing the following SQL query helped me find the contacts that where missing contact identifiers:

If you compare what a normal contact looks like vs an anonymized contact, you can see that the records have been wiped from the key ContactIdentifiers and ContactIdentifiersIndex tables.

This is the SQL statement that I used:

Normal Contact

Anonymized Contact

Another important point to note is that when a contact has been anonymized, a new ConsentInformation facet as added to the contact.

 

The Fix

I simply wrote a SQL statement that would insert new Alias identifier records in the ContactIdentifiers and ContactIdentifiersIndex tables for the contacts in my lists that had been anonymized.

Running this SQL statement fixed the missing dependency that EXM required for its email dispatch, and our email campaigns starting sending again.

Read More

Sitecore xDB - Optimizing Your xDB Index Rebuild For Speed

Having performed Sitecore xDB index rebuilds many times with large data sets, I wanted to share some key tips to ensure a successful and speedy rebuild.

My experience has been on Azure PaaS, with both Azure Search and SolrCloud, but these techniques can be applied to on-premise and IaaS as well.

Change The Log Level

Before starting a large rebuild job, it's important to enable the proper logging in case you need to investigate any issues that may arise. By default, the log level is set to Warning. Change it to Information.

Navigate to the: App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\CoreServices\sc.Serilog.xml file, and change the MinimumLevel to Information: 

   <MinimumLevel>
      <Default>Information</Default>
    </MinimumLevel>

Optimize Your Indexer Batch Size

Don't be over eager with your indexer's batch size setting. This setting determines how many contacts or interactions are loaded per parallel stream during an index rebuild. This setting is found in the following location:
App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexerSettings.xml file:
<BatchSize>1000</BatchSize>

I have had success reducing this size to 500, as it helps execution go faster and prevents you from hitting timeouts during your rebuild when your shard databases are under heavy load.

Reduce Your SplitRecordsThreshold

A good tip from a member of our Sitecore community. By default, this value is set to 25000. Tweaking and reducing this value can also make your rebuilds run faster, and improve your live indexing.

Like the Indexer Batch size, this setting is found in the following location:
App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\sc.Xdb.Collection.IndexerSettings.xml
<SplitRecordsThreshold>25000</SplitRecordsThreshold>

I have had success reducing this size to a little less than half the original value, 12000.

Reduce Your Index Writer's ParallelizationDegree

To decrease load in your Search Service, another good suggestion is to decrease the PrallelizationDegree Setting which is 4 by default.

You will see this in many of the other configs in your xConnect App Services. This setting determines how many parallel streams of data can be processed at the same time.

This setting is found in this location for Solr:
\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\ sc.Xdb.Collection.IndexWriter.SOLR.xml
 <ParallelizationDegree>4</ParallelizationDegree>​

This setting is found in this location for Azure Search:
\App_Data\jobs\continuous\IndexWorker\App_data\config\sitecore\SearchIndexer\ sc.Xdb.Collection.IndexWriter.AzureSearch.xml
 <ParallelizationDegree>4</ParallelizationDegree>​

I have seen large improvements reducing this value from 4 to 1.

Optimize Your Databases

Optimize, optimize, optimize your shard databases!!!

If you are not already using the AzureSQLMaintenance Stored Procedure on your Sitecore databases, do it today!

This is critically important for not only your shard databases but also your other Sitecore databases like Core, Master and Web.  Marketing Automation and Reference databases also get hammered pretty hard, so make sure that this gets applied and run regularly on these.

Note that this maintenance is 100% necessary. As Grant Killian says: "The expectation that Azure SQL is a 'fully managed solution' is somewhat misleading, as rebuilding query stats and defragmentation are the user’s responsibility."

Sitecore recommends an approach like this: https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Automating-Azure-SQL-DB-index-and-statistics-maintenance-using/ba-p/368974

Schedule an Azure Automation “Runbook” to attend to this after hours for all Sitecore databases.

Run the Rebuild

After all this things have been completed, run the xDB rebuild as Sitecore's docs mentions.  In Kudu, go to site\wwwroot\App_data\jobs\continuous\IndexWorker and execute this command:

.\XConnectSearchIndexer.exe -rr

After this, you will see the magic start with docs in your inactive / rebuild index first be reset to 0, and then counts start gradually increasing.

Unfortunately, there is no way to see how far along you are. There is however a query that you can run against your Azure Search or Solr indexes to check the status.

Azure Search Query
$filter=id eq 'xdb-rebuild-status'&$select=id,rebuildstate

Solr Query
id:"xdb-rebuild-status"

Returned Index Rebuild Status:
Default = 0
RebuildRequested = 1
Starting = 2
RebuildingExistingData = 3
RebuildingIncomingChanges = 4
Finishing = 5
Finished = 6

Rebuild Success

When you run the status query, and you see a "6" (sometimes you will see "5" returned, and that's ok too),  you have a successfully completed the rebuild, and new data will start flowing into your xDB index.

Read More