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;
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.
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!
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.