This document looks at behaviours surrounding Change Orders with many Configuration Items attached:
On systems where many Configuration Items are attached to an individual Change Order, there can be very large SQL queries generated which take time to be served.
This delay occurs primarily due to a need to check the Change Schedule.
These queries have been reviewed, found to be optimised, and yet may still result in a delay to the end user in the order of tens of seconds. For this reason, the user is notified of the expected delay in serving the request with a message of “Delayed Server Response”.
The results can vary substantially depending on:
A sample range of where the issue is likely to be seen is from 100 to 600 Configuration Items. Above this number, and the symptoms will almost certainly be seen, without tuning. Below this number, and the issue may be seen from time to time. Change Orders with only 20 Configuration Items or less will most likely not experience this issue.
These symptoms all occurred together in one event on a production site.
1) The web client user experienced a delay of 45 seconds to load a Change Order with 200 Configuration Items.
2) An on-screen message was displayed to the web client user of:
"Delayed Server Response
The server is taking an unusually long length of time to process your request. Your transaction is executing, and should complete soon. When it completes, this window will refresh automatically.
If you like, you can press the Close button below to close this window and return to working with HelpDesk
For further information, please contact your CA Service Desk administrator.
Delayed Since(Thur Sep 10 14:24:25 UTC+1000 2015)
Next Server Check(Thur Sep 10 14:24:40 UTC+1000 2015) "
3) The above "Delayed Server Response" message does not "refresh automatically" and clear.
4) In the application stdlog, the following error appeared:
ERROR freeaccess.spl 41192 The number of records returned from query exceeds Max supported records, processing only limited records due to String class limitation
The application architecture is why the message does not automatically clear for the user. The Change Order opens a schedule validation call in the background. This call validates the Change Schedule with all global windows available, then with inherited windows using pre-configured values for CI Relationships and number or levels to traverse in CI hierarchy. The NX variables that have an effect on this are NX_CRITICAL_SERVICE_RELATIONSHIP and NX_CI_HIERARCHY_LEVELS.
If there are many CIs attached to the Change Order, Service Desk Manager needs to send a complex query to the database for processing, which may take more than 25 seconds to return. If the page is not ready in 25 seconds, the default value for NX_EXPECTED_WEB_RESPONSE, the above messages are generated. This SQL statement has been reviewed in depth and there is nothing that can be changed to improve the SQL Server response time within the current product design.
The reason the message is not disappearing is because this was sent as an Ajax call. The Ajax call does not support “Delayed Server Response” messages and it handles the received message simply as a regular response from the server that needs to be displayed to the user. This regular response needs to be cleared manually by the user.
Release: 14.1 and higher
There are several options on how to handle the above situation.
Option 1 – Customise the HTMPL page to remove the performance issue.
This method completely avoids the slow page load. However, it does not warn the user if the change falls outside the change window:
To completely remove the performance issue, a customisation may be made to the Change Detail page “detail_chg.htmpl” to not make the schedule validation call. The sole purpose of this call is to warn the user that the change is outside the window. If this business benefit does not outweigh the cost of the message delay, a customisation may be done.
To do this you need to make a customisation to a copy of detail_chg.htmpl within your site/mods structure. Do not alter the original file in its original location.
1) Edit the copy of detail_chg.htmpl from:
<body class="detailro" onload="loadActions();schedule_validation();"onunload="unloadActions()">
<body class="detailro" onload="loadActions();"onunload="unloadActions()">
After this update, the call to schedule_validation() will not be made, so the slowdown is avoided.
Option 2 – Tune Option Manager variables to match business need with environment.
This method ameliorates the slow page load, but maintains the functionality of informing the user if the change falls outside the change window. This method will require site testing to get an appropriate balance.
1) Perform this on a test system first.
2) If you need the warning message to be displayed set this variable to 0:
This is just for testing.
3) Adjust the values of these variables to get an acceptable performance:
ci_hierarchy_levels via Options Manager
NX_EXPECTED_WEB_RESPONSE via NX.env and NX.env_nt.tpl.
Decreasing the number of levels that “ci_hierarchy_levels” searches, e.g. from 4 to 2, will decrease the complexity of the SQL statement generated and result in a performance improvement.
Increasing “NX_EXPECTED_WEB_RESPONSE” from the default of 25 seconds to a higher number, will increase the amount of time that the query can be processed before a message is generated. Note that this will involve manual changes to the above files to all servers.
4) Restore critical_service_relationship back to its original setting.