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

• DX UIM 23.4.*
• MS SQL 

 

Cause

Guidance / Documentation clarification

Resolution

MS SQL Server Database Recommendations:

• • 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
    
    How to manually partition the UIM database for Microsoft SQL Server
    
    
    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.
    
    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;

 

Attached to this KB is a PDF GUDE: UIM Database Best Practices for MS SQL Server

Additional Information

Notes ************Warning Use at your own Risk************:

• 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.
• Review this KB Article and all queries with your DBA before following the steps.