Smart-clients login times out due to large fragmented indexes

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

Problem

The production system is down right now due to issues with the server manager. The issue on the server manager is that it can’t seem to get the latest server status from the database

"2018-06-19 10:19:32.7439 [Host=xxxxxxx1] [Computer=xxxxxx1] Problem encountered getting user information from database for severSessionID: xxxxxx2-IvaraServer-xxxx-xxxxxxxx-xxxx-xxxxx-xxxxxxxxx"

All servers are listed as stale on the server manager.

Previously this was working fine. We have tried restarting the APM windows services already as well as restarting the server manager we page. We tested the server manager configuration and database connection, all seem to be normal there. Thick client seems to start up ok so it doesn’t appear to be an issue on preventing the application from starting up.

After much troubleshooting. We found that the oq.connection_reg and oq.app_lock indexes were extremely large (100000 pages) and highly fragmented (99%+). The users were timing out on getting connections with the smart client because of the slow returns on the connection reg index. Restarting the IIS Server did not help and stopping all services did not help.

Solution

DBA rebuilt both the oq.connection_reg and oq.app_lock reg. We then restarted the IIS Server, launched the server monitor and then restarted the application server. Once it was of status OK we then successfully launched a smart client connection. After which we launched the remaining 3 services (1 ODC, 1 Action Scheduler/job service provider and 1 Job service provider.
DBA expert recommended daily rebuild of the two indexes.

Users that perform a large amount of dataloading regularly have a greater need for regular maintenance of these indexes.