Path Analyzer – Error when deploying a new map

When you create a new map for the Path Analyzer, this map needs to be deployed. When deploying a map, Sitecore also schedules an historical rebuild for the newly created map. Within the historical rebuild, Sitecore will aggregate all previous interactions, to supply the map with historical data.

Unfortunately when you have a lot of data within your XDB Shards, the rebuild will not succeed. This is caused because Sitecore calls the stored procedure GetRangeBoundsAndInteractionsCount on every of your XDB Shard databases to get the data needed for the rebuild. This stored procedure does a full table scan on the interactions table because of a missing index, which leads to an SQL Timeout resulting in the failure of the historical rebuild task.

If you open the map in the Content Editor, you will see the following warning:

When you open the map in the Path Analyzer, it shows the same warning message on the top right:

The historical rebuild is done on the Processing role, and also logs an XdbCollectionUnavailableException when this occurs.

ERROR [Path Analyzer] BuildMapAgent failed
Exception: Sitecore.XConnect.XdbCollectionUnavailableException
Message: An error occurred while sending the request.
Source: Sitecore.XConnect.Client
   at Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.Client.Operations.HttpOperationInvoker.<Execute>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__98.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__98.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.XdbContext.<ExecuteAsync>d__41.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.XdbContext.<ExecuteSingleOperation>d__42`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.ReadOnlyXdbContext.<CreateInteractionEnumerator>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Analytics.Processing.TaskManager.<ScheduleInteractionProcessingCoreAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Analytics.Processing.TaskManager.<ScheduleInteractionProcessingAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.PathAnalyzer.Processing.Agents.BuildMapAgent.<ScheduleRebuildAsync>d__3.MoveNext()

Nested Exception
Exception: Sitecore.Xdb.Common.Web.ConnectionTimeoutException
Message: A task was canceled.
Source: Sitecore.Xdb.Common.Web
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.<ExecuteAsync>d__41.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.MoveNext()

Nested Exception
Exception: System.Threading.Tasks.TaskCanceledException
Message: A task was canceled.
Source: mscorlib
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.<ExecuteAsync>d__41.MoveNext()

You should be able to see a big spike within the Response Time graph on your xc-collect instance:

If you look at the Query Store within your XDB shard databases, you should also be able to see the GetRangeBoundsAndInteractionsCount stored procedure within the Top Resource Consuming Queries report.

Great, now give me the solution

Luckily there’s an easy fix for this, add an index to the interactions table and change the stored procedure to force it to use the newly created index. You have to do this for every shard! After this you can re-build your maps.
This solution has been approved by Sitecore and is registered as a wish for future Sitecore versions. If you want to communicate with Sitecore Support regarding this issue, please use public reference number 417856.

1. Create index

The index can be created by running the following snippet on your XDB Shard database.

CREATE NONCLUSTERED INDEX [IX_Interactions_LastModified]
ON [xdb_collection].[Interactions] ([LastModified])
INCLUDE ([StartDateTime],[Percentile])
WITH (ONLINE=ON)

Note; when you don’t create the index with Online=On, it will force a lock on the table resulting in failures on your website. For more information about Online Index operations, please visit the following link: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/perform-index-operations-online?view=sql-server-ver15

2. Changing the stored procedure

You can change the stored procedure by altering the GetRangeBoundsAndInteractionsCount stored procedure. Look for the SQL statement below (this should be underneath the comment “Get records count”).

SELECT
    @EstimatedRecordCount = COUNT_BIG(*)
FROM 
    [xdb_collection].[Interactions]

Change the above snippet to the following:

SELECT
    @EstimatedRecordCount = COUNT_BIG(*)
FROM 
    [xdb_collection].[Interactions] WITH (INDEX(IX_Interactions_LastModified))

3. Rebuild your maps

Now that that’s done, it’s time to rebuild your maps!
You can do this for all maps by going to /sitecore/admin/PathAnalyzer.aspx and by clicking on the button beneath “Historic Map Rebuild” (this can take a long time).

If you just want to rebuild the maps that failed, run the query below on your Reporting database. This will set the status of the map to New, and the NewMapAgent will automatically pick up the map and will schedule a rebuild.

update [dbo].[TreeDefinitions] set [Status] = 0 where [Status] = 13 

For an overview of what the status codes mean, take a look at the enum below:

public enum MapRebuildStatus
{
    Undefined = -1,
    New = 0,
    PickedUp = 1,
    Building = 2,
    Built = 3,
    Failed = 13
}