The Curse Of Cursor Options In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the often-overlooked world of cursor options in SQL Server, sharing insights from my experience and the wisdom of Paul White. I explore how different cursor options can significantly impact query performance, especially when dealing with parallel execution plans and memory grants. By examining practical examples, I demonstrate why it’s crucial to carefully consider cursor options before implementing them, as their default behavior might not always be optimal. Whether you’re a seasoned SQL Server professional or just starting out, this video offers valuable lessons on how to fine-tune cursors for better performance without rewriting complex logic from scratch.
Full Transcript
Erik Darling, right here with you. With Darling Data, too. The force of Darling Data behind me. The strength and might of Darling Data. That’s where we’re at. That’s why we have a barbell for a logo. Anyway, in this video, we are going to talk about the curse of cursor options. Because this is, I mean, you know, you can talk till you’re blue in the face about, you know, not using cursors. But you end up seeing them in a lot of code. And, you know, depending on how much time and effort and, you know, how much someone has paid you, you might find it easier to adjust the cursor options that you’re using, rather than try to rewrite this whole, you know, rewrite this whole, you know, this whole big, complex, undoubtedly nested cursor scenario with, like, you know, decades of business logic built into if statements and checking things and whatnot. Because I’ve certainly been in that position. And I want to say right off the bat that everything I’ve learned about cursor options, I’ve learned from Paul White. I have a tremendous amount of respect for his ability to know what I’ve learned about cursor options.
what kind of cursor to use immediately. I don’t. I always have to, like, refer to notes and think about things and try different stuff. I do not have the mental capability to know exactly which cursor options to use immediately. So I’m going to put that way out front there. You know, there is stuff that I’ve, like, read about cursors from other sources, but I’ve only ever understood it when Paul told me. Which, I don’t know. Maybe it’s the accent. I don’t know. I don’t know. He doesn’t type with an accent, so it’s kind of weird. God, I’m messing this whole thing up. So embarrassed. Anyway, before we talk about this, let’s talk about you and me. Because I have this here YouTube channel, and you might enjoy it so much that you think, you know what, I can part with four bucks a month to make Erik Darling feel good about himself. You might also think, I’m not giving this twit four bucks a month. You might want to like or subscribe or comment, one of those things. If you subscribe, you will be joining the legion of over 4,300 other data darlings in the known universe who get lovely flowers thrown at their faces and feet and then you will be able to find out the way out front there. And I’ll see you next time.
If you’re into that sort of thing, it might be something you’d consider. If you need SQL Server help, maybe you have a lot of cursors in SQL Server and you need some help tuning those. Apparently, apparently I can help with that. I ain’t too proud to bring that up. But in general, if you need a health check, performance analysis, hands-on query and index tuning, or I don’t know, anything else really. I’ll do all the knobs and buttons on that thing. If you’re having a SQL Server crisis or if you want developers to get trained so that you have fewer SQL Server crises to call me about, all good ways to spend money on me. If you want to get some low-cost training for a SQL Server, whether you’re at the beginner, intermediate, or expert level, you can get all of mine for life for 75% off. There are links to do all of these things in the video description.
So you should go look at the video description where the links are and click on them. It’d be the smart thing to do. If you want to see me live and in person, there are no links in the video description for this. That would be overbearing and I would have to change stuff too often. So this just gets a slide. Friday, September the 6th, I will be…
Actually, this video might be coming out on the Monday after I do this, so it’s going to be too late for that. Forget I said anything about Dallas. November 4th and 5th, I’m going to edit this slide apparently. November 4th and 5th, I will be at Past Data Summit with Kendra Little co-hosting two raucous…
rockin’… It’s going to be great. Two days of performance tuning content all about… well, I mean, obviously SQL Server. I don’t know. What else am I going to do with my life? So you can catch me there.
And with that out of the way, let us begin the festivities. Let us join the dance, my friends. All right. Let’s get over to SQL Server Management Studio. Let’s make sure that everything is in good working order here. And what I’m going to do is I’m going to run this query because this is important.
We’ve done exercises like this before where, you know, you come up with a query that runs reasonably fast, but then you put it in some other context, like maybe inserting into a table variable or something, and all of a sudden performance bites. And you’re like, what happened? I don’t understand it.
The answer, my friends, is always going to be in the execution plan. Always. It will be there. It will tell you a good general selection of things.
Let’s put it that way. So let’s run this query. And this query will finish quite reasonably in a bit under a second, right? We get a parallel execution plan.
Everything looks pretty okay here. If you were to write this query in real life, you would probably say, cool, it’s under a second. I’m done. I’m out. Don’t need to call Erik Darling about this one.
But you might if you do some other stuff. So some cursor options, like fast forward, will prevent a parallel execution plan. Other cursor options cause SQL Server to generate weird checksums on data when cursor tables are populated.
Again, these are all things that will be in the query plan that I’ll show you. This can happen. A lot of the times people in SQL Server will just declare a cursor and let SQL Server do the rest.
That’s a big mistake. Because SQL Server can make all sorts of weird choices about your cursor. They can have all sorts of performance side effects.
So if you use scroll, keyset, dynamic, or optimistic cursors without the read-only option also included there, you can end up with really awkward query plan stuff. Because SQL Server adds this checksum to see if rows changed between cursor.
So what I’m doing here is I’m using some syntax that I wish were more popular. Because I see a lot of people try to close and deallocate cursors in the wrong place in code. Myself included.
I’ve gotten caught with my pants. I mean, I’d say around my ankles, but it was like one leg is around an ankle and the other leg is pulled up over my face with that. So I’m using a cursor variable here.
So I don’t have to worry about closing or deallocating. SQL Server will just do that when the time is right. It does complicate the syntax a little bit because you have to, like, declare this and then set the options for it later. It’d be a lot nicer if SQL Server just let you declare the cursor variable with the options you care about.
But what can we do? T-SQL does not see too many improvements, does it? It’d be nice if the summer interns would do some of that stuff.
You ever see some of the syntax available in other databases? It’s real nice. Makes SQL development nice and easy. A lot less crap to do.
T-SQL, you are looking at doing maximum work in everything. So let’s run this just as a fast-forward cursor, right? And this is what I mean to show you about the parallel execution plan thing.
You may notice that this is no longer finishing in a little under a second. This took about five seconds to finish. Here we have our no longer parallel execution plan.
And this is not a side effect of the cursor variable. If we declared a regular cursor with all the options, it would do the exact same thing. There’s no difference there.
But if we, let’s see. Oh, good. We get a warning about this. The query memory grant detected excessive grant, which may impact the reliability. The reliability of what?
I do not know. Just the reliability. The grant size initially was 1,024 KB. Massive, right? Huge.
And used was 16 KB. This is what we are warned about in SSMS. This is what we got instead of commas. Or let’s just say number separators.
It doesn’t have to be commas. I learned recently from a bug in SP Quickie Store that the French use spaces for number separators. So glad that got fixed.
I do want to keep friends with the French. They do have my favorite food and they do have my favorite place to smoke cigarettes. Thanks, France.
Anyway, that’s a bummer. And if we go to the properties over here, we don’t get a loud warning about this. This we have to go digging for. And we zoom in way over here. Let’s put that somewhere nice and near my head.
We have this non-parallel plan reason. No parallel fast forward cursor. Yes, indeedy-doody. No parallelism for you there. Be very careful where you use fast forward cursors.
Sometimes fast forward. In some scenarios, fast forward can be the best option. In other cases, fast forward can really bungle up performance. So be careful there.
My favorite cursor options are local static. But, you know, depending on what you’re doing, that might not make sense either. Cursor options are crazy like that.
So anyway, let’s look at the second example of cursor weirdness. Now, we have to quote out fast forward because we cannot combine these two things. And we have to quote in dynamic.
And what’s going to happen here is performance is going to get even worse than it was before. Not only, well, we actually do get a parallel plan on this one for a bit. But that parallel plan has to do some stuff.
That took about 10 seconds. And if we look at the plan for this, so you have this whole thing over here. So this is where SQL Server populates this sort of hidden worker table that the cursor is going to work off of later. So you have the population query, which grabs all the data you care about and puts it in here.
And then you cursor over the populated table rather than cursoring over the actual table over and over again. The trouble is, even though this is in a parallel zone, right? You can see all the parallelism stuff in here.
You can see the little zoomy buttons across these operators. Notice that this sort now takes nine seconds. It takes nine seconds because, oh, we don’t need the properties. We just need the tooltip.
Calm down. Calm down. Calm down. We have this output list. And that output list contains CHK1002, which is a checksum that SQL Server generates on the entire row that ends up in the cursor table to see if there are any changes between cursor executions, which you need to do some handling for if you’re writing that type of cursor.
It’s almost the equivalent of doing something like this, where you’re selecting the top one owner user ID with this checksum across everything in there. And you won’t see the CHK1002 in here, but you will see this EXPR1001. If I were to, there’s a reason why there’s a misspelled note over this query.
I’ll pretend that didn’t happen. Here I go making fun of the reliability, and I’m saying, don’t run this, PAA. PAA, don’t run this.
Like a mel. And this would take about a full minute to run. So the checksum that happens in here is somehow worse than what happens in the cursor. But if we give this cursor the read-only attribute, right?
If we switch from dynamic-only to dynamic-read-only, we give SQL Server that extra information. Somehow, this select top one query was not enough for it to go off of. Don’t ask me why.
And we rerun this. This will finish just about instantly because we no longer have the CHK in here. And we actually have the population query is just about the exact same as it was when we ran our top one query just by itself. So, like I said, sometimes if you’re tuning code that involves cursors, you may be a full-time employee.
It may be straightforward enough, or maybe you got paid enough to turn the whole cursor thing into set-based code. If that’s true, I’m happy for you. Usually in SQL Server, getting rid of cursors is a pretty darn good idea.
If you’re in a situation where getting rid of the cursor is not an option, sometimes fine-tuning the cursor options might be an option. And you might be able to get much, much better performance if you’re able to do that rather than rewrite the entire cursor thing. Of course, that really depends on what you do with the cursor later.
You know, well, cursors do have, you know, their own sort of overhead and issues. And, you know, I generally don’t recommend them. There are times when they can solve a problem pretty quickly and easily that writing set-based code for would not be very, very easy at all to do.
So, I understand why they’re there. And I also do my best to try to understand the options available to me when using cursors because they can have a profound effect on how fast your cursor runs. So, with that out of the way, as always, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that, uh…
God, I’m talking about curses. I hope that you’ll like and subscribe and member and training, consulting, all that good stuff. Uh…
Because, you know, that’s… That’s how I drink enough to deal with cursors. Anyway, we’re gonna call that one here. Uh…
Once again, thank you for watching. You… Uh… You have now made it to the end of SQL Server. You can shut it down now. There’s nothing left to talk about, right?
That’s good. That’s good. Uh… Alright. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.