Data distribution between shards unequal

Question

We're currently using Sitecore 10.0.1 with xConnect enabled on Azure PaaS. We have set up two shards DB, shard0 & shard1. Recently we have observed that data distribution between those DBs is unequal and there is a big difference between them. Also, the utilization is different between those. Because of this we always see one of the shards is over-utilized and the space available keeps reducing.

Please can someone help here? What might be wrong here?

Thanks in advance.

Answer 1

I faced the exact same issue last week and it was due to bad traffic from specific IP. There were lots of interactions from a single contact and in one shard. Marketing Automation service was unable to process all the interactions and service started throwing SQL timeout error and due to that DTU utilization for one shard was 100% for 2-3 days.

For further analysis, I started reviewing SQL tables for xDB, then found that that AutomationPool table in Marketing Automation is holding lots of records for a bad contact and that was causing 100% DTU utilization.

Then I performed below steps to fix the issue-

1. Stopped Marketing Automation service (This brought DTU utilization down to normal state)
2. Ran query to find out which contact has more records to process

    SELECT      [ContactId], Count(contactid) Total
    FROM        [xdb_ma_pool].[AutomationPool](nolock)
    GROUP BY    ContactId
    ORDER BY    2 DESC

3. Found a contact id which had around 70+ entries
4. Deleted all records for that contact from AutomationPool table

    DELETE
    FROM        [xdb_ma_pool].[AutomationPool]
    WHERE       ContactId = 'XXXXX'

5. Restarted MA service

Hope this helps.