optimize Cursor Threshold option

Flako 1 Reputation point

Hello, I manage an ERP on MSSQL 2008 R2 that uses cursors for almost everything ..

I am analyzing changing the value of the Cursor Threshold option, from -1 to some value N, but I am not sure how to analyze which is the best value.

I understand that if I assign 100, for any query that its recordset is less than 100, it runs synchronously, otherwise asynchronously, but I don't know how to do a metric to see if there is a real improvement.

I started by looking at the performance counters, like


But there is no "how much is the average amount of recordset" metric to use as a metric.

Do you know any documentation that helps me to define some metric to be able to measure the improvement when modifying the Cursor Threshold value?

Thank you for reading this far!!!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,792 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 77,951 Reputation points MVP

    To be frank, you would be out in the wilderness on your own if you play with that option. I have never heard of anyone touching it before.

    0 comments No comments

  2. Seeya Xi-MSFT 16,246 Reputation points

    Hi @Flako ,

    Possible values returned by @@CURSOR_ROWS.
    Value Description
    -1 The cursor is dynamic. @@CURSOR_ROWS is not applicable.
    -m A keyset cursor is still in the process of being populated asynchronously. This value refers to the number of rows in the keyset so far. The negative value indicates that the keyset is still being retrieved. A negative value (asynchronous population) can be returned only with a keyset cursor.
    n This value refers to the number of rows in the cursor.
    0 No cursors have been opened, the last opened cursor has been closed, or the cursor has no rows.
    Here is the MS doc: https://learn.microsoft.com/en-us/sql/t-sql/functions/cursor-rows-transact-sql?view=sql-server-ver15#return-value
    And you can refer to this: https://flylib.com/books/en/

    Best regards,

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Erland Sommarskog 77,951 Reputation points MVP

    It might be that Seeya intended to answer a different post, but inadvertently posted that answer to this thread instead. At least there is not much connection to the actual question.

    However, there is a titbit that catches my eye:

    A negative value (asynchronous population) can be returned only with a keyset cursor.

    If this is true, that only KEYSET cursors can be populated asynchronously, it is not very likely that cursor threshold has any effect, because this is not a commonly used cursor type in stored procedures. (Some clients may use them, though.) Cursors in stored procedures should always be STATIC in my opinion, although the default is DYNAMIC - which can be really slow.

    0 comments No comments