Sep 202012
 

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.

  7 Responses to “What impact can different cursor options have?”

  1. Thank you for the great information Aaron. I have referred several people to this article already as I've run across cursor use in the wild.

  2. Great article. For years I have been using LOCAL FAST_FORWARD but never had a chance to compare it against other options. Thank you for providing the statistics for each option.

    Julie

  3. 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. :-)

  4. 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.

  5. Just keep in mind that FAST_FORWARD is DYNAMIC … FORWARD_ONLY you can use with a STATIC cursor.

    Try using it on the Halloween problem to see what happens !!!

    IF OBJECT_ID('Funcionarios') IS NOT NULL
        DROP TABLE Funcionarios
        GO
     
        CREATE TABLE Funcionarios(ID          Int IDENTITY(1,1) PRIMARY KEY,
                                  ContactName Char(7000),
                                  Salario     Numeric(18,2));
        GO
     
        INSERT INTO Funcionarios(ContactName, Salario) VALUES('Fabiano', 1900)
        INSERT INTO Funcionarios(ContactName, Salario) VALUES('Luciano',2050)
        INSERT INTO Funcionarios(ContactName, Salario) VALUES('Gilberto', 2070)
        INSERT INTO Funcionarios(ContactName, Salario) VALUES('Ivan', 2090)
        GO
     
        CREATE NONCLUSTERED INDEX ix_Salario ON Funcionarios(Salario)
        GO
     
        -- Halloween problem, will update all rows until then reach 3000 !!!
        UPDATE Funcionarios SET Salario = Salario * 1.1
          FROM Funcionarios WITH(index=ix_Salario)
         WHERE Salario < 3000
        GO
     
        -- Simulate here with all different CURSOR declarations
        -- DYNAMIC update the rows until all of then reach 3000
        -- FAST_FORWARD update the rows until all of then reach 3000
        -- STATIC update the rows only one time. 
     
        BEGIN TRAN
        DECLARE @ID INT
        DECLARE TMP_Cursor CURSOR DYNAMIC 
        --DECLARE TMP_Cursor CURSOR FAST_FORWARD
        --DECLARE TMP_Cursor CURSOR STATIC READ_ONLY FORWARD_ONLY
            FOR SELECT ID 
                  FROM Funcionarios WITH(index=ix_Salario)
                 WHERE Salario < 3000
     
        OPEN TMP_Cursor
     
        FETCH NEXT FROM TMP_Cursor INTO @ID
     
        WHILE @@FETCH_STATUS = 0
        BEGIN
          SELECT * FROM Funcionarios WITH(index=ix_Salario)
     
          UPDATE Funcionarios SET Salario = Salario * 1.1 
           WHERE ID = @ID
     
          FETCH NEXT FROM TMP_Cursor INTO @ID
        END
     
        CLOSE TMP_Cursor
        DEALLOCATE TMP_Cursor
     
        SELECT * FROM Funcionarios
     
        ROLLBACK TRAN
        GO

 Leave a Reply

(required)

(required)