UIM data_engine administration and maintenance

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

We need to get some information regarding data_engine administration and maintenance.

Environment

• Release: 20.4 or higher
• Component: UIM - DATA_ENGINE

Cause

• Guidance

Resolution

MS SQL Server Database

Memory

Normally we recommend 64GB of memory or higher dedicated to the Database Server (MS SQL Server) but your DBA should check 'memory pressure' and adjust memory accordingly.

Manually partition the largest tables...see below

Partitioning

Manually partitioning the UIM database for Microsoft SQL Server
https://knowledge.broadcom.com/external/article/34658/manually-partitioning-the-uim-database-f.html

After manual partitioning of the largest database tables is completed, the data_engine partitioning option can be enabled via the Admin Console.

Index Maintenance

Never enable the data_engine index maintenance option. Table partitioning is normally sufficient to optimize the database and maintain performance.

That said, ask your DBA to setup a DAILY job to defragment the following specific indexes during off-hours.

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;
ALTER INDEX ALL ON S_QOS_DATA REBUILD;
ALTER INDEX ALL ON S_QOS_SNAPSHOT REBUILD;
ALTER INDEX ALL ON S_QOS_DEFINITION REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_ALARMS REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;

Additional Information

Note that all of the proposed guidelines/recommendations included in this Article and document MUST be discussed with a DBA first before taking any action/making any changes to the database.

************Warning Use at your own Risk************

Please review this KB Article and all queries with your DBA before following the steps.