Undocumented Table cleaner - OOTB improvements

[Tomas van Gelde]

Tldr

New OOB table cleaner that enables multithreaded deletion which speeds up deletion activities hugely. On Yokohama dev instance active by default and can be self-enabled via sys_trigger.

Intro

Everyone probably had one or another use case where they needed to delete (lots of) data from your instance. And you should be, as instance management is an important part of keeping your environment healthy and performing well.

The table cleaner as provided by ServiceNow is a very powerful and useful tool top keep your (growing) tables under control. It helps you to get rid of stale data and clean tables based on your needs. 

The regular table cleaner has been around for quite a while now, and even though it is working fine it has some limitations. Large tables or large cleanup jobs can take very long. And if you are not precise with your conditions even your conditions can mess up the results. ServiceNow catered for some limitations such as some nice features to help prevent impact on other deletion jobs, for example limiting the maximum deletion time per table.

However, even with the qualities it has the current table cleaner also has some inherent limitations. The delete job is querying the table, using the conditions configured in the sys_auto_flush table, before starting the delete. And it does all its work on a single thread. If you have a large instance, that means large tables (and thus long query times) and usually more nodes as well.

The new table cleaner

It turns out ServiceNow has recognized these limitations and silently introduced a new and improved table cleaner. If you spin up a fresh new Yokohama instance you will see that the orginal table cleaner is deactivated. You can validate this in sys_trigger with the name 'Table cleaner', you will see its trigger type is 'Disabled'. If you search for 'Scheduler For All DMJobs' you will find a new job that takes its place. So now we can start the interesting part, the table cleaner successor.

The new table cleaner is using the same cleaning rules as you are used to (configured in sys_auto_flush). However this new cleaner is multithreaded and activities for querying and deletion are split up. The combination of these two factors (and perhaps more under the hood) makes the new table cleaner way faster. But these are not the only improvements, now we can also see how often the deletion took place and how many records have been deleted.

Unfortunately the documentation for this new feature is pretty non-existent. On the docs I only found one page 'Manage table cleaner resource usage' that explains some of its features.
And in the Washington Patch 1 release there's another reference to this 'DM Job' where a PRB was solved.

Configuring the new cleaner

As seen in the documentation we can configure the table cleaner's multithreaded features in the sys_dm_job table. Documentation says you can configure 'max producers' and 'max consumers'. Initially this did not make it clear what these settings mean, so after we checked in with ServiceNow Support we got the answer. A 'producer' is the activity where the cleaner prepares 'chunks' for deletion. A 'consumer' is the activity that will pick up these chunks and performs the delete action. Each can have multiple threads and configuration of these should be considered wisely. So please be careful, assigning too many threads can (and probably will) mean the deletion activities take too much resources of the instance.
Furthermore, you need to balance both. Too much producers could mean too much prepared work for the consumers to pick up. And too much consumers could mean they don't have anything to delete as the producer might be the bottle neck.

Activating it yourself

Now the fun part, enabling this for your own is very simple.

• Go to the sys_trigger table, find 'Table Cleaner' and set its trigger type to 'Disabled'
• Now find the sys_trigger 'Scheduler For All DMJobs' and set its trigger type to 'Repeat'

As soon as the system picks up the activities you will see data flowing in the sys_dm_run table. This contains the delete actions (chunks) that are being pickup up by a single delete thread (multiple of these can run simultaneously). You will be able to see how many chunks the job took and how many records it managed to delete.
If you open the sys_dm_chunk table you can find the chunks of work prepared. The state field of the chunks shows whether they are prepared ('ready') for picking up, or if they are completed/errored etc. The chunk_details field contains the table, wehereas 'chunk_size' contains the amount of records in scope for this activity.

Practice test

We have had a a practical testcase where the new cleaner was able to delete 25 million CMDB records in a timespan of approximately 60 hours. Of course the throughput is hugely depending on what table you're deleting from and what kind of conditions you chose such as cascade delete.

Some small footnotes

1. We have confirmed with ServiceNow Support that this is the new table cleaner and it will replace the old one. Seeing there is so little documentation I would advise to be cautious before activating and make sure to monitor its results.
2. In the sys_dm_job table you will find three DM Jobs, 'DMOrphan', 'DMUnreferencedRecordCleanerList' and 'DMTableCleaner'. For now my guess is as good as yours in what these do besides what their name indicates.
3. The name suggest there's a connection to the data management job for deleting and updating records. I havent looked into that part too much so that is something for another post.

If you're interested in instance management, make sure to check out the blogs from Mark Roethof as he has some important topics covered. ~400 Articles, Blogs, Videos, Podcasts, Share proj... - ServiceNow Community

[Tomas van Gelde · Accepted solution]

I cant make an article so im posting a reply to mark this resolved.

[ctsmith]

Just out of curiosity... did this new job get renamed to "DMScheduler"? That's the only one I can find similarly named in my instances.  Thanks!  This satisfied a curiosity as to why the original was suddenly disabled in Y upgrade.