I've written several times about using cursors and how, in most cases, it is more efficient to re-write your cursors using set-based logic.

I'm realistic, though.

I know that there are cases where cursors are "required" – you need to call another stored procedure or send an e-mail for every row, you are doing maintenance tasks against each database, or you are running a one-off task that simply isn't worth investing the time to convert to set-based.

How you are (probably) doing it today

Regardless of the reason you are still using cursors, you should at the very least be careful not to use the quite expensive default options. Most folks start their cursors off like this:

DECLARE c CURSORFORSELECT whatever FROM...

Now again, for ad-hoc, one-off tasks, this is probably just fine. But there are…

Other ways to do it

I wanted to run some tests using the defaults and compare them to different cursor options such as LOCAL, STATIC, READ_ONLY and FAST_FORWARD. (There are a ton of options, but these are the ones most commonly used as they are applicable to the most common types of cursor operations that people use.) Not only did I want to test the raw speed of a few different combinations, but also the impact to tempdb and memory, both after a cold service restart and with a warm cache.

The query I decided to feed to the cursor is a very simple query against sys.objects, in the AdventureWorks2012 sample database. This returns 318,500 rows on my system (a very humble 2-core system with 4GB RAM):

Then I wrapped this query in a cursor with various options (including the defaults) and ran some tests, measuring Total Server Memory, pages allocated to tempdb (according to sys.dm_db_task_space_usage and/or sys.dm_db_session_space_usage), and total duration. I also tried to observe tempdb contention using scripts from Glenn Berry and Robert Davis, but on my paltry system I could not detect any contention whatsoever. Of course I'm also on SSD and absolutely nothing else is running on the system, so these may be things you want to add to your own tests if tempdb is more likely to be a bottleneck.

So in the end the queries looked something like this, with diagnostic queries peppered in at appropriate points:

Results

Duration

Quite arguably the most important and common measure is, "how long did it take?" Well, it took almost five times as long to run a cursor with the default options (or with only LOCAL specified), compared to specifying either STATIC or FAST_FORWARD:

Memory

I also wanted to measure the additional memory that SQL Server would request when fulfilling each cursor type. So I simply restarted before each cold cache test, measuring the performance counter Total Server Memory (KB) before and after each test. The best combination here was LOCAL FAST_FORWARD:

tempdb usage

This result was surprising to me. Since the definition of a static cursor means that it copies the entire result to tempdb, and it is actually expressed in sys.dm_exec_cursors as SNAPSHOT, I expected the hit on tempdb pages to be higher with all static variants of the cursor. This was not the case; again we see a roughly 5X hit on tempdb usage with the default cursor and the one with only LOCAL specified:

Conclusion

For years I have been stressing that the following option should always be specified for your cursors:

LOCALSTATICREAD_ONLYFORWARD_ONLY

From this point on, until I have a chance to test further permutations or find any cases where it is not the fastest option, I will be recommending the following:

LOCALFAST_FORWARD

(As an aside, I also ran tests omitting the LOCAL option, and the differences were negligible.)

That said, this is not necessarily true for *all* cursors. In this case, I am talking solely about cursors where you're only reading data from the cursor, in a forward direction only, and you aren't updating the underlying data (either by the key or using WHERE CURRENT OF). Those are tests for another day.

I am just now seeing this post so forgive me for the late reply. One thing I want to point out is that even though Static may not have the smallest footprint out of the cursors and might be slightly slower under these conditions it can be dramatically faster in a heavy use / high concurrency one. Even LOCAL FAST_FORWARD cursors go back to the original source with new fetches and do take locks and latches which can interfere with a highly concurrent operation. This can be from both sides meaning that it can cause blocking and be blocked. Where as a STATIC cursor only hits the table once and all further actions are against the copy in tempdb which has no need for dealing with concurrency options. As such in a heavily used environment it can actually be much faster than any of the other options. In my experience it has proved to be the fastest option in general overall in real life use cases and can have the least amount of CPU usage as well. Just my 2 cents :)

Thanks Andy, yes it is true that STATIC can exhibit some benefits at a certain scale, but overall I would prefer to use a consistent approach until I have a reason to break consistency, and in most cases the transactional volume just doesn't justify using STATIC – especially in cases where we're already increasing the load on tempdb with things like snapshot isolation. Ideally, of course, we just get rid of the cursors altogether. :-)

Hi Aaron, this is a very interesting, I really enjoy reading your articles. I think you got very similar results between LOCAL FAST_FORWARD and LOCAL STATIC READ_ONLY FORWARD_ONLY because FAST_FORWARD it's a cursor model equivalent to READ_ONLY FORWARD_ONLY that compiles to a static-like or dynamic-like cursor plan. Seems as you said FAST_FORWARD is a little better because the way the decision is made to use a dynamic-like or a static-like plan.

Another tip that has no performance impact, but helps the developers – I use always a WHILE 1 = 1 loop, fetch the records in the first statment and BREAK when no more records could be fetched. This way you have only to insert a single FETCH (even if you are using something as IF @ignore_this = 1 CONTINUE), which prevents infinite loops (CONTINUE without an explicite FETCH) or missing columns on the second, third … FETCH after you added a new column to the SELECT

PS:
– what is the syntax to insert code (and why can't I edit my posts)
– in the IF above there should be a not eqal (!= or lesser/greater sign), which was removed by the forum software, just before the 0