Performance Analytics and Table Rotation - Support and Troubleshooting

Performance Analytics and Table Rotation Issue  Overview Table Rotation can sometimes have unexpected results in conjunction with Performance Analytics. This article will describe the cause of some of these discrepancies.

The Issue There is currently a known issue in which having a table configured for Table Rotation will cause any Performance Analytics Data collections on those tables to be unable to access any of the secondary shard tables of the root parent table. Thus, the counts and record lists returned from these data collections will often be much smaller than what might normally be expected. The default rotation duration setting for most tables in a Table Rotation scheme is from several hours to a week, and thus, any records older than that rotation periods (i.e. 7 days) will no longer be found in the base table but instead be found in the individual shard table, which, in the current versions of ServiceNow, are not able to be collected from through the Performance Analytics system.

This issue will usually appear in which it is noticed that historical data for a specific Indicator suddenly stops collecting data or collects much less data than is normally expected for that Indicator. This is usually be found soon after Table Rotation has been activated on the system. It may also be noticed when attempting to create a new Indicator and the data being collected for associated Jobs is only getting data related to very recent records.

To check if this is, in fact the case, first determine which table the Indicator is collecting data on. This will generally be the Facts Table for the Indicator Source from which the Indicator used by the Data Collection job is obtaining it's data. After verifying the specific table names that are part of the Indicators which are apparently not obtaining the necessary data, check the Table Rotation configuration for verification that the table is, in fact, part of the Table Rotation configuration.

To do this, first browse to the following location on the instance: System Definition -> Table Rotations .

Filter the resulting list of table names to locate the Name of the table found in the steps above which corresponds to the Facts Table from the Indicator Source record. If the table is not found in the list, that table is not part of Table Rotation. If the table is found in the list AND the Initialized field of it's corresponding record is set to true , than that table is part of the Table Rotation scheme as configured on that instance. You can review the record corresponding to that record for the number of days per rotation ( Duration field) and rotation type ( Type field). A Type field value of Rotation indicates that only data corresponding to the number of days in the Duration field, multiplied by the number in the Rotations field is retained on the instance. This data is retained in a number of secondary shard tables. Each of these tables will usually, at the Database level, consist of the table name with an extension of "xx", where xx is a two digit number corresponding to the table rotation number (such as 02 or 15). If the value fo the Type field is instead set to Extension , then the data is retained indefinitely, over the course of any number of necessary tables. A new table shard would generally be created in a time period equal to the duration field of the corresponding Table Rotation record. Currently, the Performance Analytics system does not allow historical data collection with either type of Table Rotation enabled for the source table associated to the record type.

Thus, in the current version of the product collection of Performance Analytics data on a rotated table is not supported, however the may certainly change as new versions of the system are released.

Additional Information The following Docs site article describes how to configure Table Rotation for a specific table on an instance:

https://docs.servicenow.com/csh?topicname=t_ApplyTableRotation.html&version=latest