Tech Tip - UIM - Why do the UIM database tables become so fragmented?

Document created by DavidM Employee on Jul 18, 2016Last modified by Melissa Potvin on Dec 17, 2016
Version 5Show Document
  • View in full screen mode

Question:

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 responsiveness and the ability of the data_engine to process the messages in its queue fast enough. 

 

Answer:

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:

MS 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.

MS-SQL-reindex1.jpg

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

Click the 'OK' button to run the rebuild.

MS-SQL-reindex2.jpg

 

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;

 

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.

data_engine-IndexMaintenance.jpg

 

data_engine-IndexMaintenance-AC.jpg

 

This is a copy of my knowledge document:

TEC1177308 UIM Why do the UIM database tables become so fragmented?

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1177308.aspx

2 people found this helpful

Attachments

    Outcomes