UIM Why do the UIM database tables become so fragmented?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Why is fragmentation a problem if the UIM data_engine probe is designed to maintain the database table indexes?

Fragmentation is often a problem severely impacting database performance and responsiveness and the ability of the data_engine to process the messages in its queue fast enough.  

Environment

Any UIM Version

Resolution

Note: it is very important to consult with your database administrator before performing these changes. 

The data_engine only maintains the indexes for the RN_QOS_DATA_* tables. The RN_QOS_DATA_* tables contain all the various QOS data so maintaining fragmentation is critical. However the S_QOS_DATA and the CM_* tables are constantly queried and fragmentation in them will have an impact on performance. 

A table index rebuild can be performed via: Microsoft SQL 

 - Load Microsoft SQL Server Management Studio

 - Expand the tree to show the table Indexes and then right click on an Index and select Rebuild.

The 'Rebuild Indexes' window will open and it shows the percentage of fragmentation. 

Click the 'OK' button to run the rebuild.

Note:

After rebuilding the indexes, you will still see indexes that have a low page count and fragmentation of 99.9%. This is totally normal as when an index has low page count it will be read from memory and the index is not used. Article Id: 113257 discusses this in more detail.

https://knowledge.broadcom.com/external/article?articleId=113257

 

MySQL:

Open a command window and login to mysql.

To rebuild all tables:

mysqldump database_name > dump.sql

mysql database_name < dump.sql

To rebuild a specific table:

mysqldump database_name table_name > dump.sql

mysql database_name < dump.sql

 

Oracle:

ALTER INDEX index_name REBUILD;

 

SQLServer:

Indexes that require defragmentation (daily)

Here is a list of tables that you CAN run a daily index defrag job upon:

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 NAS_TRANSACTION_SUMMARY REBUILD; 
ALTER INDEX ALL ON NAS_ALARMS REBUILD; 
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;

 

Additional Information

Additional Information:

To access the data_engine settings for index maintenance load the probe's configuration.   

In IM Console go to the General tab and click 'Advanced...' next to 'Index maintenance properties'.

In Admin Console select 'Database Configuration' and it is at the bottom.