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

Problem Description

An APM user noticed several long runtime queries suddenly starting to exceed timeouts for actions such as Remote Download Package generation.

Solution

Upon review of the issue, it was found that the query itself was inefficient however, of greater influence to query time, the database statistics had not be run in several years. Databases require regular maintenance. Once the maintenance was done (running statistics on the db) the query time reduced by a factor of approximately 90%.

The following advice is given to all users at the start of all our implementations/projects but is not always followed long term as database ownership often changes hands:

1. Don’t add, modify or remove indexes without getting the proper instructions from Bentley support.
2. Do recalculate statistics regularly. Rebuild indexes when needed.
3. Do update the compatibility level on your database when you upgrade to new versions of SQL and APM.
4. Do not set auto-growth sizing in small increments. Do regular database resource monitoring and grow the database when statistics are run and indicates resources are within 10% of being exceeded.

The above advice are basic "rules of thumb" please discuss maintenance plans with your professional, inhouse dba’s as they are in far better position to assess your needs.