Cursory
Video Summary
In this video, I dive into the world of cursors and explore why my favorite cursor option is the local static cursor. Despite some contention in the SQL community about which cursor options are best, I share one compelling reason for preferring local static cursors over others like the fast forward cursor. I explain how using a fast forward cursor can force your entire query to run serially, leading to suboptimal performance. By contrast, a local static cursor allows for parallel execution, significantly improving query performance in certain scenarios. The video also covers the importance of testing different cursor options and understanding their impact on execution plans and performance metrics.
Full Transcript
This is still just plain black coffee and I’m having a tough time with that this morning. I’m recording video number two today on this cold, chilly, rainy day. Brrr, day where it sure would be nice to have a heaping helping of some Lafroig or Lagavulin or Ardbeg in a fellas coffee cup. Sure would be nice. Ah, boy. So I want to talk about my favorite cursor options. I know that there is some contention.
I almost knocked my coffee over because I’m apparently too sober to function. So there is some contention about what the best cursor options are and why. And you should always use this and never use this. And I’m going to throw my hat in the ring.
And my favorite cursor options are local static. And there are very smart people out there like Erlend Somerskog who agree with me. And I’m going to show you one of my reasons why I like the local static cursor better than other cursor types. But first, we have to go look at that other cursor type.
So this is a type of cursor that a lot of people will tell you to use a local fast forward cursor. And if you look at the documentation for cursor options, you’ll see that fast forward has some optimizations. Optimizations.
What those optimizations are, we don’t know. I don’t know. I don’t think they’re documented. But one optimization about fast forward cursors that I heartily dislike is that when you run them, much like many other things in SQL Server, like inline scalar, non-valued functions, non-inlined scalar valued functions, modifying table variables, reading from multi-statement table valued function table variables, accessing some system views, blah, blah, blah, blah, blah, will force your entire query to run serially.
Let’s go take a look. So with the fast forward cursor in place, we can see that this query plan up here is entirely serial. And it runs for just about 11 and a half seconds. If I go look at the properties of this open cursor, right?
Something again, I think I say this in a lot of videos when I’m talking about execution plans. One should always be looking at the properties window when they are looking at query plans. There is so much good, rich, detailed information in there about what is going on with your query.
Without it, you would be lost. Sad babe in the woods. We can see immediately that there is a non-parallel plan reason. Now, why none of these things have spaces in them is beyond, is a little bit beyond me.
I’m not quite sure why. You know, I understand that sometimes space bars stop working. You could probably just ask Microsoft’s IT department for a new one.
They probably have some spares sitting around. But anyway, we have a non-parallel plan reason that says no parallel fast forward cursor, which is a bit of a departure from most non-parallel plan reasons.
Most of the time when you see a non-parallel plan reason, it will either say maxed offset to one or could not generate valid parallel plan. And could not generate valid parallel plan is an umbrella for a lot of things that we cannot have query parallelism for.
Now, am I saying that parallel queries should always be used or that they are the best or that you should always strive to have a parallel query? No. No, of course not. And if we’re being honest about the query that’s running, there are probably some indexes that would help this thing run a lot faster, where you wouldn’t notice a difference in the type of cursor that you are using.
But not everyone is in a position where they can, you know, change indexes or, you know, tune queries or tune logic or any of that other stuff. And you might have a big honking crazy query that drives the initial result set of the cursor you’re going to use. And you might not have too many options for tuning that.
This is one thing where changing the cursor option can help you a little bit. So this query takes 11 and a half seconds when it runs serially with the local and fast forward options assigned to the cursor. But over here we have a query or we have a cursor.
We have a… With my favorite cursor options, local static. And when we look at the query plan for local static, we can see that we do not have the same parallelism inhibitor going on that we did in the fast forward plan.
We actually have parallel, parallel, parallel, parallel up until we gather streams here. And this runs for about 2 point… Well, let’s be fair.
About 2.8 seconds. So we got about 12 seconds versus about 3 seconds. Right? Okay. So about a 9 second difference in the query that drives the initial population of the cursor temp table doodad thingy. So, kinda nice that you can…
You should be careful with your cursors and all that. And, you know… I think…
I think cursors are a bit inevitable in some cases. And, you know… Well, you know, some people might watch this video and hiss because I’m talking about ways to tune a cursor rather than just getting rid of the cursor. Well, I mean, like, you know, what if I was going to take the results of this cursor?
What if I was going to take ID, display name, and score? And what if I was going to feed those into a stored procedure and I was going to have to run that stored procedure over a whole bunch of rows? Like, how else am I really going to do that?
If I write a while loop, is it going to make a difference in how I call a stored procedure? If I dump those rows into a temp table, is it really going to be all that different than if I use a cursor to populate a static temporary object? No.
It’s not going to be all that different. So, you know, well, a lot of people will get mad about the cursor in general. And I understand why because there are some very bad uses for cursors. And, you know, I see them and I got to help people with those kind of performance problems pretty often.
But, you know, if I had the choice between spending a long time tuning a cursor query and just changing the cursor option and, you know, saving nine seconds right off the bat, you know, if I’m in a hustle, if I’m trying to like, you know, really get some stuff done, I might just go with the different cursor options to move things along. Maybe get to something else that’s, you know, a performance bump in the night for something I’m trying to tune.
Anyway, the moral of the story is don’t use cursors if you don’t have to. But if you have to or if you’re stuck with them for some other reason, choose a cursor. Look at your cursor options carefully.
Don’t just jump right to the fast forward cursor. It can, you know, do some do some performance testing, you know, run the run the query inside the cursor and see how it goes. And then then run the query inside the cursor and see if see if you get the same execution plan.
See if you get the same time, the same performance metrics, because, you know, if you run the query, then, you know, you don’t use outside of the cursor and you get a nice parallel plan and everything’s fast. And then you put it in the cursor and it takes 20 minutes, you’re gonna be like, oh, this damn cursor. But it’s you. It’s your fault. You chose the wrong cursor options.
Don’t blame the cursor. Blame the you. That’s it. Anyway, I’m gonna go now. I’m going to go invest in Scotland and have a nice day. So, thanks for watching. I hope you I hope you learned something.
I hope that you enjoyed yourself. Maybe. And I will see you in another video. Maybe even another one today.
I don’t know. I don’t know where I don’t know where today may take me in. And if I’m feeling proper Scottish, I might I might feel very record-y today. I don’t know. We’ll see. It’s a surprise for everyone.
Anyway. Thank you. And I will see you in another video where maybe I’ll still be able to stand up. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.
What about LOCAL FOWARD_ONLY READ_ONLY?
What about it?