Asynchronous Population
Microsoft SQL Server 2005 offers a performance optimization of populating large keyset-driven or static cursors asynchronously. Keyset-driven and static cursors use work tables built in tempdb. Keyset-driven cursors use the work table to store their keyset, the set of keys that identify the rows in the cursor. Static cursors use work table to store the rows comprising the cursor. If the SQL Server query optimizer estimates that the number of rows returned in a keyset-driven or static cursor will exceed the value of the sp_configure cursor threshold parameter, the server starts a separate thread to populate the work table. Control is immediately returned to the application, which can start fetching the first rows in the cursor instead of having to wait until the entire work table has been populated before performing the first fetch.
There is some extra overhead associated with populating a cursor asynchronously. It is more efficient not to populate small cursors asynchronously, so the sp_configure cursor threshold value should not be set too low. Reserve the use of asynchronous population for large cursors.
Note
SQL Server 2005 does not support generating keyset-driven or static Transact-SQL cursors asynchronously. Transact-SQL cursor operations such as OPEN or FETCH are usually batched, so there is no need for the asynchronous generation of Transact-SQL cursors. SQL Server 2005 continues to support asynchronous keyset-driven or static application programming interface (API) server cursors where low latency OPEN is a concern, due to client round trips for each cursor operation.
The @@CURSOR_ROWS function reports the number of rows in a cursor. If you select @@CURSOR_ROWS on a cursor with a work table that is still being populated, @@CURSOR_ROWS returns a negative number. The absolute value of the number returned is the number of the rows that have been populated in the work table up to that time. For example, if @@CURSOR_ROWS is selected while the keyset of a keyset-driven cursor is still being populated, but 1,243 keys are already in the keyset, @@CURSOR_ROWS returns a value of -1243.
See Also
Other Resources
@@CURSOR_ROWS (Transact-SQL)
sp_configure (Transact-SQL)