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.
The 'Rebuild Indexes' window will open and it shows the percentage of fragmentation.
Click the 'OK' button to run the rebuild.
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.
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