Three significant Cursor changes in SQL Server 2005

Many ISV applications use ODBC API Server Cursors. One source of developer confusion when coding and debugging API Server Cursors against previous versions of SQL Server (6.5, 7.0, & 2000) was Implicit Cursor Conversions, also known as cursor degradation.

These cursor conversions 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. These conditions are documented in SQL Server 2000 Books Online under the topic ‘Implicit Cursor Conversions’.

Typically, when these conversions occurred, the cursor type degraded to a ‘more expensive’ cursor type. Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant.

For example, if a FAST FORWARD ODBC API Server Cursor referenced a text column, it would typically degrade to a DYNAMIC cursor, however if the query also generates an internal work table because an ORDER BY condition is not covered by an index, then the cursor would degrade to a KEYSET cursor. If at this point the query also referenced a table without a unique index the cursor degraded further to a STATIC cursor. This is an extreme example; however it illustrates the sequence of events that previous versions of SQL Server used to degrade a cursor to a progressively ‘more expensive’ cursor type.

Sometimes these conversions caused performance problems particularly when the resultant cursor type was STATIC or KEYSET. That’s because these two cursor types required that the entire resultset (Static) or keys for the entire resultset (Keyset) be populated in a worktable before the first row can be returned to the application. Whether or not this is problematic was directly related to the number of rows the cursor must gather. When the cursor created a very large resultset this could result in the application, and thus the user, seeing slowness in 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 resultset.

NOTE: the sp_configure cursor threshold option may be able to mitigate some of this latency by enabling asynchronous cursor generation.

1) Implicit Cursor Conversions have been largely eliminated in SQL Server 2005.

For the most part, applications will now get the cursor type that they requested. There are still a very small number of cases where cursors will be converted, and they are documented in SQL Server 2005 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.

2) The SQL Server 2005 SQL Native Client ODBC Driver adds an additional performance optimization for FAST FORWARD Cursors.

FAST FORWARD-ONLY Cursors with AUTOFETCH are further optimized in SQL Server 2005 to return the first n rows immediately to the application, even if the query requires generation of a worktable. This optimization should make this already very efficient cursor type even more performant on SQL Server 2005 by reducing the latency required for opening the cursor when compared to SQL Server 2000. FAST FORWARD-ONLY are generally the least expensive cursor in SQL Server 2005 and are an optimal choice for most situations that require

read-only, forward-only cursor semantics.

3) Real-Time Cursor tracking with sys.dm_exec_cursors.

Another significant change allows SQL Server 2005 users with VIEW SERVER STATE rights, to monitor currently allocated cursors. This is accomplished using the Dynamic Management Function: sys.dm_exec_cursors. This function is one of the many new Dynamic Management Views and Functions that monitor SQL Server 2005 in real-time.

Sys.dm_exec_cursors returns information about currently allocated cursors. This information provides information about the cursor properties including cursor type, a handle back to the text of the cursor, cursor creation-time, and much more.

For the sake of completeness I would also like to mention the following Profiler Trace CURSORS Event: CursorImplicitConversion. This trace event, which also existed in previous versions of SQL Server, traces the implicit cursor conversions as they occur. That is; in Profiler the CursorImplicitConversion event will contain information regarding the requested and resultant cursor type immediately upon the conversion occurring.

By using the sys.dm_exec_cursors DMF as well as profiler you have significantly improved capabilities to diagnose cursor based applications over previous versions of SQL Server. For example, you can now look at the list of currently open cursors to detect ‘orphans’ – cursors that were not closed by the application by looking at creation date. You can determine whether the cursors are truly the cursor type that application requested. You can determine whether there are a mix of API cursors and TSQL cursors. You can also see if a KEYSET or STATIC cursor is currently being asynchronously populated, and much more.

The following code illustrates the topics;

To see Implicit Cursor Conversion on SQL 2000

Start SQL Server 2005 Profiler and point to a SQL Server 2000 Server, create a new trace with the CURSORS – CursorImplicitConversion event enabled.

Run the following script against SQL Server 2000:

use tempdb

go

drop table t1

go

Create table t1 (c1 int, c2 char(200), c3 text)

go

insert into t1 values (1, 'Smith', null)

go

insert into t1 values (2, 'bob', null)

go

-- the following simulates an ODBC generated API Fast-Forward cursor

declare @P1 int

set @P1=-1

declare @P2 int

set @P2=0

declare @P3 int

set @P3=28688

declare @P4 int

set @P4=8193

declare @P5 int

set @P5=10

exec sp_cursorprepexec @P1 output, @P2 output, N'',N'

SELECT c1, c3

FROM t1

WHERE c2 = ''Smith''

', @P3 output, @P4 output, @P5 output

go

The CursorImplicitConversions trace event should contain the following:

• IntegerData column = 16 – indicating that a fast-forward cursor was requested

• Binary column = 2 – indicating that the resulting cursor type is dynamic

This is consistent with the SQL 2000 Books Online Implicit Cursor Conversion description for ODBC API cursors referencing a text data type. A Fast-forward cursor will degrade to a dynamic cursor under these criteria.

SQL Server 2005 differences.

Use the same instructions as above but this time point both the trace and the query to a SQL Server 2005 database.

This time you should see no profiler trace event for this cursor.

Now, issue the following query against the same SQL 2005 database using the new Dynamic Management Function.

select * from sys.dm_exec_cursors(0)

go

The output should show that you indeed have an open API Fast_Forward Read_Only cursor, which is what was requested.

 [ Crossposted from https://blogs.msdn.com/mssqlisv by mssqlisv ]