ServiceNow Indexes 101
ServiceNow Indexes – Quick Guide
Reasons to create indexes
- Indexes for custom tables are not created automatically!
- Improving query times for existing tables (slow queries log)
One of the main ways to improve query time is through indexes. Indexing fields can significantly improve query times.
How an index works
The purpose of indexing a DB is fundamentally the same as indexing a book. A book's index contains a list of subjects which reference page numbers where these subjects are found. Index doesn't tell us exactly what line a text can be found in that page or the position in that line. But we're able to use the index to locate information more quickly.
Simalarly, a DB index will allow a DB to locate records in a table more quickly. If a specific field has been indexed (i.e., the make of a car), the index will store the location of the records for those different makes. The DB can use that index to return results more quickly.
Browser response time
Via the browser response time tool you can actually see how long the query itself takes. But there's a way you can time that using the GlideStopWatch().
Timing queries with GlideStopWatch()
Script:
// Using GlideStopWatch(), record the time taken to execute a query
gs. cacheFlush();
var stopWatch = new GlideStopWatch();
var gr = new GlideRecord('x_272088_vehicle_0_vehicles');
gr.addEncodedQuery('make=Volvo');
gr.query();
gs.info('Query duration: '+ stopWatch. toString());
Note: it needs to be run on the global scope.
If the query duration is more than 2 seconds, then you have to create an index on that particular field to make the query signficantly faster and to avoid all kinds of problems.
Create an index
Procedure:
- Navigate to the table > Database Indexes (in the related list)
- Click New
- Select the correct table + appropriate field
- Click Create index
Note: unique index ensures that if you have multiple values, they together should form a unique record.
Result --> after creating an index on a particular field, the query should run signficantly faster.
Reasons you need indexes
- Slow Queries Log
- This will enable us to determine for which queries we need an index for
- Explain plan
- To take a closer look at the query itself to determine if an index was used and also what part of the index was used
- Composite indexes
Basically, how do you know when you to create an index?
For a custom table, you'll definitely need indexes for it. You'll need to identify what queries will be performed most often against that table; whether that is from user transactions, lists, forms, background processes (i.e., flows, scheduled jobs, etc.). Essentially, look at the fields that are being used in those queries and create indexes based on them.
Slow Queries log and high-impact queries
For system administrators it's crucial to monitor the overal performance of their instance. One of the ways they can do that is by analysing the slow queries (sys_query_pattern).
This table records every query in the system where the total execution time (for every query) exceeds 5 seconds.
Total execution time > 5 seconds
Type of query impact:
2 queries * 10,000ms = 20,000ms --> LOW IMPACT
5,000 queries * 5,000ms = 25,000,000ms --> HIGH IMPACT
Important is to know what actually slow transactoins mean: is it a slow transaction that maybe has just occured once or twice in a week where the execution time was 10 or 20 seconds? Or is it more that we're looking at queries that are being performed more often where total execution time is having a bigger impact on the DB and app?
The latter is usually the case. For queries that are being performed more often where the average execution time is likely half a second or one second, these queries have significantly more impact on your application. So, these queries are important to look at. For the rest of the queries that take too long they don't mean much; meaning they don't have a great impact on the performance of your app.
Average execution time * Execution count = Total execution time
Helpful fields to examine/decipher what's being recorded in the slow queries table are:
- Total execution time
- Example (decipher the SQL queries)
- Example URL
- everything with a regular name - without a slash / - they usually indicate background or scheduled jobs that are being executed
- everything with a slash at the beginning of the URL indicates that it's user transaction that's being performed
Additionally, you can dive into the slow query record itself to see more details:
An explain plan is a record of information about the query that is being performed; what indexes are being used, etc.
Things to look out for is in the Extra field using index. Another interesting field to look out is the Possible keys field. The Type field is also a good sign which indicates that an index was used for that table. Also interesting is to check the Rows field to see how many records are being returned:
Composite indexes are indexes that have more than one column in each index.
Basically, composite indexes contain > 1 field
Composite indexes are valid for more than one query.
You can always test these composite indexes with the script mentioned above to check whether or not the queries are faster or not.
Queries must include the leftmost prefix in a composite index. If this is not true, then the index won't have an impact on the speed of the query. For instance, if you glidequery the make field based on the model column (which is not the leftmost prefix in the composite index above), then the index won't work and as a result the query won't speeding up.
Tip:
To test new queries that are not registered in the slow queries table, a workaround is to create a new slow query record with the preferred query:
Once you have created the slow query you can create an explain plan based on that query. This will enable you to see what the system is going to do; is it going to use an index (if so, which index, what part of the index? etc.).
Query for Indexed Tables
To verify the indexing status of tables (i.e., the most relevant CMDB tables) use the following GlideRecord script in the ServiceNow Script Editor or run it in the background script:
var cmdbTables = [
'cmdb_ci_server',
'cmdb_ci_database',
'cmdb_ci_hardware',
'cmdb_ci_computer',
'cmdb_ci_network',
'cmdb_ci_application',
'cmdb_ci_service'
];
var indexedTables = [];
for (var i = 0; i < cmdbTables.length; i++) {
var tableName = cmdbTables[i];
var gr = new GlideRecord('sys_dictionary');
gr.addQuery('name', tableName);
gr.addQuery('internal_type', 'index');
gr.query();
while (gr.next()) {
indexedTables.push({
table: tableName,
index: gr.getValue('element'),
indexName: gr.getValue('name')
});
}
}
if (indexedTables.length > 0) {
gs.info('Indexed CMDB Tables:');
for (var j = 0; j < indexedTables.length; j++) {
gs.info('Table: ' + indexedTables[j].table + ', Index: ' + indexedTables[j].index + ', Index Name: ' + indexedTables[j].indexName);
}
} else {
gs.info('No indexed CMDB tables found.');
}
That's a wrap!
Resources:
Comments
Post a Comment