CMDB dashboard 'toplist' widgets can be extremely slow to load and cause performance issues - Support and Troubleshooting

CMDB dashboard 'toplist' widgets can be extremely slow to load and cause performance issues Background

CMDB (Configuration Management Database) Dashboards introduce a number of new performance analytics widgets such as:

Top 10 Incident Generators (cmdb_toplist_incident/cmdb_service_toplist_incident/cmdb_group_toplist_incident) Top 10 Alert Generators (cmdb_toplist_alert/cmdb_service_toplist_alert/cmdb_group_toplist_alert) Top 10 Change Generators (cmdb_toplist_change/cmdb_service_toplist_change/cmdb_group_toplist_change) These widgets are primarily displayed on CMDB Dashboard pages however, as with other performance analytics widgets, can be added to any dashboard in an instance. An example of these widgets is as follows:

Unfortunately the design of these widgets means that they do not perform well in large instances (especially those with large task tables). Use of these widgets can therefore result in degraded end user experience and, in extreme situations, service affecting events such as semaphore exhaustion.

Technical issue

Performance degradation is due to the widgets being designed to display data related to all records (i.e. incident or change) since the 'beginning of time'. As a result SQL queries and associated logic must traverse the corresponding table in its entirety to build results - this approach does not scale well. Corresponding functionality is implemented within platform Java code so cannot be directly modified by ServiceNow customers or technical support.

Ultimately this leads to population of the widgets triggering extremely expensive /api/now/cmdbHealth transactions (and potentially corresponding SQL queries) such as the following:

2021-01-27 05:18:25 (895) Default-thread-14 F7D6C598DBC2E85066917E77F496193E txid=8aa74d14db06 #3740647 /api/now/cmdbHealth/taskCis/cmdb_ci/incident Parameters ------------------------- service_context=false domain=global api=api 2021-01-27 05:18:25 (895) Default-thread-14 F7D6C598DBC2E85066917E77F496193E txid=8aa74d14db06 *** Start #3740647 /api/now/cmdbHealth/taskCis/cmdb_ci/incident, user: Q822493 ... 2021-01-27 05:20:50 (704) Default-thread-14 F7D6C598DBC2E85066917E77F496193E txid=8aa74d14db06 Time: 0:02:24.235 id: xxxxxx_1[glide.26] (connpid=7008419) for: SELECT task0.`cmdb_ci` AS `cmdb_ci`, cmdb1.`name` AS `cmdb_ci_name`, count(*) AS count_of_884494749, count(*) AS count_of_1210670061 FROM (task task0 LEFT JOIN cmdb cmdb1 ON task0.`cmdb_ci` = cmdb1.`sys_id` ) WHERE task0.`sys_class_name` = 'incident' AND cmdb1.`sys_class_path` LIKE '/!!%' AND cmdb1.`sys_class_path` LIKE '/!!%' GROUP BY task0.`cmdb_ci`, cmdb1.`name`,cmdb1.`name` ORDER BY count_of_884494749 DESC ,cmdb1.`name`,cmdb1.`name` /* quintiles051, gs:F7D6C598DBC2E85066917E77F496193E, tx:8aa74d14db06e85066917e77f496199e */ 2021-01-27 05:21:59 (043) Default-thread-14 F7D6C598DBC2E85066917E77F496193E txid=8aa74d14db06 Time: 0:01:08.338 id: xxxxxx_1[glide.0] (connpid=7008393) for: SELECT count(distinct(cmdb1.`name`)) as count_of_1906846291 FROM (task task0 LEFT JOIN cmdb cmdb1 ON task0.`cmdb_ci` = cmdb1.`sys_id` ) WHERE task0.`sys_class_name` = 'incident' AND cmdb1.`sys_class_path` LIKE '/!!%' AND cmdb1.`sys_class_path` LIKE '/!!%' /* quintiles051, gs:F7D6C598DBC2E85066917E77F496193E, tx:8aa74d14db06e85066917e77f496199e */ ... 2021-01-27 05:21:59 (068) Default-thread-14 F7D6C598DBC2E85066917E77F496193E txid=8aa74d14db06 EXCESSIVE *** End #3740647 /api/now/cmdbHealth/taskCis/cmdb_ci/incident, user: Q822493, total time: 0:04:05.210, processing time: 0:03:33.179 , total wait: 0:00:32.031, session wait: 0:00:32.031, SQL time: 0:03:32.575 (count: 8), business rule: 0:00:00.001 (count: 1), source: 162.44.150.10 , type:rest, method:GET, api_name:now/cmdbHealth, resource:now/cmdbHealth/taskCis/cmdb_ci/incident, version:Default, user_id:fcde31a04f68de00ac4e48428110c7ad, response_status:200

