PeopleSoft ODBC API Server Cursor

 PeopleSoft applications use ODBC for database connectivity to the SQL Server database. The ODBC API server cursors are used extensively throughout the PeopleSoft application suite for result set processing.

In previous versions of SQL Server, there were several known issues with ODBC API server cursors.

In SQL Server 2000, the ODBC API server cursors could change the cursor type requested by the application to another cursor type based on a well-defined list of conditions; such as whether the query contained references to text, ntext, or image columns, and numerous other documented conditions. This is defined as cursor conversion, also known as cursor degradation.

Typically, when these conversions occurred, the cursor type degraded to a more expensive cursor type. Generally, a fast forward–only cursor performs best, followed by dynamic cursor, keyset-driven cursor, and finally, the static cursor is the lowest performing cursor.

Sometimes these conversions caused performance problems particularly when the resulting cursor type was static or keysetdriven.

This is because these two cursor types required that the entire result set (static) or keys be populated in a work table before the first row could be returned to the application. Whether this is problematic is directly related to the number of rows the cursor must gather. When the cursor created a very large result set, the application slowed when retrieving the initial set of rows. This can be problematic for applications that tend to join many tables with many target rows but only plan to use a small number of rows from the beginning of the result set.

To improve some of the above-mentioned issues and to further enhance the API server cursors, two enhancements have been made to the API server cursor model in SQL Server : implicit cursor conversion and real-time cursor tracking with dynamic management views.

Implicit Cursor Conversion
In SQL Server , for most of the cases, the requested cursor type will be the resulting cursor type. Cursor degradation is now very limited and will happen in very few cases, as documented in SQL Server Books Online under the topic “Implicit Cursor Conversions (ODBC).” However, the fact that the majority of cases that caused cursor degradation to occur have been eliminated should result in a more consistent application experience when using ODBC API server cursors in SQL Server .

Because this is an optimizer improvement in SQL Server , no manual steps are required by the developer to leverage it.

Real-Time Cursor Tracking with Dynamic Management Views
Because PeopleSoft applications widely use ODBC API server cursors, dynamic management views in SQL Server can be effectively used for cursor tracking and monitoring.

The sys.dm_exec_cursors dynamic management object is a specific cursor-related dynamic management view. Querying this dynamic management object will return information about the open cursors in the database, such as:

• Cursor name
• Properties for declaration interface, cursor type, cursor concurrency, cursor scope, cursor nesting level

• Sql_handle – handle to the text of the cursor that could be used with the sys.dm_exec_sql_text(sql_handle) view to return the exact cursor text
• Creation time
• Reads or writes performed by the cursor
• Fetch buffer size
• Others as documented in the SQL Server Books Online topic, “sys.dm_exec_cursors.”

In the following example, the sys.dm_exec_cursors dynamic management view can be joined with the sys.dm_exec_sql_text to find the session_id, properties, reads, writes, creation time, and the exact cursor code executing currently, for all cursors:

select c.session_id,, c.reads, c.writes, c.creation_time,
(case when c.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else c.statement_end_offset end - c.statement_start_
as cursor_text
from sys.dm_exec_cursors (0) c
cross apply sys.dm_exec_sql_text(c.sql_handle) qt

To find information about a specific cursor, replace the 0 with the session_id (SPID) of the session that holds the cursor as the input parameter for the sys.dm_exec_cursors dynamic management view.

By using the sys.dm_exec_cursors dynamic management view you have significantly improved capabilities to diagnose cursorbased applications over previous versions of SQL Server. For example, you can determine whether the cursors are truly the cursor type that application requested. You can also see if a keyset or static cursor is currently being asynchronously populated, and so on.