Kentico EMS - Timeout in inactive contact cleanup

Life buoy and rope

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.

En garde!

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.

Developer Notes