Troubleshooting issues caused by contacts with an excessive number of interactions in SQL collection database


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

This article describes how to locate and analyze the data to find contacts with an excessive amount 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

Contacts with several thousands of interactions must be checked further since 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 it 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 of the records. Check what pages are visited by the contact (for example, PageViewEvent). The most common causes 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.
    • 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 over-number the page visits. They usually do not give much value to the reports. In API Controller actions Tracker.Current.CurrentPage.Cancel() can be called 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 xDB collection must be redeployed after the operation.

  4. Check the IP Address for the contact. 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 what is the IP and if you know it. If the interactions done by it are suspicious and excessive, you can exclude it as described in "Configure robot detection functionality".

We recommend you to 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: