Kentico EMS - Timeout in inactive contact cleanup
Posted 15 Mar 2018
On high traffic sites Kentico's EMS feature is a treasure trove of marketing information but the large volume of data can also become your site's tombstone. As the Kentico guidance documentation rightfully points out, it makes no sense to keep older data around forever so you should setup a strategy to clear out inactive contacts from the start.
Even when you properly setup the inactive contact cleanup though, the volume of data can get to a point where the contact cleanup tasks start to time out and nothing gets cleaned up any more. As it turns out, there is quite a bit you can do to prevent from getting into this situation. One of the keys is to keep an eye on your event log and your database. Especially long running queries and timeouts should raise a red flag.
Tuning the database
One of the key queries used by Kentico inactive contact cleanup is this one:
SELECT (COUNT(*)) AS [Count] FROM ( SELECT * FROM OM_Contact WHERE (([ContactEmail] = N'' OR [ContactEmail] IS NULL) AND (EXISTS ( SELECT TOP 1 [ActivityContactID] FROM OM_Activity WHERE [ActivityContactID] = [ContactID] GROUP BY ActivityContactID HAVING MAX(ActivityCreated) <= '1/14/2018 2:00:20 AM' ) OR ([ContactCreated] < '1/14/2018 2:00:20 AM' AND NOT EXISTS ( SELECT TOP 1 [ActivityContactID] FROM OM_Activity WHERE [ActivityContactID] = [ContactID] )))) ) AS SubData
Given this query and the knowledge that there can be millions of rows in the OM_Activity table (17 million in this case) the middle part of the query really stands out as being risky:
SELECT TOP 1 [ActivityContactID] FROM OM_Activity WHERE [ActivityContactID] = @ContactId GROUP BY ActivityContactID HAVING MAX(ActivityCreated) <= '1/14/2018 2:00:20 AM'
It will effectively need to search through the entire OM_Activity table to figure out what contact has recent activities. Unfortunately, the default Kentico setup does not provide a covering index for this. This will force SQL Server to process the entire table, which is quite costly. In this particular case it took an Azure SQL S2 instance well over 40 minutes to execute this.
The covering index is pretty straight foreward and looks like this:
CREATE NONCLUSTERED INDEX [IX_ActivityByContactAndCreated] ON [dbo].[OM_Activity] ([ActivityContactID]) INCLUDE ([ActivityCreated])
It takes SQL server a bit of time to build up this index so make sure you do that during off-peak hours. After applying it though, Kentico is able to determine the number of rows up for deletion in just over 1 minute, well within the configured timeouts.