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. 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.
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.
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.
cursor threshold 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 stop and restart).
To set the cursor threshold option
Transact-SQL