Aaron Bertrand

What impact can different cursor options have?

SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

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 CURSOR FOR 
  SELECT 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):

SELECT c1.[object_id] 
  FROM sys.objects AS c1
  CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2;

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:

DECLARE @i INT = 1;

DECLARE c CURSOR
-- LOCAL
-- LOCAL STATIC
-- LOCAL FAST_FORWARD
-- LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT c1.[object_id] 
    FROM sys.objects AS c1
    CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2
    ORDER BY c1.[object_id];

OPEN c;
FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @i += 1; -- meaningless operation
  FETCH c INTO @i;
END

CLOSE c;
DEALLOCATE c;

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:

    Duration, in seconds

    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:

    Additional memory, in MB

    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:

    tempdb usage, in pages

Conclusion

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

LOCAL STATIC READ_ONLY FORWARD_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:

LOCAL FAST_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.