How to: Configure the cursor threshold Option (SQL Server Management Studio)
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.
To configure the cursor threshold option
In Object Explorer, right-click a server and select Properties.
Click the Advanced node.
Under Miscellaneous, change the Cursor Threshold option to the desired value.
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.