Common Query Plan Patterns: Cursor Weirdness

Why Are You Here?

---

Within the realm of cursor-y oddness, there are a couple things to be extra-aware of.

Yes, I know that Aaron Bertrand has written about cursors. Thank you, Aaron.

This post is about a slightly different set of things that I want you to be aware of.

Not So Fast

---

First, FAST_FORWARD cursors force your queries to run serially, and plan quality may suffer if a dynamic cursor is chosen.

Take this query for example:

SELECT TOP (1)
    p.OwnerUserId
FROM dbo.Posts AS p
ORDER BY p.Score DESC;

By itself in the wilderness it gets a parallel plan and runs for under 1 second.

But in the grips of a fast forward cursor, we’re not so lucky.

DECLARE @i int;
DECLARE c CURSOR 
    LOCAL 
    FAST_FORWARD

FOR
SELECT TOP (1)
    p.OwnerUserId
FROM dbo.Posts AS p
ORDER BY p.Score DESC;
 
OPEN c;
    FETCH NEXT 
    FROM c INTO @i;

WHILE @@FETCH_STATUS = 0
BEGIN   
    PRINT @i;
    BREAK;
END;
CLOSE c;
DEALLOCATE c;

Other Problems

---

Sometimes, the default cursor, along with several other cursor types, will lead to a CHECKSUM being generated.

This can happen when you:

• Declare a cursor that will do writes
• Declare a cursor for a select but don’t define it as read only

Here’s a breakdown of how that works if you don’t have a rowversion column in the table(s) that your cursor is touching

Son Of A Check

---

What can happen to performance if you use one of these types of cursors that does require a checksum?

Well, remember the query up there that took about a second with no cursor?

You could put together a query that resembles what happens here by doing something like this:

SELECT TOP (1)
    p.OwnerUserId, 
    unfair = 
        CHECKSUM(*)
FROM dbo.Posts AS p
ORDER BY p.Score DESC;

But as all non-Australians agree, this is unfair because when the cursor does it in the query above, it’s the storage engine computing the checksum and the row data is all there locally. When the optimizer does it, it has to go through extra steps.

But Why?

---

When you don’t tell SQL Server that your cursor query is read only, it will generate row version checksums to compare on subsequent to assess if rows changed. If your cursor query contains multiple table references, each table will receive a row checksum that doesn’t have a rowversion column already in it.

For example, this is what the next fetch looks like after the poorly-performing query:

You can see the cursor snapshot table joining back to the Posts table, along with an update of the cursor snapshot table.

The fetch query from the cursor query that performs well looks only like this:

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.