Asynchronous Population of a Cursor May Result in Performance Degradation

SQL Server offers an option to asynchronously populate API created keyset and static server cursors via the sp_configure ‘cursor threshold’ parameter. When a cursor is asynchronously populated, a secondary thread is spawned to populate the cursor asynchronously in the background, while the original thread returns control to the client.

The default value for ‘cursor threshold’ is -1, which implies that the cursor will be populated synchronously. When ‘cursor threshold’ is set to a value equal to or greater than 0 (max = 2147483647), the SQL Server query optimizer estimates the number of rows that will be returned from the query execution and if the value is less than the ‘cursor threshold’ then synchronous population is used, else asynchronous population is used (refer to SQL Server Books Online for a detailed explanation). Asynchronous population for cursors is particularly useful for large result-sets where it may be preferable for the client application to not have to wait for the entire result set to be completely materialized before the data is accessible. Asynchronous population is not advisable for small result sets because the overhead of initiating a secondary thread for the cursor population often outweigh the benefits.

Recently we encountered a case where a customer accidentally changed the value of ‘cursor threshold’ from its default of -1 to 0 on their production database system for a large ERP (Enterprise Resource Planning) application. This rather small and what the DBA thought was an insignificant change caused a huge performance degradation, because ‘cursor threshold’ = 0 instructed SQL Server to populate all keyset and static cursors that had 0 or more rows to use a secondary thread for asynchronous population. To further complicate matters the database administrator did not remember exactly which parameter he had changed and what the original value was.

Debugging this problem was a bit challenging. We spent considerable time investigating the different attributes of the database server and finally managed to get to the root of the problem using data collected from two Data Management Views (DMVs): sys.dm_exec_cursors and sys.dm_os_tasks. The output of these DMVs showed that the keyset and static cursors were getting populated asynchronously which was not a recommended setting for the particular ERP application.

To present what we found by way of an example, we have executed a single keyset cursor query that populates a large result set. While the query is actively populating the result set of the keyset cursor we queried the two DMVs using the following T-SQL statements via a separate session.

SELECT session_id, cursor_id, properties, is_async_population FROM sys.dm_exec_cursors(0);

SELECT * FROM sys.dm_os_tasks

WHERE session_id = 52;

GO

The screenshot shown in Figure (i) depicts the output of the DMVs when the ‘cursor threshold’ value is set to -1 (default).

Figure (i): DMV output corresponding to synchronous population of a keyset cursor.

As can be seen in the sys.dm_exec_cursors query output, session_id 51 which corresponded to the session through which we were executing the keyset cursor query has the is_async_population bit flag set to 0 during the cursor population phase signifying that the result set was being populated synchronously. In addition the output of the sys.dm_os_tasks query shows session_id 51 waiting in the RUNNABLE state during the cursor population phase.

Next, when we change the ‘cursor threshold’ value to 0 using the statement (sp_configure 'cursor threshold', 0) and the repeat the experiment. Figure (ii) below depicts the result of this experiment.

Figure (ii): DMV output corresponding to asynchronous population of a keyset cursor.

As can be seen in the sys.dm_exec_cursors query output, session_id 51 which again corresponded to the session via which we were executing the keyset cursor query now has the is_async_population bit flag set to 1 during the cursor population phase which signifies asynchronous population of the result set. Note: The is_async_population bit flag is only set to 1 during the actual population phase; once the population phase is complete the flag is set to 0 even if the cursor was populated asynchronously. The asynchronous population was further corroborated via the output of the sys.dm_os_tasks query in which we see two tasks associated with session_id 51 during the cursor population phase, one corresponding to the original query (SUSPENDED status) and a secondary one associated with the asynchronous population (RUNNING status).

So, our recommendation is to not change the instance-wide sp_configure ‘cursor threshold’ setting unless you are sure that the change will have a net positive impact on all applications running on that instance and have extensively tested the performance with the new value in a test environment.