It is important to monitor the performance of servers in any application and with CA-SDM this is no different.
There are native tool utilities and some options that can help identify and correct performance problems.
Unfortunately there is no "universal cake recipe" because depending on the characteristics of your installation and your usage scenario, the settings may be different. And it is precisely for this reason that it is important to understand some fundamental concepts used in diagnosis.
In this post I will address one of the major performance bottlenecks that can significantly compromise the user experience:
Access to the database (DBMS).
When CA-SDM services are started, a number of database agents are instantiated in the memory of all servers in the application infrastructure. These agents can be easily identified as "sql_agent_nxd.exe" processes.
They are options that determine the amount of agents available and consequently the amount of "slots available for connection" that their users will have to manipulate information in the DBMS.
The "options" below determine the minimum and maximum number of database agents managed by the system.
NX_MIN_DBAGENT
NX_MAX_DBAGENT
By default, a minimum of 6 and a maximum of 6 database agents are used.
Consider these agents as those used for generic queries to any table in the application.
But there are also specific database agents that can be allocated to very manipulated tables in the tool, and there are other specific options that are used for this purpose identified with the prefix 'NX_VIRTDB_AGENT'.
By default, there are 11 unique database agents configured in the tool, but others may be included if necessary.
Each database agent "costs" an average of 6 MB of RAM on each server. Therefore the appropriate number for these agents will depend on your current usage scenario. The more database agents available, the faster database transactions will be processed and the reverse will be true as well. If there are no agents available, the transaction will be queued and you will have to wait for any agent available.
We can then assume that the availability of database agents on infrastructure servers will directly influence the response time each transaction will take to process. In short: it means to us the phrase "The tool is slow and the user is dissatisfied".
But are these native configurations meeting the need of your business?
The log message "SIGNIFICANT sqlclass.c 1043 The following statement took 3056 milliseconds" is common for you?
1) How do I know the amount of database agents that is set on my system?
Just run the "nx_env DBAGENT" utility
The following information will be displayed (for example):
NX_MIN_DBAGENT = 6
NX_MAX_DBAGENT = 6
2) How do I know if this setting is suitable for my system?
Just run the "PDM_VDBINFO" utility.
The following information will be displayed (for example):
=========================================
VDBINFO invoked at 09/30/2018 19:56:58
=========================================
Min Config Agents = 6
Max Config Agents = 6
Max DB Agents = 6
Tgt num idle = 2
Num Agents running = 20
Num Agents starting = 0
Num Requests pending = 0
Current idle = 3
...
This utility runs in real time and must run on each server used in the installation and preferably at different times of system use in order to accurately identify any configuration problems.
The two most important information for this utility are:
Tgt in idle: Indicates a recommended goal of free agents:
Current idle: Indicates the current amount of free agents.
The recommended goal is calculated by looking at the NX_BUSY_AGENT_THRESHOLD configuration parameter that has a default value of 70%.
Shortly after the summary of the execution of this utility the current status of each database agent is displayed and then some statistics of 'updates' and execution queues in tables.
3) How can I change the configuration of database agents?
Just run the commands below directly on the primary server / background console of your installation.
// To modify the MINIMUM number of agents
pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MIN_DBAGENT -v XX
pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MIN_DBAGENT -v XX -t
// To modify the MAXIMUM number of agents
pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MAX_DBAGENT -v XX
pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MAX_DBAGENT -v XX -t
Where XX defines the number of agents desired and the '-t' option ensures that these modifications will continue even if a product reconfiguration (pdm_configure) is performed.
You must restart all servers for these changes to take effect.
As I said earlier, it is important to run this utility regularly on CA-SDM servers to identify the need to set up more database agents and also identify queries that are being made and are being "suspended" during their execution.
A query gains SUSPENDED status when there are no database agents available, or when the agent occupancy percentage has reached the threshold defined in the system (NX_BUSY_AGENT_THRESHOLD).
Finding this situation occasionally is normal, BUT it is indicative of performance issues if it is customary in your system.
Misconfigured archival processes that run or invade business hours are a very common cause of this situation.
Another common cause is stored queries (stored_queries) that make SELECTs very expensive in the system used in the queue manager or even in performance metrics (KPI's). It is good to note that there are queue "nodes" that are processed every 120 seconds even when they are not expanded by the user (root nodes).
To identify SQL queries being triggered by the tool on all database agents without having to trigger your "DBA", use the DB_REPORT utility.
Through the information obtained with this utility it is possible to list which SQL's are problematic and are compromising the performance of the system. You may have to use some third-party tool to analyze the similarity of these queries and build your TOP10_BAD_QUERIES.
If you identify that your problem is related to custom tables, it is likely that you will need to create new database indexes. But only do this with the CA support orientation.
Another procedure that can optimize access to database records is the eventual execution of the SHRINK process in your DBMS. This process organizes the database data so that the data can be accessed continuously, not fragmented. This is especially interesting when there have already been many archiving / deletion processes in the tool's MDB base.
Enjoy.
Originally published in the CA Service Desk Manager (Brazil) group by Daniel Becker Bighelini