Welcome back to my plansplaining blog series, where I dissect execution plans in order to increase our understanding of how SQL Server processes specific queries. We’re at part 33 already, and I’m still looking at cursors. After discussing the basics, static cursors, dynamic cursors, and keyset cursors, I will now look at the last of the cursor types: the fast forward cursor.

Fast forward cursor

One of the things that has always bothered me about the fast forward cursor type is the shocking lack of documentation of what it does exactly. Okay, the name suggests that it is fast. But is it really? When I first looked at cursor performance (granted, a long time ago), I found that a static cursor was actually faster than a fast forward cursor for the same query. So… fastish forward?

The name also suggests that this cursor is forward only. That is indeed the case. At least they got that right in the naming.

Let’s look at Microsoft’s documentation to see what it tells us, beyond the things we can (sometimes misleadingly) infer from the name.

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD can’t be specified if SCROLL or FOR_UPDATE is also specified. This type of cursor doesn’t allow data modifications from inside the cursor.
Note
Both FAST_FORWARD and FORWARD_ONLY can be used in the same DECLARE CURSOR statement.

That’s all. That’s all it says. And if you look closely, then it actually says even less than it seems. Not being able to combine FAST_FORWARD with SCROLL or FOR_UPDATE (sic!) is really already kind of implied by telling us it’s FORWARD_ONLY and READ_ONLY. Not allowing data modifications from inside the cursor is literally the definition of READ_ONLY. And while it’s nice to know that redundantly specifying FORWARD_ONLY will not throw an error, it doesn’t add much to my understanding.

So what the documentation actually says, after removing all redundancy, is just this:

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

Well, FORWARD_ONLY and READ_ONLY are quite clear. But “performance optimizations enabled”? What performance optimizations? How do they affect the results? And what about such details such as visibility of concurrent changes? We know exactly how static, dynamic and keyset cursors deal with those. Wouldn’t it be relevant to get some insight on visibility of concurrent changes in fast forward cursors too?

Since Microsoft doesn’t document any of this, there are only two ways to safely use a fast forward cursor in production code. Either you first test every possible scenario of concurrent changes that you can possibly imagine, to discover the effect empirically. Or you look at the execution plans to figure out how exactly the data is retrieved.

So, time to return to our standard example, but this time we’ll change the cursor type to FAST_FORWARD.

DECLARE MyCursor CURSOR 
                 GLOBAL
                 FORWARD_ONLY 
                 FAST_FORWARD 
                 READ_ONLY
                 TYPE_WARNING
FOR
SELECT     soh.SalesOrderID,
           soh.OrderDate,
           sod.SalesOrderDetailID,
           sod.OrderQty,
           sod.ProductID,
           p.ProductID,
           p.Name
FROM       Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
   ON      sod.SalesOrderID        = soh.SalesOrderID
INNER JOIN Production.Product     AS p
   ON      p.ProductID             = sod.ProductID
WHERE      soh.SalesOrderID  BETWEEN 69401 AND 69410
AND        sod.OrderQty           >= 10
ORDER BY   p.ProductID;

OPEN MyCursor;
FETCH NEXT FROM MyCursor;
FETCH NEXT FROM MyCursor;
CLOSE MyCursor;
DEALLOCATE MyCursor;

This is what the execution plan for the DECLARE CURSOR statement looks like:

(Click on the picture to enlarge)

Fast forward = static ???

It is especially interesting to compare this with the execution plan for the static cursor. For your convenience, here is a copy of the relevant screenshot from the post about static cursors:

(Click on the picture to enlarge)

The left hand side of the Population Query (up to and including the Segment operator), as well as the Fetch Query, are exactly identical between the two execution plans. The only obvious difference is the name of the top left operator.

Similar parts

Now, when you dive into the details of the properties, you will see a few minor differences. Most are related to names for internal columns. For instance, the name of the row number column that is computed in Sequence Project is not Expr1005, but I4Rank1004, the Predicate property of the Clustered Index Insert on CWT_PrimaryKey uses [STREAM] instead of [CWT] as the table alias, and it returns one less column to its parent operator, that ignores the returned data anyway. But those differences do not affect the operation. For both the static cursor as well as the fast forward cursor, the right hand side of the execution plan (that we’ll look at in more detail further down) produces the results of the SELECT statement in the DECLARE CURSOR statement, and the left hand side then adds row numbers, stores that data in a temporary table, and the Fetch Query then uses the (internal) scalar function FETCH_RANGE(0) in a Clustered Index Seek to retrieve the correct row.

Differences

To the right of the Segment operator is where the differences start. If you only look superficially, the two subplans look very different. But if you look a bit better, you will see that the actual difference is not that large. The far right side of the plan for the static cursor, a Nested Loops join between Clustered Index Seek operators on SalesOrderHeader and SalesOrderDetail, into a Sort by ProductID, is still intact in the execution plan for the fast forward cursor. However, it is now no longer the top input to another Nested Loops join, but the bottom input to a Merge Join. And the Product table is now read using a Clustered Index Scan instead of a Clustered Index Seek.

Outside of a cursor, when just running the query, we can also get both variations of the execution plan. We normally get the execution plan with two Nested Loops operators, as shown in this post. But by rewriting the query and adding a join hint, we can get the same execution plan that we now see for the population query of the fast forward query:

SELECT     soh.SalesOrderID,
           soh.OrderDate,
           sod.SalesOrderDetailID,
           sod.OrderQty,
           sod.ProductID,
           p.ProductID,
           p.Name
