cursor threshold Option
Use the cursor threshold option to specify the number of rows in the cursor set at which cursor keysets are generated asynchronously. When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.
If you set cursor threshold to -1, all keysets are generated synchronously, which benefits small cursor sets. If you set cursor threshold to 0, all cursor keysets are generated asynchronously. With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set in cursor threshold. Do not set cursor threshold too low, because small result sets are better built synchronously.
Note
SQL Server does not support generating keyset-driven or static Transact-SQL cursors asynchronously. Transact-SQL cursor operations such as OPEN or FETCH are batched, so there is no need for the asynchronous generation of Transact-SQL cursors. SQL Server 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 accuracy of the query optimizer to determine an estimate for the number of rows in a keyset depends on the currency of the statistics for each of the tables in the cursor.
The cursor threshold option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cursor threshold only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
In order to populate a cursor asynchronously, the cursor creation must be wrapped in a user transaction.
Example
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'cursor threshold', 0;
RECONFIGURE
DECLARE @cursorId INT
BEGIN TRANSACTION
EXEC sp_cursoropen @cursorId OUTPUT, 'SELECT * FROM sys.all_objects', 8
SELECT @cursorId
SELECT session_id, cursor_id, properties, is_async_population FROM sys.dm_exec_cursors(0);
COMMIT TRANSACTION
EXEC sp_cursorfetch @cursorId
EXEC sp_cursorclose @cursorId
See Also