DX Unified Infrastructure Management

Hi All,

Database optimization job is scheduled everyday for CA UIM database by Our DB team.Earlier the job used to take 3 hours to complete.But recently it takes more than a day to complete and result of which data engine queue is getting increased. Our DB team is suspecting that there is a change in the database structure and No.of indexes got increased.But we have not done any change in the application level.

This is the observation from DB end.

Total Indexes in the database is 60,913.
Index fragmentation above 90% is 1009. These indexes highly fragmented.
Index fragmentation between 50% to 90% is 52,949.
There are 26 heap tables with huge fragmentation.

So they have come up with a solution and they want the below details

=============================================

Please check with the OEM and get us the indexes that are used frequently for daily operations and for reporting purpose, so that we can create a customized job to perform the maintenance activity. =============================================

Any idea which are the indexes(i mean the tables)are used frequently.

Regards,

Usha

So you can disable the indexing in the data_engine probe.

then have your DBA setup a nightly index job on the followig types of tables

RN_*

DN_*
HN_
CM_

S_QOS_

NAS_*

Some other KB that might help

Defragmentation of UIMSLM Database - SQL Server - CA Knowledge 

Why after following KB000011550 do I still see ind - CA Knowledge 

Hi Gene,

The Indexing in the data_engine probe is already disabled.The job which i was mentioned above was scheduled by DBA.

His query was can we exclude any of the tables below from indexing job, if this table is not used frequently.

RN_*

DN_*
HN_
CM_

S_QOS_

NAS_*

 Regards,

Usha

No none of them should be excluded.

the index job should check the fragmentation level and only execute on the tables that need it based on the fragmentation levels.

the data_engine does a re-org if it is between 5-30% and a rebuild over 30%.

You DBA should set up something similar.

If a table is not used often it will be skipped.

Hi Gene,

Thanks for the update. I will check with DBA.

Meanwhile is it fine if we execute the index rebuild weekly once and reorganize the total indexes on daily basis.

Just sharing the DBA response for your reference.

====================================================================

As discussed, please find the following indexes count against the tables as recommended by OEM.

OEM has recommended to perform maintenance activity on 60,798 indexes whereas the total indexes in the database is 60,913. At this situation, we can plan to execute this activity weekly once starting from every Saturday @ 2 AM to Rebuild & reorganize the total indexes and on daily basis will plan for Update statistics starting @ 11 PM.

=========================================================================

Regards,

Usha