Best Practices to manage and maintain Task table

GTSPerformance · ‎01-13-2021

< Previous Article		Next Article >
Viewing statistics (xmlstats.do & stats.do) for all my nodes?		Demystifying table rotation, extension, and table cleaner

Overview

This guide is written by the ServiceNow Technical Support Performance team (All Articles). We are a global group of experts that help our customers with performance issues. If you have questions about the content of this article we will try to answer them here. However, if you have urgent questions or specific issues, please see the list of resources on our profile page: ServiceNowPerformanceGTS

The task table is one of the core tables in the ServiceNow platform. It provides a series of standard fields used on each of the tables that extend task, such as the Incident [incident] and Problem [problem] tables. In addition, any table which extends task can take advantage of task-specific functionality for driving tasks. By default the task table uses the 'Table per Hierarchy' extension model on most instances. It is usually one of the biggest tables by size and the busiest table by usage.

Below are some of the best practices to manage/maintain the “task” table:

Active task records < 10%

In a typical ServiceNow instance approximately 95% of all operations are related to the task table in the underlying database. Consider that task is the mother of all incidents, changes, problems, service catalog requests and custom applications. Good performance of task table is the foundation of optimal performance within the ServiceNow platform.
Due to its importance the task table was optimized for performance out-of-the-box (OOB) with a number of selective database indexes being added against the table. To ensure these indexes provide maximum benefit the data in the task table should follow expected patterns meaning that indexes can be efficient.
A good example is the ‘Active’ column (which is included in a number of OOB database indexes against the task table). In a production environment, we expect less than 10% of all tasks to be ’active’, so when we look for information on 'active' records, we can immediately / quickly ignore the remaining 90% of 'inactive' tasks for optimal performance. Having a very large set of 'active' records significantly reduces the effectiveness of database indexes involving the 'active' column.
The OOB 'mark closed' business rule takes care of marking a task record 'inactive'(i.e. active=false) when a record is moved to one of the closed states. At times, task records would be moved to one of closed states but they remain 'active' (active = true) state instead of 'inactive' (active = false). For such cases, please review dictionary override configuration for “closed states” for “state” field. [Community] Understanding tasks table "closed" states.

Timeboxing queries

Timeboxing is the idea of only returning data sets from the task table which have some kind of data/time field (which has an underlying OOB database index) falling into a particular range. Such queries are recommended to efficiently retrieve data from a large table such as task. Queries that access a slice of data set, using some kind of tactics listed below, are performance efficient.
- Timeboxing restriction / range should be included in the query
- Data returned corresponds to only the period of time you are interested in, not all.
- Choose a ‘timeboxing’ field that has an appropriate index, then the queries can be extremely efficient.
- Query for ‘delta’ data instead of entire range (i.e. show me the records which have changed since I last checked)
Some of date / time fields to use for selective filters are listed below. These fields can be used appropriately to build optimal filters.
- opened_at: Recorded at the time the user clicked “New” (before DB insert)
- sys_created_on: Recorded at the time the user clicked “Submit” (@DB insert tx)
- sys_updated_on: Recorded when a record is inserted/updated (never null)

Task Table growth

Over time the 'task' table hierarchy could grow in terms of total number of records as well as total number of child tables extended from 'task' (meaning that the physical task table becomes very complex). In some cases this might cause errors due to MySQL/MariaDB limits for a single table being exceeded (i.e. max row size, max columns, and so on) or it may lead to performance degradation. In these scenarios a technique known as 'hybrid flattening' can be considered to de-flatten the task hierarchy and move selected child tables out to their own physical tables. Ultimately ServiceNow administrators should exercise caution when designing/creating new custom tables which extend the task hierarchy (i.e. these should be kept to a reasonable minimum and be well designed) however ServiceNow support can also make recommendations around hybrid flattening when necessary. A combination of good design and selective hybrid flattening (if required) can help manage organic growth of the task table.
Archiving is another approach to manage the size/growth of the task table. It moves user defined sets of historic data which is no longer needed for day to day operations from primary tables out to a set of archive tables. This can help keep the task table lean/performant whilst avoiding the complete loss of historic data. Please see [SN Doc] Data Archiving for more details. Also see [Community] Demystifying table rotation, extension, and table cleaner.

Column Add Considerations

Option to Consider	Ingest Performance	Display Performance	Task Query Effect	Can Sort	Can Search	Can Group	Workflow	Business Rules	Reporting
Directly on task	Medium	Small	Medium	Yes	Yes	Yes	Direct access to fields on GR	Direct access to fields on GR	Yes
Normalized referencing task	Medium/small	Small	None	With DB View, or in related list	With DB View, or in related list	No	Separate query	Separate queries/updates	With DB View, or on related table
Normalized referenced by task	Large/medium	Small	Small/None	Yes	Yes	Yes, potentially w/ performance impact	Dot-walking available	Can get referenced records & do updates	Yes
Normalized m2m	Small	Medium	Small/None	With DB View, or in related list	With DB View, or in related list		Separate query	Separate queries/updates	With DB View
Stored as variables	Large/Medium	Medium	Small/None	No	Keyword only	No	Direct access to variables on GR	Yes	Yes with limitations (special reports on variables exist)
Stored in journal	Small	Small	Small	No	Keyword only	No	Method call can get journal entries, might need processing	Yes	No
Metrics (special case)	Small	N/A	N/A	On DB view	On DB view	On DB view	No	Implemented using BRs	Special reports exist, on DB views

Any time data is stored in a multiple physical database tables (for example when using hybrid flattening or querying task but ordering on data from another physical table such as sys_user) the resulting SQL query will contain a JOIN condition. Doing the join, querying and sorting data can be slow, depending on the size of the datasets, how selective the query can be, and the indexes on both tables.

A Database View can be defined to make these kinds of joins easier and more efficient. You would then search the DBView instead of either of the original tables. This is a requirement for many-to-many relationships since you can't dot-walk through that relationship (neither Task nor non-Task directly references the m2m table)

Question/Answer tables (i.e. variables) store values, not display values. Some specific accommodations are made in the Reporting UI for querying for Service Catalog variables and Task Questions:

https://docs.servicenow.com/bundle/quebec-now-intelligence/page/use/reporting/concept/report-on-ques...

https://docs.servicenow.com/bundle/quebec-now-intelligence/page/use/reporting/concept/rep-service-ca...

< Previous Article		Next Article >
Viewing statistics (xmlstats.do & stats.do) for all my nodes?		Demystifying table rotation, extension, and table cleaner