Description

A contact with an excessive number of interactions might cause high resource consumption on the xConnect instance and SQL databases. This might result in HTTP Server errors because the requests take a long time to execute. For example:

Sitecore.XConnect.Operations.GetEntityOperation`1[Sitecore.XConnect.Interaction]: Sitecore.Xdb.Collection.Failures.DataProviderException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out

This article describes how to locate and analyze the data to find contacts with an excessive number of interactions for the SQL collection database provider.

Locating The Data

The following query must be executed against all shard databases that are present in the solution. It is supposed to show the contact ID and the number of interactions for this contact.

SELECT TOP (100) ContactId, COUNT(ContactId) as Count
FROM [xdb_collection].[Interactions]
GROUP BY ContactId
ORDER BY Count DESC

Contacts with several thousand interactions must be checked further because they might cause harm to the system. The following query is needed to retrieve the top 100 interactions of a contact that was found during the previous operation. Interactions must be investigated as defined in the "Analyzing the data" section.

SELECT TOP (100) *
    FROM [xdb_collection].[Interactions]
    WHERE ContactId = '6ffc58de-6c56-0000-0000-05d6639738da'

Analyzing The Data

To analyze the located data, perform the following steps:

1. Check the UserAgent column and ensure that the user agent is not a robot. If you are sure that it is a robot user agent, you can exclude it as it is described here: Configure robot detection functionality.
   
   
2. Check the Events column of several records. Check what pages are visited by the contact (for example, PageViewEvent). The most common cases are:
   
   • One page visited per interaction. If there are thousands of interactions and all of them have one page, it is possible that the contact is a robot.
   • The interaction contains custom API requests, which should not be tracked. It is a common case that API calls are used heavily on the site, and they exceed the number of page visits. They usually do not give much value to the reports. In API Controller actions, you can call Tracker.Current.CurrentPage.Cancel() to exclude a page from tracking.
     
     
3. Check the Contact Identifiers and Facets to understand if the contact is known. The following query allows to understand if the contact is anonymous or not. IdentifierType will be 1 if the contact is Known:

   SELECT *, CONVERT(VARCHAR(MAX), Identifier) as IdentifierString
       FROM [xdb_collection].[ContactIdentifiers]
       WHERE ContactId = 'E24BAC10-CD1B-0000-0000-05D6D3DE8788'

   If the contact is not anonymous, you can try to understand who is this contact. You can do this by analyzing the IdentifierString value, or by analyzing the FacetData value returned by the following query:

   SELECT *
       FROM [xdb_collection].[ContactFacets]
       WHERE ContactId = 'E24BAC10-CD1B-0000-0000-05D6D3DE8788'

   Quite common issues here are contacts that are used for load testing. They must be removed after the test session, or the test session must be performed on a lower environment, and the xDB collection must be redeployed after the operation.
   
   
4. Check the IP Address for the contact. The IP Address can be retrieved from the FacetData using the following query:
   

   SELECT TOP(100) *
       FROM [xdb_collection].[InteractionFacets]
       WHERE ContactId = 'E24BAC10-CD1B-0000-0000-05D6D3DE8788' AND FacetKey = 'IpInfo'

   You can analyze the IP about whether you know it. If the interactions done by it are suspicious or excessive, you can exclude it as described here: Configure robot detection functionality.

We recommend that you remove contacts with an excessive number of interactions. They are often robots, which do not provide value in the reports and cause extra load on the system.

Deleting The Contacts

To remove contacts with an excessive number of interactions, consider one of the following options:

• For Sitecore XP 10.1 and later, use the xConnect purging tool.
• For Sitecore XP 9.2 and later, remove a contact using the API as described here: Deleting contacts and interactions from the xDB.
• You can use the ADM module as described here: How to purge historical analytics data.