optimize Cursor Threshold option

Question

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!!!

Answer 1

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.

Answer 2

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/2.257.1.134/1/

Best regards,
Seeya

---

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.

Answer 3

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.