02Jun, 2023
Understanding 100% Database Usage Caused by Your Sitecore GenericProcessingPool Table
While managing an instance of Sitecore I saw a bot got past the excludedUserAgents configuration, which means its traffic is recorded and we don't want that. Flushing the data out is simple with the Analytics Database Manager, but there are cases where there's transient data queued for processing in the GenericProcessingPool table, and it can be a problem.
Looking at the Cause
Bots get by the excludedUserAgents configuration from time to time, which is not a big deal. Part of maintaining a Sitecore site is to keep this up to date. But if you have an instance that isn't scaled to meet the demands of processing a large number of contacts this can be an issue.
Cleaning the junk data is simple with the Analytics Database Manager, but the GenericProcessingPool can cause a massive spike in consumption later on.
The behaviour above is seen is due to an Arithmetic overflow loop with orphaned data (and the drop is my SQL scripts below being executed). The datatype of the Attempts column is smallint. And the largest possible value for smallint is 32767. Therefore, when the query runs and tries to [Attempts] = ([Attempts] + 1), it causes an infinite Arithmetic overflow loop.
Resolving the Database Demand
In order to fix the performance issue just remove all entries with 32000+ attempts. The related junk data is already gone by the earlier use of the ADM module, so this should be safe to do so. Here's a few queries to help you out:
Total number of offending records:
select count(Id) from [xdb_processing_pools].[GenericProcessingPool] where attempts > 32000
Sample of scheduled dates for these records:
select top(100) scheduled from [xdb_processing_pools].[GenericProcessingPool] where attempts > 32000
Remove the records to clear out DB demand:
delete from [xdb_processing_pools].[GenericProcessingPool] where attempts > 32000
Preventing Future Cases
The bug that's allowing this to happen has been reported and acknowledged by Sitecore, and will be rolled into future releases. Until then, this should help you out.
