	Product(s):	APM Implementation and Performance Management
	Version(s):	7
	Environment:	N/A
	Area:	N/A
	Subarea:	N/A

Problem

APM Foundation's database contains several indexes out of the box. These indexes are often well maintained and there are various scripts to check that the indexes are formed properly and functional. However, occasionally users and consultants may build their own Indexes to help various functions to perform quicker. These indexes are not checked by the out-of-the-box check index script.

When these Custom Indexes start to get large and fragmented, sometimes even simple views and queries may take a long time to execute when executed within APM.

Some users have even reported that launching certain views will hang APM Foundation and these hangs were found to have been the result of large fragment indexes. Often these hangs only happen in a Dev or Test Environment but not in the Production environment, this is often due to the Production Environment having more system resources than the Dev or Test Environments, and this extra resource in Prod helps the Prod environment to get through the large, fragmented indexes quicker before a timeout or hang can occur.

Solution

Large and fragmented Indexes causing performance problems and hangs when trying to access views (Tables, configurations) are often difficult to diagnose. The following steps can help bring this to light.

1. With Customizations and Custom Events turned-off. First try to replicate the steps within the problematic environment, if there is a hang, capture a Dumpfile and log files from the Application server. Next test the same steps in a non-user environment without any customizations, such as a blank install of APM or an install of APM's Training environment, let's call this a clean environment. If the problem doesn't happen in the clean environment report as an SR.

2. If there are no error messages in the error logs that appear suspicious and the problem isn't replicated in the clean environment, have the user's DBA check the database indexes. APM support can suggest the data tables to review, such as mnt.IndicatorReadings, etc. The DBA can then run the checkindex scripts from APM. If these scripts find nothing wrong, then the DBA can manually compare the Indexes for the questionable data tables, such as mnt.IndicatorReading in the user's problematic environment's database with the indexes for the same data table in the clean environment's database. The indexes in question are usually those titled IVPERF, found at the end of the list of indexes for the data table.

Example 1. Indexes in the mnt.INDICATORREADING table in the problematic database

Example 2. Indexes in the mnt.INDICATORREADING table in the clean database.

Note, in the example, the index called IVPERF_IR_DATETIME_DTTM is only found in the problematic environment, indicating that this is a custom index:

In this case, ask the user's DBA to try disabling the custom index in the problematic environment's database:

Once disabled, try to access the problematic view to see if the slow load or hang has been resolved.

If this does indeed resolve the reported issue, the user's DBA or the Bentley Technical Consultant should be contacted to determine the need for the custom index. If the custom index is not required, then the index should remain disabled and disabled in any other environments. If the Index is required, then the Bentley Consultant and DBA will need to correct the issue with the custom index. It may simply need to be rebuilt, corrected or put on a regular maintenance schedule.

Troubleshooting slow performance or hangs due to custom Indexes

Problem

Solution

See Also