New Query Transformation Rules in Azure SQLDB
Thanks for watching!
Video Summary
In this video, I dive into the exciting world of Azure SQL DB and share some new transformation rules that caught my eye. After a bit of a technical hiccup with my computer, which almost derailed our session, we managed to connect and explore these rules in detail. Specifically, I highlight two query transformation rules: Logical to Physical Sort and Redundant CSE Spool, which are intriguing for their potential impact on query optimization. The video also delves into a left outer join to left anti-semi-join transformation, showcasing how SQL Server can optimize certain complex queries more efficiently. Along the way, I share some tips and tricks, like forcing a higher compatibility level with use hints, to make these new features work for you.
Full Transcript
Erik Darling here, live and in person with Darling Data and of course Batsmaru who is now my, just a permanent fixture in these videos. I hope that I don’t get sued for briefly having a candy dispenser with a licensed, copyrighted, trademarked image on it. But I just feel like the resemblance is uncanny when you get down to it. So anyway, I took a little break from recording because I had so much built up and when I came back to do this, there were like 40 things wrong with the computer, including that it couldn’t see my microphone anymore. So it’s a wonder that this is even happening for us today because it almost didn’t. So we’re going to talk about some new transformation rules.
I had a reason to use Azure SQL DB finally, not allowed to say what it is, but while I was using Azure SQL DB, I figured, what the hell, Erik, let’s get curious a little. Let’s see if there’s anything new and interesting and exciting alive in Azure SQL DB because oftentimes I do find it is the saddest place on earth. I realize that it’s also sort of a playground for Microsoft to test new things out.
So, you know, guinea pigs of the world, I guess, I guess we need them too. Anyway, before we get into that, let’s talk about you and I and our beautiful journey together. If you like the content that shows up on this channel or even if you don’t, I don’t care.
Leave me alone. You can like, you can comment, you can subscribe. These are all fine things to do.
If you feel that the content on here is of such value to you, such amazing, you know, mind blowing value that you want to support this channel, you can sign up for a membership for as low as $4 a month. There’s a little link you do in the video description that will let you do that.
If this thing will move forward. Yes, there we go. If you would like, oh, wait, that’s that went one too far. Ha ha.
See, I haven’t lost it all yet. If you need anything consulting wise for SQL Server, that is my current profession. That is my title. I guess, I guess, you know, if I want to be really impressive, I could say I’m a founder, but then, you know, you get a bunch of founder mode jokes made about you.
I’m not, I don’t really need that in my life. I’ve got enough problems. So, you know, if you need any of this stuff, here I am. And here you are. And here we are together with you paying me money to make your SQL Server faster.
If you would like some high quality, low cost training, good for life, you can go to the link up there, which is also quite coincidentally in the video description down below. And you can use this discount code to get 75% off.
Again, for life, SQL Server training. It brings it to about 150 US dollars. So, that’s a pretty good deal considering what other people charge you for far crappier training. If you would like to see me live and in person, I only have one event coming up through the end of this year.
November 4th and 5th, I will be at Past Data Summit with Kendra Little doing two miraculous, stupendous, fantastic days of SQL Server performance tuning, wizardry, witchcraft. Probably no miming, which is good.
If there’s an event near you, and that event could maybe use someone like me as a pre-con speaker, let me know what that event is, because maybe I’ll show up in pre-con there. Who knows?
Crazy things happen. With that all out of the way, let’s look at SQL Server stuff. Alright, cool. So, we’re going to come over here, and we’re going to hope that Azure did not have a weird connection thing, because sometimes if I leave this out of the box for too long, it takes a really long time to re-establish the connection, and that gets really frustrating.
And it looks like that’s what’s happening now. So, if you ever, you know, are tempted to use Azure for something, I don’t know what you might want to use Azure for. It’s not a fun place.
If you ever walk away from your computer for a little bit, and you come back, and you just want to run a simple query, sometimes it can take a really long time. And sometimes it’ll take so long that the connection will actually time out.
And sometimes it’ll take so long, you’ll actually have to go turn your Azure SQL DB back on, because it just, it goes away on its own sometimes. So, we’re at almost a minute just to run this thing.
And this is one reason why working in the cloud is a true misery for a lot of people. But, wow, at one minute even, that finished. Okay, great.
So, I’m going to talk about a couple other things that, I don’t know, myself and others have found poking around in Azure SQL DB. One of them is a new use hint. So, Microsoft, you know, those option, you know, max stop, option recompile, whatever.
Apparently, those hints just weren’t enough. So, Microsoft added these use hints. So, option use hint, something that, you know, get, well, I mean, I guess they get used to replace a lot of trace flags.
So, I thought this one showed up, abort query execution. Apparently, this one can be used to abort queries after a certain amount of time. I haven’t quite gotten the syntax on that working yet, nor do I plan to spend a lot of time on it.
Because, the other kind of funny thing about Azure SQL DB is that even though some of this stuff is there, it shows up, it doesn’t mean it’s actually implemented. So, even if you got the syntax absolutely right, SQL Server still might just not do anything with it. It might just be a no-op.
Another kind of funny one that showed up is this one. This is a database scoped configuration setting called Optimized SP Execute SQL. What this does, I don’t know.
I haven’t been able to get it to do anything interesting yet, but that’s there too. What we’re here to talk about, though, are some of the new query transformation rules that I found a little bit earlier today. Specifically, these first two.
Because the first two are the only interesting ones. Well, actually, I’m lying. They’re not the only two interesting ones. They’re just the only ones that I can say anything about right now. Because I haven’t quite figured out what to do with the other two.
Logical to physical sort, I’m not really sure on that one yet. You know, there’s this weird burning eternal hope that someday Microsoft will fix batch mode sorts. So that when they spill, they are as efficient as row mode sorts.
I don’t think that’s going to handle that. It’s a little weird. And then there’s another one down there. Redundant CSE spool.
I’m guessing that that’s a redundant common sub-expression spool. But I don’t quite know yet. But anyway, those first two caught my eye and were interesting. Because I can figure out a little bit what that means.
You have LOJ and ROJ. And that’s going to be left and right outer join. Right? To left anti-semi-join. That is LASJ.
So that is actually a really cool thing. Because what that is, is something that myself and others have been complaining about in SQL Server for a very long time. Now, if you’ve, but I don’t know, I’m going to guess that this might not be the first video you’ve ever watched of mine. If it is, welcome, of course.
But if not, you may have seen me complain in other videos and blog posts about when you do a left join. And so it’s like a very common thing in databases. You need to find rows in one table that are not in another table.
Probably the most common way that gets described to do that in various SQL tutorials is to do a left join from one table to another. And then use the WHERE clause to filter out where the primary key column and the table that you left join to is null. So you only, so that by doing that, you can find rows that exist in the first table, the from table that do not exist in the, the second, the join to table, the left join to table, because the primary key cannot be null when you actually have a join match.
Right? So good stuff there. And that’s what exactly what those rules do.
So let’s, now that we have reestablished a connection to Azure SQL DB, let’s create a couple of tables and put a little bit of data in them. This isn’t a big performance thing. This is just to show you that this does exist and can happen, but you need a little bit, you need to put a little bit of grease on the ball, but a little bit of grease on the ball.
Of course, I mean, you need to, you need to force a higher compatibility level. Now, when I first started messing with this, like it wasn’t happening naturally. Now my database in Azure SQL DB is a compat level 160, which is the highest compat level that Microsoft like has, I don’t know, for a database level setting.
I think I actually didn’t try to change this one to 170. Maybe I should. But anyway, the, the, the, that, that use hint right there that you’re looking at is the thing I was talking about these use hints.
Now, so this is the syntax for those. And with, by doing this, I can force the query optimizer compatibility level to use 170. Now I tried some other stuff first.
I tried the query optimizer hot fixes stuff. So that’s, well, that’s that use hint or trace flag 4199, which is the equivalent there. That didn’t get it to work.
And then I was sort of like, well, you know, these, these things are new. Why not? Why not? Why not see what compat level 170 does? Why not horse around with that, that little thing for a little bit. And what we’re going to do is just, just run these queries real simple, real quick.
We have query plans turned on. And we get zero rows back from both of these because, I mean, let’s be honest. I put the same data in both tables.
Right? So there’s not like the work we’re going to count. There’s no rows that don’t match between these two, right? It’s just row numbers one through 16,000 something. But if we look at the query plans for these, we’ll see up here, this is the query plan pattern that I see a lot when I’m working with clients that, of course, annoys the bejesus out of me.
You have a full scan of one table, a full scan of another table. You have a right outer hash join, which, you know, the right outer part doesn’t bother me so much. That’s the optimizer’s choice.
It can reorder this stuff and do whatever it want with it. And then we have this filter operator after that. Right? And, of course, in this filter operator, this is where we are going to be looking for where this ID is null. Right?
That’s exactly what our where clause prescribed in here. We are saying join I to O on these two IDs where this ID is null. Right?
So that’s exactly what I was talking about as far as the query pattern goes. But down here in Compat Level 170, SQL Server can take a different approach. So that’s the first thing that I’ve done with the query. Rather than use a right outer join and then later filter things out, what SQL Server can do now, or rather what SQL Server’s optimizer can do now with these two glorious, beautiful new rules in them, is it can convert that query to a left anti-semi-jub.
If anyone from the SSMS team is out there, please just put the full names of things in the query plan. It’s okay. It’s okay if you do it.
You can show us the whole thing. We don’t need to guess. We don’t need to tooltips for everything. Just show us the full name for things. Please.
I’m begging you. I’m begging you. Why won’t you give me this one thing? I’m not even asking for dark mode or debuggers like everyone who annoys you. Just show the full name of a query operator. We don’t need the suspense.
We shouldn’t need to hover over this to say, oh yes, that is a left anti-semi-join. How nice. So anyway, that’s about it for this one. I would like to personally thank and give high marks and just convey my personal esteem to whomever on the optimizer team.
It could be multiple whomevers. It could be a plural whomever. I don’t know what that is.
Got these two query rules in there. I do hope that they’ll be allowed outside of compat level 170. Otherwise, it’ll be 15 years before anyone sees them. So, that’s fun.
But yeah, good job, optimizer team. Bad job, SSMS team. The fish. The fish yearn for you.
Anyway. I’m going to go, I’m going to close this file out. You might see that I have several other tabs open here. And I do have some work to do in order to get all these recorded for you.
And so, I’m going to work on those. And that’s all you need to know is that they’ll be here soon. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I hope that, I don’t know, I hope that life is just going grand for you. I’m really, really excited for all the things you have to look forward to. So, anyway, that’s about it for me here.
Time to go record some other stuff. Thank you. I love you. Good night. 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.