Poor performance of database I/O in Control Center set to use Globalized MSSQL Database

Troubleshooting

Problem

Control Center was set to use a Globalized Database with MSSQL causing the poor performance of the database.

Symptom

Timings for writing to the database are very poor in Control Center to the MSSQL database.

Cause

When configCC was ran the option to Globalize the database was taken.

Environment

MSSQL Database being used in Control Center

Diagnosing The Problem

Check the engine log or the JDBCService.xml for SendStringParametersAsUnicode=true.

The InstallationInfo.properties will also show CCENTER_MSSQL_GLOBAL=true.

To confirm database performance issues are occurring you may run the following text-based searches in the IBM Sterling Control Center Monitor Engine logs:

    CCTR135I
    CCTR138I
    excessive
    inordinate

Messages logged with this text appear as follows:

    CCTR135I Time to insert monitored server data into EVENTS database table was: 436776 milliseconds
    CCTR138I Time EventMonitor took to retrieve data from EVENTS database table was: 1254366 milliseconds
    updateQueuedProcesses query time excessive
    updateQueuedProcesses query time (t1-t0) excessive - Total time
    ProcessSummaryWorker.getEvents() logic took an inordinate amount of time!
    Time to update nnn processes as ended was excessive.
    ProcessSummaryWorker took an inordinate amount of time to summarize file transfer
    ProcessSummaryWorker took an inordinate amount of time to summarize Process
    ProcessSummaryWorker took an inordinate amount of time to commit database changes to CC_PROCESS and CC_FILE_TRANSFER

Resolving The Problem

NOTE: Globalization is only need for languages using multi-byte characters. If you really have this type of data you should use a different database than MSSQL.

If you don't require multi-byte characters support you can undo the Globalization of the MSSQL Database following these steps:

1.   Stop all EPs
2.   Update the /<install>/conf/services/system/JDBCService.xml url value to include “;SendStringParametersAsUnicode=false”
3.   Update /<install>/conf/InstallationInfo.properties file the value for CCENTER_MSSQL_GLOBAL to be false
4.    Modify the table X_PROPERTY using the SQL command

UPDATE X_PROPERTY SET PROP_VALUE = 'false' WHERE PROP_NAME = 'CCENTER_MSSQL_GLOBAL'

Note that each EP's JdbcService.xml and InstallationInfo.properties will have to be updated.

Related Information

Diagnosing and Tuning Database performance

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSJC3O","label":"IBM Sterling Control Center Monitor"},"ARM Category":[{"code":"a8m0z000000cwVwAAI","label":"DATABASE"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips