Despite the fact that this feature was made available in the new Fuji release to the Administrators, creating database indexes via the User Interface has been in the ServiceNow platform for a long time. Before the Fuji release, it was only available to and was only used by ServiceNow staff that has the maintenance access – the super user role known as ‘maint’.
As your instance is supported by a traditional relational database management system (RDBMS), you will be able to apply some of your database optimization skills to fine-tune the ServiceNow instance. The ultimate goal is to improve the performance level on your instance. One of the most popular techniques to improve database performance is adding appropriate index to your table.
Now, users on the Fuji instances with 'Admin' role can have access to the ‘index creator’ module via two different paths:
- Follow these instructions to Add a Database Index
- Go to System Definitions >Tables and Columns, select the target table by clicking the table name in the list and the ‘index creator’ will be at the bottom of this page:
Before you get excited and submit the request, there are a few things to consider:
- This is an ADVANCED procedure and should only be performed by an administrator familiar with database management.
- The index is not created immediately. The next available system background worker thread will execute the request.
- Once the index has been created, you cannot remove it without asking ServiceNow Support team.
- Be careful of the ‘Use Unique Index’ option. WARNING: creating a UNIQUE index in the UI could lead to data loss if the data on the selected columns are not unique.
- Is there another index that already exists for the target column? If so, reevaluate.
Once you have considered everything and determined a new index will help to speed up the reading of data from the Database. You can implement it in minutes:
- Select one or more columns to index.
- Specify a unique Index Name.
- Click Create Index. The 'Create a New Index' dialog appears.
- Enter your email address in the Email me field (recommended), or select Do not notify me.
- Click OK.
After you click OK a few things will happen. First, a background job will be put in the system jobs queue waiting for the next available worker thread. Once the job starts to execute, a temporary table will be created and altered with the new table definition. This will create triggers to propagate any changes on the live table into the temporary table. The data will then be copied from the live table into the temporary table in small chunks and will swap the temporary table with the live table. Once this occurs, the triggers drop. Finally, if necessary a notification will be sent to the nominated email address.
Over the years, I have recognized a few best practices and recommendations of my own.
Best Practices for creating Database Indexes via the user interface:
- Design your index together with your tables. Don’t wait until there is a performance issue.
- Consider the fact that index only speeds up reading from the database at the cost of slowing down writing operations.
- In some cases a bad index can be worse than no index at all on the table.