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.properties, c.reads, c.writes, c.creation_time,
substring(qt.text,c.statement_start_offset/2,
(case when
c.statement_end_offset = -1
then len(convert(nvarchar(max),
qt.text)) * 2
else
c.statement_end_offset end - c.statement_start_
offset)/2)
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.