If an instance shows evidence of slow /api/now/cmdbHealth/* transactions it is highly likely that the instance is being impacted by this issue.

Workaround

Whilst it is possible to use a variety of means to identify dashboards containing one or more of these widgets then manually edit dashboards to remove this is not ideal as, whilst it may give short term improvements, does not prevent users from adding the widgets to dashboards once more and causing a repeat of issues.

Instead a better solution is to modify the widgets definitions such that any 'toplist' widgets are not populated with results and instead show a message indicating that they have been disabled. This can be achieved via the following modifications and will prevent issues regardless of which dashboard contains/is loading the widgets:

Log into the instance as a user with 'admin' role Navigate to 'System UI -> Widgets' (sys_widgets_list.do) and search for widgets with name as follows: CMDB Widgets CMDB Service Widgets CMDB Group Widgets Open each widget in turn, modify the render() function in the corresponding script to return a descriptive string if a 'toplist' widget is being loaded, then save the corresponding record i.e.: CMDB Widgets - modify the code at lines 326 - 330 from: // If type starts with CMDB scorecard... if(type == 'cmdb_scorecard' || type == 'cmdb_chart' || type == 'ci_scorecard' || type == 'cmdb_report_result' || type == 'cmdb_report_score' || type == 'cmdb_report_relation' || type == 'ci_list' || type == 'ci_timeline' || type == 'cmdb_toplist' || type == 'cmdb_task_table' || type == 'cmdb_hierarchy') {

To:

// Disable CMDB toplist widgets due to performance concerns (fixed in Quebec via PRB1431346) if (type == 'cmdb_toplist') { return 'Widget currently disabled due to PRB1431346' } // If type starts with CMDB scorecard... if(type == 'cmdb_scorecard' || type == 'cmdb_chart' || type == 'ci_scorecard' || type == 'cmdb_report_result' || type == 'cmdb_report_score' || type == 'cmdb_report_relation' || type == 'ci_list' || type == 'ci_timeline' || type == 'cmdb_toplist' || type == 'cmdb_task_table' || type == 'cmdb_hierarchy') {

CMDB Service Widgets - modify the code at lines 68 - 69 from: // If type is valid if(type == 'cmdb_service_scorecard' || type == 'cmdb_service_toplist' || type == 'cmdb_service_dashboard_class_hierarchy') {

To:

// Disable CMDB toplist widgets due to performance concerns (fixed in Quebec via PRB1431346) if (type == 'cmdb_service_toplist') { return 'Widget currently disabled due to PRB1431346' } // If type is valid if(type == 'cmdb_service_scorecard' || type == 'cmdb_service_toplist' || type == 'cmdb_service_dashboard_class_hierarchy') {

CMDB Group Widgets - modify the code at lines 68 - 69 from: // If type is valid if(type == 'cmdb_group_scorecard' || type == 'cmdb_group_toplist') {

To:

// Disable CMDB toplist widgets due to performance concerns (fixed in Quebec via PRB1431346) if (type == 'cmdb_group_toplist') { return 'Widget currently disabled due to PRB1431346' }

// If type is valid if(type == 'cmdb_group_scorecard' || type == 'cmdb_group_toplist') {

Once complete the widgets will still be displayed on dashboards however will not be populated with results and therefore will not generate problematic/expensive /api/now/cmdbHealth transactions - for example:

Note that:

Any modifications to widgets must be fully tested in a sub production ServiceNow instance prior to changes being made in production An update set containing the above modifications (CMDB Widget Fixes (PRB1431346).xml) is attached to this article - this can be applied as follows: Download the update set Log into the instance as a user with 'admin' role and navigate to 'System Update Sets -> Retrieved Update Sets' (sys_remote_update_set_list.do) Right click any column header and select 'Import XML' - in the resulting page select the downloaded XML file and click 'Upload' A retrieved update set with name 'CMDB Widget Fixes (PRB1431346)' should be shown with state of 'Loaded' Open the update set and click the 'Preview Update Set' UI action followed by the 'Commit Update Set' UI action To back out the above changes the two lines of additional code added to each widget definition can simply be removed, i.e. for 'CMDB Widgets' remove the following from the corresponding sys_widgets record script (starting at line 326):

// Disable CMDB toplist widgets due to performance concerns (fixed in Quebec via PRB1431346) if (type == 'cmdb_toplist') { return 'Widget currently disabled due to PRB1431346' }

For assistance with the content of this article please open a case with ServiceNow Technical Support

Fix for the issue:

This issue is tracked via PRB1431346 and is addressed in the Quebec (and later) releases of ServiceNow. Once on these releases the widget definitions (as modified above) should be returned to their out of box configuration.