How to request Table/Index reorganization?

[bobswisshelm]

I don't see that we have access to any DBA-like tools in ServiceNow to reorganize tables and/or indexes to reclaim unused space or to improve performance.

My specific question is with CMDB_REL_CI.  This table sees a lot of inserts and deletes.  In a traditional database, this could easily lead to table and index fragmentation. Database engines often provide reorganization tools to reclaim that free space without affecting the availability of the data.  Alternatively, you can save/export the data to another table, then TRUNCATE the original table to release its storage, then reload the data from the copy.

ServiceNow doesn't seem to give us the ability to do any of this.  Do I need to enter a HI request to reorganize tables/indexes?  Can that be executed without affecting user access to the data?

[Mwatkins · Accepted solution]

Hi Robert,

Generally ServiceNow does not expose this layer of database administration because we don't want our customers to have to worry about it. We are a hosted cloud service, after all, and so we want our customers to be able to focus their resources on meeting their business needs without having to spend time and resources on optimizing the database. We have DBA's on staff who routinely optimize different aspects of each MySQL database. We also have some optimizations baked directly into the platform.

One way to force a table reorganization is to add a column to the table. Under the covers we use a swap/drop mechanism - we call it an online alter (ServiceNow KB: Online alter changes (KB0539427), How online alter operations can prevent outages ) - that creates a whole new table and then swaps it with the old one without any impact to the end user. If you wanted to force reorganization on a certain table you could create a meaningless, hidden, custom field on that table. Then next time you want to reorganize the table you could remove said field.

Another way to force a table reorganization is to add an index to the table. Each time an index is added we do the same online alter operation that totally re-creates the table.

By the way, in Jakarta we are adding a really interesting feature for those customers who are database savvy called "Suggested Indexes". This feature is based on our slow query pattern recognition engine (see Database performance tuning tools). If a slow query is identified that could benefit from a certain index then the tool suggests the index to the ServiceNow administrator who then can choose to test out the new index. Data is collected for a period of time (30 days?) and the administrator can see if the index improved the query or not - at which time they can choose to revert the change.

Regards, Matthew

[shivanipatel]

Robert,

We are glad you took advantage of the ServiceNow Community to learn more and to get your questions answered. The Customer Experience Team is working hard to ensure that the Community experience is most optimal for our customers.

If you feel that your question was answered, we would greatly appreciate if you could mark the appropriate thread as "Correct Answer". This allows other customers to learn from your thread and improves the ServiceNow Community experience.

If you are viewing this from the Community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

Thanks,

Shivani Patel

[Janel]

For indexing, you can use the GlideIndexUtils to drop index.  I found this OOB script left over from some upgrade.  I asked support about it and they use the same thing to drop indexes.
I used it on a bunch of stuff without any issues.

var giu = new GlideIndexUtils();
giu.dropByName('table_name with the index', 'index_name');

Also, back in 2017 "...because we don't want our customers to have to worry about it" may be a thing, but that sure isn't the case in 2023, especially with storage.