Need to know what is the impact on creating index on production

[Saib1]

Hi,

User is asking to create an index on sys_email table against target table sn_customerservice_bti_billing.

If we create any index on production against above criteria what will be the impact?

[Anand Kumar P]

Hi @Saib1 ,

First configure in lower instance then do it in production no impact it will improve the performance .

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.

Mark it as helpful and solution proposed if it serves your purpose.
Thanks,
Anand

[Ehab Pilloor]

Hi @Saib1,

Creating an index on the `sys_email` table against the target table `sn_customerservice_bti_billing` in ServiceNow can have both positive and negative impacts, and the decision to create an index should be made with careful consideration. Here are the key points to consider:

Positive Impacts:

1. Improved Query Performance:
- Indexing can enhance the performance of queries that involve searching, filtering, or sorting based on fields involved in the index.

2. Faster Data Retrieval:
- Retrieving data related to the target table can become faster when specific columns are indexed.

Negative Impacts:

1. Increased Disk Space:
- Indexes consume additional disk space. If the `sn_customerservice_bti_billing` table is large, creating an index might significantly increase storage requirements.

2. Overhead on Write Operations:
- Index maintenance imposes overhead on write operations (inserts, updates, and deletes). This means that while read operations might become faster, write operations could become slower.

3. Maintenance Overhead:
- Regular maintenance of indexes is necessary to ensure optimal performance. This includes rebuilding indexes and keeping them up-to-date, which may require additional resources.

4. Impact on Inserts and Updates:
- Inserts and updates on the `sys_email` table could be impacted, especially if there are frequent changes to the `sn_customerservice_bti_billing` table, as the corresponding indexes need to be updated.

Considerations:

1. Query Patterns:
- Evaluate the typical query patterns in your ServiceNow instance. If there are frequent queries involving the relationship between `sys_email` and `sn_customerservice_bti_billing`, indexing might be beneficial.

2. Database Size:
- Consider the size of your ServiceNow database. In smaller instances, the impact of indexing might be negligible, but in larger instances, it becomes more critical.

3. Performance Testing:
- Before implementing any changes in a production environment, conduct thorough performance testing in a non-production environment to gauge the actual impact on your specific use case.

4. ServiceNow Best Practices:
- Refer to ServiceNow documentation and best practices for indexing recommendations. ServiceNow provides guidelines on when and how to use indexes effectively.

While indexing can improve query performance, it's essential to weigh the potential drawbacks and assess the specific needs and characteristics of your ServiceNow instance before implementing any changes in a production environment. Always perform such operations during maintenance windows to minimize the impact on users.

If you found this reply useful, please mark it as solution/helpful.

Thanks and Regards,

Ehab Pilloor