Easysoft ODBC-ODBC Bridge

Why is my NT OOB Server using a lot of CPU when my ODBC connections go to MS SQL Server

Article:
00983
Last Reviewed:
27th October 2008
Revision:
2

Obviously, there could be many reasons for this. It should be remembered that all the CPU used and showing in Task Manager for esoobserver includes the CPU used by the driver manager and any ODBC drivers you are using. If the OOB Server is running threaded (the default) then CPU usage SEEN in Task Manager will be greater than if the OOB Server is running MultiProcess. This is simply due to the fact that when in MultiProcess mode a new process is created to handle each incoming connection, when that connection is closed the OOB Server process handling it will die and you cannot see the CPU usage for a dead process.

The most common scenario where the OOB Server appears to be using a lot of CPU is when it is handling a connection to MS SQL Server, a result-set has been generated but not consumed/closed and an attempt to create a new result-set is made. In this case, the MS SQL Server ODBC driver appears to spin-wait, consuming a lot of CPU. We have seen this ourselves on our web site and a few customers have hit the same problem - it is always down to not closing the result-set. The example below is a real example which occurred in a PHP script, it is provided in the hope it will help others avoid this problem.

The table in the MS SQL Server database contained a column defined as TEXT. One row in the table contained one of these text columns with over 7K in it but PHP's odbc.defaultlrl (default long read length) was set to the default of 4K. A query was issued which retrieved all of the columns from this table. PHP bound columns for most of the columns in the table but used SQLGetData to retrieve the TEXT column as it was defined as SQL_LONGVARCHAR. The problem arises because when PHP's odbc_result() was called to retrieve the result-set data, PHP passed a 4K buffer which was insufficient, MS SQL Server returned 4K and SQL_SUCCESS_WITH_INFO and hence the result-set is not consumed (ODBC says you can call SQLGetData multiple times on the same column to retrieve the data in chunks). The PHP script then goes on to issue another query which hangs. At this stage the OOB Server on the NT machine showed a lot of CPU usage which was tracked down to spin-waiting in the ODBC driver. This problem can be avoided very simply by closing any result-set you generate, in PHP that means calling odbc_free_result(), in ODBC terms it is a call to SQLFreeStmt(SQL_CLOSE).

In every case high CPU usage in the OOB Server has been reported to us, the connection was always to MS SQL Server and always resulted from the result-set not being closed.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.