Snakey TOP
Video Summary
In this video, I tested my SneakyTops demo in SQL Server 2019 Community Technology Preview 3.1 to see if it was still susceptible to the same issues as before. As you’ll see, I had a bit of an accident with a bottle of water during the recording, but that’s all part of the fun! The main focus was on parameter sniffing and how SQL Server’s optimizer handles top expressions with dynamic parameters. Even though I updated the parameter value to 1000 and tried recompiling the query, SQL Server still guessed 100 rows—just as it did in previous versions. This test didn’t reveal any improvements in SQL Server 2019 regarding this particular issue, which is a bit disappointing but not entirely surprising given the complexity of parameter sniffing scenarios.
Full Transcript
Yeah. So I finished recording the last video on SneakyTops. And what I realized is that I should test my SneakyTops demo in SQL Server 2019 to see if it was still susceptible to SneakyTops. So here we go. On SQL Server 2019, this is CTP 3.1. You can see down there in the corner, maybe, probably. I don’t know. If you stare hard enough, if you squint, I’ll zoom in, I guess. I suppose I’m a nice person. Oh, there we go. Wow. So that, I don’t know really how that worked out, but I’m never zooming again in this thing. So I don’t know what you saw on the screen. Could have been anything, but we’re gonna, we’re gonna leave that as is. You may have seen my Twitter mentions. You may have seen another SSMS window. I don’t care. Anyway. Point is here, I almost knocked over a bottle of water, that no matter what we do, so this expression up here, right, let’s say, right now it’s at 100, right? So if we run this query, this will run for, I don’t know, two and a half seconds. And we get back 100 rows. And if we look at what SQL Server guessed was going to come out of there, it is still 100. Okay, so we have 100 row guess. But if we update, that setting to be 1000, that setting to be 1000, and we run this again, SQL Server’s guess is going to remain at 100, right there. And even if we tag in recompile, and run this, well, SQL Server is still going to guess 100. So, this is not Freud’s fault. So what I wanted to test was the Freud inlining of functions, and see SQL Server would take this and say, hey, we can inline this function. Maybe we can guess at the outcome of it with a recompile hands or without or really just anything. Is there any change here? And there isn’t. So don’t look forward to SQL Server 2019 fixing that problem.
Again, this isn’t like the fault of Freud. This is kind of a weird thing to be doing anyway. And I didn’t, I don’t expect the optimizer to cover every single bizarre scenario that I might encounter. But I did want to, for the sake of completeness with my sneaky top, I did want to see if SQL Server 2019 helped. And it doesn’t look like the guess for a top with an expression is different here. So, and that’s fine. I again, I don’t expect that to, you know, get improved, or change even. I don’t see what the point is. Anyway, thanks for watching. I’m going to open my door and get some air conditioning now. Goodbye. Bye.
Video Summary
In this video, I tested my SneakyTops demo in SQL Server 2019 Community Technology Preview 3.1 to see if it was still susceptible to the same issues as before. As you’ll see, I had a bit of an accident with a bottle of water during the recording, but that’s all part of the fun! The main focus was on parameter sniffing and how SQL Server’s optimizer handles top expressions with dynamic parameters. Even though I updated the parameter value to 1000 and tried recompiling the query, SQL Server still guessed 100 rows—just as it did in previous versions. This test didn’t reveal any improvements in SQL Server 2019 regarding this particular issue, which is a bit disappointing but not entirely surprising given the complexity of parameter sniffing scenarios.
Full Transcript
Yeah. So I finished recording the last video on SneakyTops. And what I realized is that I should test my SneakyTops demo in SQL Server 2019 to see if it was still susceptible to SneakyTops. So here we go. On SQL Server 2019, this is CTP 3.1. You can see down there in the corner, maybe, probably. I don’t know. If you stare hard enough, if you squint, I’ll zoom in, I guess. I suppose I’m a nice person. Oh, there we go. Wow. So that, I don’t know really how that worked out, but I’m never zooming again in this thing. So I don’t know what you saw on the screen. Could have been anything, but we’re gonna, we’re gonna leave that as is. You may have seen my Twitter mentions. You may have seen another SSMS window. I don’t care. Anyway. Point is here, I almost knocked over a bottle of water, that no matter what we do, so this expression up here, right, let’s say, right now it’s at 100, right? So if we run this query, this will run for, I don’t know, two and a half seconds. And we get back 100 rows. And if we look at what SQL Server guessed was going to come out of there, it is still 100. Okay, so we have 100 row guess. But if we update, that setting to be 1000, that setting to be 1000, and we run this again, SQL Server’s guess is going to remain at 100, right there. And even if we tag in recompile, and run this, well, SQL Server is still going to guess 100. So, this is not Freud’s fault. So what I wanted to test was the Freud inlining of functions, and see SQL Server would take this and say, hey, we can inline this function. Maybe we can guess at the outcome of it with a recompile hands or without or really just anything. Is there any change here? And there isn’t. So don’t look forward to SQL Server 2019 fixing that problem.
Again, this isn’t like the fault of Freud. This is kind of a weird thing to be doing anyway. And I didn’t, I don’t expect the optimizer to cover every single bizarre scenario that I might encounter. But I did want to, for the sake of completeness with my sneaky top, I did want to see if SQL Server 2019 helped. And it doesn’t look like the guess for a top with an expression is different here. So, and that’s fine. I again, I don’t expect that to, you know, get improved, or change even. I don’t see what the point is. Anyway, thanks for watching. I’m going to open my door and get some air conditioning now. Goodbye. Bye.
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.