FROM       Production.Product AS p
INNER MERGE JOIN(           Sales.SalesOrderHeader AS soh
                 INNER JOIN Sales.SalesOrderDetail AS sod
                       ON   sod.SalesOrderID = soh.SalesOrderID)
      ON   p.ProductID = sod.ProductID
WHERE      soh.SalesOrderID BETWEEN 69401 AND 69410
AND        sod.OrderQty >= 10
ORDER BY   p.ProductID;

For this query, you get the execution plan below, which does indeed match the right hand side of the Population Query for the fast forward cursor.

(Click on the picture to enlarge)

This does require the MERGE join hint. Remove the hint, and you’ll get the other plan again, with the two Nested Loops operators.

Let’s look a bit further. For instance at the estimated cost of these two execution plans. For the unhinted plan, with two Nested Loops operators, the Estimated Subtree Cost of the SELECT is 0.0496 cost units (rounded). Adding the MERGE hint to force the alternative execution plan reduces that a bit, to 0.0420 cost units. So based on the estimated cost only, the hinted plan actually looks cheaper. Why didn’t the optimizer pick it?

How about reality? Which plan is actually cheaper? That depends on how you look. Based on logical reads, the plan with the Merge Join is cheaper: 13 instead of 24 logical reads on the Product table. But when executed against an empty buffer pool, the double Nested Loops wins: only 3 physical reads and no read ahead reads, as opposed to 1 physical read but 20 read-ahead reads. And the execution times? Both take less than a millisecond, so I executed each a 100,000 times in a loop, while discarding the output, and here the original plan clearly won: 11 versus 25 seconds. So it really depends on what measure you use.

Conclusion

The execution plans for this query definitely show that the fast forward cursor behaves, in this case, exactly like a static cursor. The population query retrieves the data that we want the cursor to return and stores it in an internal worktable, and the fetch query retrieves and returns a single row from that worktable. Changes made to the underlying tables in between opening the cursor and retrieving the row will not be visible.

The only difference between the execution plans for fast forward and for static is a small change in how data is retrieved and joined in the population query. That, then, must apparently be the effect of the “performance optimizations”, that are enabled. But my measurements show that it really depends on what metric you use whether those performance optimizations benefit or actually hurt performance.

Fast forward = dynamic ???

However, it gets weirder when I make a small change to the original query, as follows:

DECLARE MyCursor CURSOR 
                 GLOBAL
                 FORWARD_ONLY 
                 FAST_FORWARD 
                 READ_ONLY
                 TYPE_WARNING
FOR
SELECT     soh.SalesOrderID,
           soh.OrderDate,
           sod.SalesOrderDetailID,
           sod.OrderQty,
           sod.ProductID,
           p.ProductID,
           p.Name
FROM       Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
   ON      sod.SalesOrderID        = soh.SalesOrderID
INNER JOIN Production.Product     AS p
   ON      p.ProductID             = sod.ProductID
WHERE      soh.SalesOrderID  BETWEEN 69401 AND 69410
AND        sod.OrderQty           >= 10;
--ORDER BY   p.ProductID;  -- Changed line

OPEN MyCursor;
FETCH NEXT FROM MyCursor;
FETCH NEXT FROM MyCursor;
CLOSE MyCursor;
DEALLOCATE MyCursor;

By just removing the ORDER BY clause, we now get a very different execution plan:

(Click on the picture to enlarge)

The first thing you will notice is the absence of a population query. There is only a fetch query in this case. Just as when we tested the dynamic cursor. The rest of the plan is quite different though, but that is purely due to the removed ORDER BY clause. If you look at the execution plan for the dynamic cursor after removing that ORDER BY, you will see that it is exactly the same, except for adding one Compute Scalar operator that adds a column Expr1003 and sets it to 1, for absolutely no visible reason.

Conclusion

Apparently, in this case, the fast forward cursor generates an execution plan that is almost identical to the execution plan for a dynamic cursor. So I expect it to behave the same as well. And it does. If you experiment by changing data after opening the cursor, you will see that those changes are now visible through the fast forward cursor, just as they are also visible through a dynamic cursor.

There’s also nothing here to support the suggested “performance optimizations”. The plan for this FAST_FORWARD cursor is, except for a seemingly useless Compute Scalar, identical as when I had specified DYNAMIC, which is actually the slowest cursor type. The word “fast” in the name is actually misleading.

Summary

What we have seen is that the fast forward cursor type can be quite unpredictable. Depending on the text of the query, it might behave as a static cursor, which won’t see changes made to the data after the cursor is opened, or it might act as a dynamic cursor, which does see all those changes. Knowing this, I now don’t even dare to exclude the option that a fast forward might under some circumstances also decide to act as a keyset cursor, if it feels like it. That makes this cursor type, in my opinion, completely unreliable for use in any code.

Of course, you can test and verify the actual concurrency behavior, by looking at the execution plan. But are you sure it won’t change after the next cumulative update? Or when the DBA adds or changes an index? Or perhaps even when a statistics update triggers a recompile into another plan?

Perhaps the behavior is predictable in some way. But if it is, then that way is not documented. Which in my book makes it the same as unpredictable behavior.

Until Microsoft decides to put out extensive documentation to help us understand what we can expect when we use a fast forward cursor, I will recommend against using this cursor type at all.

As always, please let me know if there is any execution plan related topic you want to see explained in a future plansplaining post and I’ll add it to my to-do list!