Why Deleting User Records is Slow

If you’ve ever spent any time as a system administrator with ServiceNow, you’ve probably had to delete a User record, either as demo data, created in mistake, or to clean up the data in general.

The Problem

Deleting data on the sys_user table takes a really long time, to the tune of minutes per user.

The Why

This is because of how many table column references there are to the user table, and how with cascade delete rules, that every one of those columns and related records must be queried for each user deleted.

The Fix (or closest to it…)

The easiest first step is to run use the setWorkflow(false) function when deleting records. This stops any notifications or business rules from firing.

Next, delete a single user using a background script and look at the detailed output. If there are messages like:

Could not find field u_userref in table u_example during cascade delete

Locate the sys_dictionary record for that column and delete it. Sometimes there are leftover user column references when the column is deleted, but the record still remains.

Next look at all sys_dictionary user references, and review the Cascade Delete rule settings for those columns. Consider changing some of the columns to None if maintaining the reference is not important. More information on Cascade Delete rules can be found here: https://docs.servicenow.com/bundle/paris-platform-administration/page/administer/field-administration/task/t_CascadeDeleteRules.html

Once all those fixes have been applied, you can see a drastic improvement in the speed of the deletes. Even cutting the time in half!

Extra credit: ServiceNow provides an SQL Debugger tool which can help you identify where the slowest parts of the DB operation are, including Cascade Delete operations.