No this isn't a sales pitch to go out and buy your instance a gym membership. You should, however, consider whether you are making your database work unnecessarily hard. Having an 'active' column on a table enables the database to quickly assess whether a record is of interest or not - work smart, not hard!
Consider the 'incident' table. Users are not only constantly creating new incidents, but support teams and end users are also interested in reviewing their current workload or ongoing incidents which they have opened. This information might be presented in a list or it may be shown in a gauge, perhaps grouped by the current state of the incident (new, work in progress, solution proposed etc).
Typically users are only interested in 'active' records (New / Work in Progress / Solution Proposed)
By ensuring the 'active' column is being updated correctly (active = false when an incident is closed) and having a database level index on the 'active' column, the database can quickly return the active rows and discard the in-active ones. This will become increasingly noticeable as the data set increases.
One example I saw had an incident table with in excess of 6 million rows. The service desk had an expected back log of around 150,000 active incidents (either actively being worked or pending problem), but the performance of their instance was progressively degrading. It turned out that due to a customization of their incident autoclose business rule, incident tickets were not updating their 'active' flag to be false when they were closed out.
As a result of this, the database did not use the index in place on the 'active' column and was having to perform a full table scan every time a user accessed a list or gauge of incidents. With the assistance of ServiceNow Technical Support, we were able to identify the reason tickets were not being moved to be 'inactive' (and helped fix it). Additionally, a data clean-up exercise was undertaken to restore good performance to the instance.
Here is what to do if you are experiencing delays in viewing incident lists/gauges or reports:
- Check whether the 'active' column is used in the list filter / report definition. Remember that a positive assertion is more performant (eg: active = true is more efficient than active != false).
- Confirm that the 'active' column is indexed. Do this by going to System Definition > Tables & Columns.
- Verify that the 'active' flag correlates with the ticket 'state' (are 'closed' tickets marked as 'inactive').
Caution: If you determine that you need to perform a data clean-up exercise, consider any business rules or workflows which will be triggered when you update records en masse. You will almost certainly want to update records in batches. Be mindful of any integrations which may not cope well with a flood of updates.
Also be wary of making queries which list or count up "My 'old' work." I have seen instances which have a module available to users which will display them all the tickets which they ever assigned to. This may seem like a relatively harmless feature to offer your users. It is worth remembering however that this view will get progressively slower over time, as more tickets are assigned to that user and the volume of closed ('inactive') tickets grows. User expectation should be set accordingly.
Perhaps more importantly, make sure you are not displaying a module count in the navigator for this module. Forcing the database to perform an inefficient count every 30 seconds (how frequently the navigator refreshes) for every logged in user is going to have a negative impact across the board. While displaying module counts in the navigator might seem like a nice usability feature to offer your users, from the DB perspective this is not a zero-cost exercise. I strongly recommend careful consideration to what true value displaying this information is going to provide your business, bearing in mind that by clicking in to the module, you are given a count in the list view (showing x of y records).
