When providing application end users access to SQL tools for querying your OpenEdge database, this provides users the ability to create custom, many times one-off, reports which may or may not use efficient indexing strategies for resolution.

When the queries are particularly large or inefficient they will consume a lot of CPU on the Database Server or write an enormous amount of data to disk in temp files in order to resolve. 

In order to resolve these problems when they occur you may take one or more of the following actions:

• Use Disk and CPU monitors to check for unusual or high levels of usage.
• Once identified, disconnect the users from the database.
• When the above leaves an orphan _sqlsrv process consuming resources it is sometimes necessary to restart the database.

What proactive steps can be taken to limit the impact of a bad report, instead of being reactive and waiting until the alerts are going off?

 

Below are some solutions that can be used individually or all together in order to avoid allowing bad queries to overwhelm a production database.

Resources:

• Over-allocate CPUs and memory in order to increase available resources.
• Use high -B value when starting the database to keep as many buffers in memory in case an inefficient query is executed in an update.

Replication:
Don't let reporting users run queries against your production database.

• Use OE Replication and have your users query the replication target database instead of the original production.
• Use OpenEdge Pro2 and likewise have your users query the MS SQL or Oracle target database.

Set Timeouts:
Set the ODBC query timeout property described here: Long running SQL queries are timing out, to prevent SQL queries from running more than a certain amount of time.

Note that the behavior documented in SQL-92 query timeout settings do not work in all cases discusses the fact that the query timeout can get reset periodically. 

This behavior has changed in versions 11.7.9 and 12.2.4 to be more responsive, by segmenting the process better, and not resetting the query timeout property in between segments.