Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
A documented "best practice" within ServiceNow, though also very unknown best practice: do not use a Delete Multiple method on tables that contain Currency type fields. Nothing new that I'm sharing today, this was documented by ServiceNow years ago already and almost a year ago I already made a LinkedIn post on this. I'm writing this short article to raise more awareness on this best practice.
Do not use deleteMultiple()
What best practice am I referring to? When searching a bit on the ServiceNow Documentation, you can find this page which mentions:
"Do not use deleteMultiple() on tables with currency fields. Always delete each record individually."
Doing some more research on the reason behind this best practice, there is tiny bit more information mentioned in this knowledge article from ServiceNow Support:
"Records in fx_currency_instance/fx_price are deleted when the currency value is deleted from the parent record. However, this does not always happen when deleteMultiple() is used. This is a known limitation of the currency API.
When deleteMultiple() is allowed, currency records can be orphaned. This does not affect functionality; however, the tables can grow large."
From experiences at customers, I can mention this also applies to the undocumented "GlideMultipleDelete" API. I'm unaware yet if this also applies to using a Table Cleaner [sys_auto_flush], though going to test this out 😀.
Consequences
The knowledge article from ServiceNow Support mentions that orphaned currency records do not affect functionality. Personally I don’t fully agree with this. Perhaps it doesn't directly affect end-user functionality, though tables can grow very large affecting performance, manageability, and database footprint.
At one of my customers, using GlideRecord deleteMultiple() in a few Scheduled Jobs caused more than 300 million orphaned Currency Instances records [fx_currency_instance] up to when this was discovered, and still growing. This caught my attention when I started turning the database footprint inside out, noticing that the fx_currency_instance table was in the top 10 tables in their database footprint report. fx_currency_instance being one of the top 10 tables for a ServiceNow instance is extremely unusual.
Performance: One of the noticeable consequences would be that when opening a list of records for such a table would freeze your browser session for tens of seconds or the browser session would even time out. Also performing queries through scripting would not return results in a split second.
Manageability: When performing operational tasks, searching for issues, maintaining, reporting, etcetera the time and effort involved would exponentially increase. Increase due to the large number of records, and as mentioned the performance involved.
Database footprint: Unmanaged these tables will grow very large, making the Database Footprint unnecessarily large and having some technical implications though also... contributing to a Database Footprint size that exceeds the contractual allowed size. When that happens, customers will pay additional licensing for every additional Terabyte (rounded up).
Finding orphaned records
When tables grow very large, analyzing its data and viewing records manually is not a realistic task because of the volume though also as mentioned the performance. What makes it more difficult in this case, the fx_currency_instance and fx_price tables don't have actual Reference type fields to the parent record though a Document ID type field instead.
A method to identify if your instance is facing orphaned fx_currency_instance and/or fx_price records, might be running below script in the background like the one below. Note: this can run for several hours!
(function() {
var table_name = 'fx_currency_instance',
encoded_query = 'tableISNOTEMPTY^idISNOTEMPTY';
var grSource = new GlideRecord(table_name);
var isValidQuery = grSource.isEncodedQueryValid(encoded_query);
if(isValidQuery) {
grSource.addEncodedQuery(encoded_query);
grSource._query();
var count = 0;
while(grSource._next()) {
if(!gs.tableExists(grSource.getValue('table'))) {
count++;
//grSource.deleteRecord();
} else {
var grTarget = new GlideRecord(grSource.getValue('table'));
grTarget.get(grSource.getValue('id'));
if(!grTarget.isValidRecord()) {
count++;
//grSource.deleteRecord();
}
}
}
gs.info('# Orphaned ' + table_name + ' record count: ' + count);
}
})();
Instance Scan idea
You could create an Instance Scan Scan Check focussed on Data Quality. Making the above script more generic so it can be applied against multiple tables that contain a Document ID type field for example. Do ensure to add a limit in such cases so the Scan Check won't time out. A limit like ending the Scan Check after 10,000 Scan Findings. For a customer, it doesn't matter if you have 10,000 Scan Findings (and you know that the Scan Checks reached the maximum set threshold) or that it contains 300 million Scan Findings.
---
That's it. Hope you like it. If any questions or remarks, let me know!
| C |
If this content helped you, I would appreciate it if you hit bookmark or mark it as helpful.
Interested in more Articles, Blogs, Videos, Podcasts, Share projects I shared/participated in? |
Kind regards,
Mark Roethof
Independent ServiceNow Consultant
9x ServiceNow MVP
---
