Bring The Goalies In
Video Summary
In this video, I delve into the mysterious world of row goals in SQL Server, highlighting their impact on query plans and execution. Row goals can significantly alter join strategies and data retrieval methods, leading to seemingly inefficient queries that return zero rows but run for extended periods. By examining a specific example where a query took 15 seconds to return no results due to an overly optimistic row goal, I illustrate how these settings can mislead the plan cache and cause confusion. I also discuss how to identify such issues using tools like sp_BlitzCache in expert mode, pointing out the ‘low cost, high CPU’ warning that often accompanies these scenarios.
Full Transcript
Sweet. We’re live. I’m here with the third installment of Plan Cache Liars, things that can look very confusing and confounding in the Plan Cache. And this is the final installment of things that have a low cost but high CPU. Now, there are other things that can cause this. Chief among them is, you know, parameter sniffing or parameter sensitivity. I’m gonna cover that. I’m gonna cover a few different variations on that in future videos. This is like the non-parameter sniffing stuff. This is just stuff that’s straight up weird. Alright, and I think probably one of the weirdest things in SQL Server is our, our, our, probably, uh, row goals. Because they can affect query plans in very strange ways. Uh, you know, SQL Server will internally say, look, I think, uh, I, you know, I don’t want to read every row in the table. I think I can, I think if I set a, a smaller internal row goal, I can, I can meet, I can meet, I can meet my quota. I can meet like all the, the, the, the demands of this query in a, in a much quicker and easier way than if I like, you know, uh, process every single row and do all this other stuff. So, uh, in doing so, it reduces the cost of the query and, um, chooses a total amount of data.
Totally different strategy for doing things. Um, so like you’ll see a lot of like, you know, little nested loops joins and key lookups and stuff where like, you know, without the row goals, you might see like hash joins and merge joins. Now, row goals aren’t always bad. They’re there for very good reasons. And sometimes they can be very, very helpful to queries. Other times they can be less so. So let’s look at a case where they are less. So I’m going to execute this query. And then we are going to look at a query plan. Surprise! Nice! Shocking, I know. No one would have seen this coming. It’s the most bizarre thing. Boy, this is running for a long time. How long does this query run for? 15 seconds to return zero rows. Why? Row goals! Yay! We figured it out. We cracked the case, you and me. Now, first thing I want you to see. These lines are thick. Thick, thick lines. Much thicker lines than one would typically want to see going into a nested loops join. That is a very unscientific thing to say. But, it’s one of those things that you might, you might look at and say, hmm, that’s a lot of work for a nested loops join.
Now, if we, for newer versions of SQL Server, which hopefully you’re on by now. Gosh! It’s enough already. Newer versions of SQL Server will, not in the tooltip, but in the properties window, the extended properties. If you hit F4, right click and go to properties. You will see this little doodad up here.
Estimated rows without row goal. 3, 7, 6, 7, 5, 6, 0. Estimated number of rows. And this is an estimated number of rows with the row goal. 2.84807. So, SQL Server is like, I think I can do this in about three rows. I don’t know what a .84807th of a row is. Don’t ask me. Well, let’s just call that three. Because, I don’t, I don’t, if I start to bend this finger down, strange things will happen.
If I wanted to represent, like, .84807, I would have to do strange things. Like, bite part of my finger off. But, SQL Server is like, I think, you know, the estimated rows without the row goal. Look, that’s a big number. Estimated rows with the row goal. It’s a much smaller number.
Or, the thing is that, let’s see if I can, I can frame this well. This number and this number are pretty close to each other. SQL Server did not save itself any work with this row goal.
SQL Server did a bad job of, of, of finding, of, well, actually, maybe it did a great job of finding data that wasn’t there or something. I don’t know. But, the point is that we had this row goal introduced.
And, because the row goal introduced a different join strategy, a different strategy for finding data than we might otherwise see. Then, we have this little nested loopy plan where SQL Server took 3.75, 375, 375, 3.7 million rows from the votes table. And, for each one of those rows, stuck it through a nested loops join and went to find data over here.
And, then, and then, obviously, didn’t find data. We found zero of four rows over here, which is interesting. but the point is that uh with because we had that row goal we had that nested loops join where sql server thought three rows would do the job here it clearly did not it took 3.75 million and then we had to do 3.75 million uh things over here which is unfortunate to say the least now this is of course not about actual execution plans this is of course about uh non this is about how the plan cache can lie to you and how you can use sp blitz cache to figure things out now once again we’ll see a very low cost query here that low cost is that cost reduction or rather the cost reduction comes from the row goal being introduced over in the warnings column we will see two familiar warnings low cost high cpu and row goals uh again newer versions of sql server where this is available in the uh plant in the plan xml blitz cache will find this for you under expert mode um there are a bunch of checks that don’t get run in non-expert mode just because for most people um there’s enough information with the the like round one checks to go on and uh you know just some of the all the xml plan parsing stuff sucks so like if we can avoid it unless people ask for like additional stuff and i i like to do that now the query plan for this is going to look like nothing happened my goodness how could this have possibly taken 14 seconds or whatever how could this be well bro goals now if we go over here and we look at uh some stuff we will see that indeed we had a whole bunch of cpu and a whole bunch of duration right bad bad bad news and this was uh if we go over a little bit further uh we can see that we’ll take all that time to bring no rose back thanks thanks that’s very helpful anyway um now i don’t want you to think that row goals are all bad i don’t want you to think that they’re horrible things that you should avoid at all that you know you should go stomp out and fight at all costs and also something i forgot to mention earlier is nothing in this uh series is going to teach you how to fix things this is just to show you how how plans might lie to you if you want to learn how to fix things well that’s a whole different set of videos uh but anyway uh mr paul white mr paul white who does his black magic uh has written a series of four part series on row goals uh over here at seagullperformance.com and i would highly suggest uh reading every single one of those in excruciating detail and then leaving a lot of comment questions for paul because paul loves comments adores them anyway uh i am eric darling and uh the next video is a total surprise to everyone i’ll see you there
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.