Databse index adding on table columns

[Martin45]

Hello team,

We have the issue with performance on CI lookup list. When we want to open CI records on Incident form it takes long time when the CI list is loaded.

We were advised to create new database index on one of custom columns of cmdb_ci table on DEV instance and when Servicenow internal team forced the use of new index the query got down for about 5 seconds.

We would like to do the same on PROD instance but we are afraid there could be some negative impact on instance performance.

Could you please advise?

Thank you,

Martin

[Ratnakar7 · Accepted solution]

Hi @Martin45 ,

Creating database indexes can indeed enhance query performance, but it's essential to approach this cautiously, especially in a production environment. Here are some considerations:

1. Understand the Impact:

   • Positive Impact: Indexing can significantly speed up data retrieval for certain types of queries.
   • Negative Impact: Indexes can slightly slow down data modification operations (inserts, updates, deletes) because the index structures also need to be maintained.

2. Analyzing the Query:

   • Ensure that the index you are planning to create aligns with the actual queries being executed. Indexing the wrong columns may not provide the desired performance improvement.

3. Database Maintenance:

   • Regularly monitor the performance of the instance after adding an index.
   • Perform regular database maintenance, such as updating statistics and rebuilding indexes if needed.

4. Testing in a Safe Environment:

   • Before making changes in the production environment, test the impact of the new index in a non-production (e.g., sub-production or clone) environment that mirrors your production environment as closely as possible.

5. Consider Alternative Solutions:

   • Sometimes, optimizing the query itself or tweaking the ServiceNow platform configuration might be an alternative solution. Consider reaching out to ServiceNow support or community forums for guidance.

6. ServiceNow Upgrade Considerations:

   • Keep in mind that creating custom indexes could have implications during ServiceNow upgrades. Ensure that any customizations are supported in future releases.

7. Backup and Rollback Plan:

   • Always have a backup and a clear rollback plan in case the changes lead to unexpected issues.

Thanks,

Ratnakar

[Ratnakar7 · Accepted solution]

Hi @Martin45 ,

Creating database indexes can indeed enhance query performance, but it's essential to approach this cautiously, especially in a production environment. Here are some considerations:

1. Understand the Impact:

   • Positive Impact: Indexing can significantly speed up data retrieval for certain types of queries.
   • Negative Impact: Indexes can slightly slow down data modification operations (inserts, updates, deletes) because the index structures also need to be maintained.

2. Analyzing the Query:

   • Ensure that the index you are planning to create aligns with the actual queries being executed. Indexing the wrong columns may not provide the desired performance improvement.

3. Database Maintenance:

   • Regularly monitor the performance of the instance after adding an index.
   • Perform regular database maintenance, such as updating statistics and rebuilding indexes if needed.

4. Testing in a Safe Environment:

   • Before making changes in the production environment, test the impact of the new index in a non-production (e.g., sub-production or clone) environment that mirrors your production environment as closely as possible.

5. Consider Alternative Solutions:

   • Sometimes, optimizing the query itself or tweaking the ServiceNow platform configuration might be an alternative solution. Consider reaching out to ServiceNow support or community forums for guidance.

6. ServiceNow Upgrade Considerations:

   • Keep in mind that creating custom indexes could have implications during ServiceNow upgrades. Ensure that any customizations are supported in future releases.

7. Backup and Rollback Plan:

   • Always have a backup and a clear rollback plan in case the changes lead to unexpected issues.

Thanks,

Ratnakar

[Maik Skoddow · Accepted solution]

Hi @Martin45 

Unfortunately, you did not provide any more details, and thus it is difficult to answer.

• OOTB there is a reference qualifier on the CI field for Incidents which only loads so-called "Principal classes". The idea: only a small portion of class is of interest when it comes to being referenced on an incident form. Please check whether you can leverage that attribute to reduce number of loaded CIs (see https://docs.servicenow.com/bundle/vancouver-servicenow-platform/page/product/configuration-manageme... )
• Another trick to speed up list loading is omitting the total number of records. The reason: For determining the total number of records, ServieNow has to perform a so-called full-table scan. If you have millions of records in the list, this can take in the worst case several minutes. (see https://docs.servicenow.com/bundle/vancouver-platform-administration/page/administer/list-administra... )
• And regarding the index: Without any more information, I don't see a reason why an additional index should improve your situation.

Maik