Have you ever developed slowly running queries and wondered how to make your own SQL Server queries go faster? I know I did. It is how I stumbled into my career in performance tuning. In this session, you will learn some T-SQL performance tuning tips that can be used in the office immediately. You will learn how to identify top offender queries, be able to benchmark queries. Finally, we will cover some coding patterns that are developer friendly but are not SQL Server database engine friendly and show you how to rewrite them so they can run faster.
John Sterrett is a Microsoft Data Platform MVP, MCSE: Data Platform, Principal Consultant and the Founder of Procure SQL LLC. Previously, he was a Sr. Database Admin Advisor for Dell, directly responsible for several mission-critical databases behind dell.com. John has presented at many community events, including PASS Summit, SQLRally, 24 Hours of PASS, SQLSaturdays, PASS Chapters, and Virtual Chapter meetings. John is a leader and founder of the HADR Virtual Chapter.
Enjoy the Podcast?
John Sterrett: Hello, everyone. Been having a blast here with the wonderful sessions that we’ve been having today and I’m very thankful that you guys thought that making your queries go faster would be something that you guys wanted to see and voted for it. So I’m definitely hoping to give you guys your money’s worth here. Worst case, if I miss, it’s free. Anyway, so I present at a lot of different events. Before I even say a word about me, I’m more of an attendee than I presenter.
So the first thing I wanted to point out here is all the demos – in fact, there’s actually more demos here, because we only have an hour right, is that correct? Okay – we only have an hour here, so we’re not even going to get through them all. So you’ve got some bonus demos here that you can go get. If I learned anything from Brent in my career, it’s that if you ever present, give free stuff away. So there’s more videos and stuff out there too. And I’m sorry to disappoint everyone, but the Stack Overflow database will not be presented here at all. But to make up for that, because I know it’s important, I am going to try to use the statistics parser. I know one person over there who’s a Cubs fan might be happy about that. Pirates fans usually never try to make Cubs fans happy; Richie knows all about that.
Anyway, a little bit about me – I’ve been around SQL Server for a very long time here in Austin. I help run the user group, help run virtual chapters, speak all around; basically, I love SQL Server. But enough about that; this is what everyone came for. We came for big fun. We came here to do some tuning, so that’s exactly what we are going to do here today.
Now, I’m going to start off with some basics here because they’re very, very important. Everything that’s on this list is because these are what I would call patterns that I constantly see abused over and over and over again. And I hope some people watching this session will maybe be bored because they already know not to do these things. If that is you, I gave you the link at the beginning, just have fun with demos and tweak them; do whatever you want with them. But this is really for [anti] patterns that I see repeated over and over again. And we’ve even got one little bonus on here, AQP, which I’d love to do maybe a whole hour session on that in the future. But I at least want to highlight it because regardless of whether you’re using SQL Server 2017 or a lower version, knowing the difference between multi statement table functions and inline statement functions is pretty important for you. So we definitely want to go through that as well.
So with this, we’re pretty much done with PowerPoint, so if you love PowerPoint, you might be disappointed. We’re going to be in Management Studio pretty much for the rest of our time here. So with that, I’m going to go ahead and get our new little VM here and make it a little bigger. And, Erik, if you can, just let me know if that’s good for everyone to see real quick.
Erik Darling: Well it’s good for me, and that’s what counts.
John Sterrett: There you go. I like that. Well we’ll run with that. If not, I said there’s videos of a lot of this stuff there on the link. So with that, we’re going to go ahead and jump to the very starting point here. And that’s – as you can see, there’s a lot of different demos over here. And with that, we’re going to look at our very first query. And this is our basic introduction. So this is kind of going over a lot of the fundamentals, getting started here with benchmarking. And we’re going to use this as an example to go over an indexing pattern I see repeated over and over and over again.
So we’re going to start off by using this AdventureWorks 2012 database. Maybe I should have just restored it as Stack Overflow and people might not have even noticed. Anyway, we’re going to go ahead and clean up here and drop some indexes in case they’re there to do some little cleanup here. So the very first thing I’m going to talk about – and this is a real good thing if you want to do an apples to apples comparison in a non-production environment. So we’ll talk a little bit and dive through some execution plans, but inside of an execution plan, you’re never going to see how many data pages are in memory. So what we want to do is start from the same exact point every time, and the easiest way to do that is to start with nothing. So we’re going to checkpoint to make sure any dirty pages get persisted to the data files. And then we’re going to drop our clean buffers because there is no command to drop dirty buffers, that I’m aware of.
Erik Darling: Now, you should do this all the time in production, right?
John Sterrett: Yeah, definitely – actually, kind of never…
Erik Darling: And then call a consultant…
John Sterrett: No, please, I love work, but no, don’t do this ever. The main pointy here is, you want to get that same starting point every time as you’re benchmarking and going through the query. So in this first script, we’re going to go over benchmarking here and we’re going to go over an indexing pattern; kind of kill two birds with one stone. So here, I am throwing everything away and we’re going to pop back over here. A little tidbit knowledge, a lot of people just use DBCC FREEPROCCACHE and don’t know that if you put it in the plan handle here, you can actually just get rid of that one plan. So I always love throwing that extra info in there. And next, we’re going to do something here to capture the metrics of how long it takes for a query to run, and then also get the CPU and I/O behind it as well.
Now, for people who are super lazy, like me, I would typically just go into the properties here and automatically always tell this to give me that every single time. But hey, if you like to copy and paste that in there, you can do that too. So with that, we’re going to get that going on. And I’m going to go ahead and start with one of my all-time favorite interview questions. So if anyone’s ever looking for any jobs, you may run across something like this. We’re going to run two queries here and the question is, which one is faster? Now, pro tip – if you start answering by saying any one of these is faster than the other, you’re already failing. The main point here is you need to know a lot about what’s involved with these queries. So I dumbified this down to make it as simple as possible, where we’re just touching one table and we’re selecting everything. And really, the only difference here is one is selecting star, which means give me every single column, whereas with the next one, we’re going after just a couple of columns here.
So if we had a filter, you probably would be curious about indexes, right? And you still can be, because we can still scan across one index, compared to scanning across the whole table here. But we’ll go ahead and run these here. We can see that while this is going here, I outputted our logic over here, to just kind of show you already, from the actual I/O perspective, it’s exactly the same here. And as my execution plans finish here, at a very high basic level, you can see we’re doing a clustered index scan here. So this basically means we’re reading across the whole table. It doesn’t matter if we’re doing select star or selecting a few columns. Because we’re missing maybe an index that could be helpful, we’re going across our whole enchilada here. The only big difference you can see here is the duration on how long it took here.
So surprisingly enough, it actually took you a little bit longer just to get the data on the second one. But the main thing to know is the plans on how you’re getting your data is the same here. And the mean reason why is – already hinted this. if we go look at the real basic help of the table, you’re going to end up seeing that we have no indexes at all over here that are helpful to us. So with that there, you can see, we have no indexes that really help us here, which is exactly why we’re scanning across the whole table. So let’s fix that.
Alright, so the next one here, we’re going to go ahead and add a filter. And I’m not even going to run this here because we already know there’s no index. We’re going to get the same thing. So the next thing we’re going to start is we’re going to go ahead and create our index here. And now, we’re going to go ahead and run our query here, and we’re going to see that this is going to finish about 10 times less I/O. We’re at 3800…
Erik Darling: That’s why it was faster…
John Sterrett: Yeah, pro tip, if you want your query to run real fast, truncating tables may get you the same result, but people just may not like the results.
Erik Darling: Syntax errors, truncating tables, corruption; all good ways to make things finish real quick.
John Sterrett: Another future presentation, ways you may not want to make you queries faster. Alright, so here we have our index here. We made it faster. The one thing I do want to point out here – so if I go back here and I look, we’re going to see that we have an index seek here. So we’re able to actually filter and go after what we want, but we will have this real interesting thing over here called a nested loop. And for any developers who aren’t real familiar with join types, this is what you might refer to as the fancy for loop. For everything up in here, we’re going to go down and do everything down below there.
So the next thing you may want to think is, well how can I reduce the I/O and make this even faster? Well, let’s go ahead and create an index here. Now, hopefully, you might want to redo the index here by using drop existing, so you can add this in here. We’re going to go ahead and do that. And while that’s running here, we’ve got our index, we do this again, we’re going to see, our key lookup went away with our nested loop action as well here. So we got our index seek that we’re running across here.
Alright, so, great, we went through how one query works, but the problem with people’s workloads is you usually never have just one single query that’s running. So what happens when we have this case happen here? So same exact query, the only difference is we’re adding another name over here. So I’m going to go ahead and run this guy. And what you should already be expecting is, we’re going to go back to that key lookup, because now, we still have an additional column that is not coming over here. So this is exactly as we expected. As we zoom in here, our for loop, going straight in, doing everything down in here.
So, what do most people do to fix this? Most people end up adding another index. And so, this is the scenario where we end up having lots and lots of duplicate indexes where you actually think you end up helping your code; you might be hurting your total workload…
Erik Darling: All these indexes happen to have DTA in the name.
John Sterrett: Yes, or better yet, the default name that gets built if you look at that execution plan and…
Erik Darling: Name of missing index…
John Sterrett: Yes. So as you can see here, we’re going to go back right over here. And if we go back to our execution plan here, we’re going to see that this guy – index sequence again. So once again, the problem is that, if we go look at this query here, we’re going to actually see that we now have what’s known as a partial duplicate. And that’s a partial duplicate because we have two indexes and one overlaps the other down here. So the key thing to note here is that the second one would actually cover both of them. So you could be good to go there.
So, for example, here, if we wanted to say – due to the way my VM is running right now, I’m not even going to try to run that 100 times. But as this goes through, we can then look at a DMV query and we can get some more great information on the actual size of the index, how many times the index has been used. And from there, once that’s done here, we can drive right here into this and see exactly here how many times we’re using it. And as we would use more, we’d end up just using our second index over there.
So the moral of the story there is actually know your indexes and don’t always just grab indexes that you might see in your execution plan and create a scenario where you’ve got a lot of storage use. And the other key thing that a lot of people forget that I experience in the real world is, the more indexes you have on a table, the harder the optimizer works. And sometimes, it just ends up flat out failing at picking good ones. I’ve seen some scenarios where you have like 10 or more that start with the same leading column, and the optimizer is just like randomly throwing a dart at the board at that point.
Alright, so that one, we kind of dug into this one here. The next one we’re going to jump into, and this is where we’re going to start really diving into – we’re going to identify a pattern. We’re going to know. It’s one we like to use, because we’re friendly and comfortable with it, but these are ones that optimizers truly hate. And we’re going to show you how you can do something a little different to get the same result. So with that, we’re going to pop over to our inline function here.
Alright, so we’re going to go on to inline functions. And this is one I like to call my little silent performance killer because a lot of the basics, that we won’t go over, for benchmarking, like looking at set statistics, looking at execution plans, you’re going to really get confused because you’re not going to see this in there. But I’m going to show you exactly that there. So we have our scalar function here. And basically, all we’re doing here is I’m going to say, for a product ID, I just want to know, what was the max quantity of any order for that product ID?
So once again, we’re going to go through our normal apples to apples comparison, because we’re not in production. We’re going to then go ahead and run our function here, and I want to make sure profiler is running here. And the reason why is because I’m going to show you what’s missing from what you’re going to end up seeing with looking at your statistics here and actually looking at your execution plan. So this guy here is going to take a bit of time to run. And then, once again, remember, all we’re doing is calling this function. The other thing I want to do is point out, notice that this function is in the select part, so it’s not even part of the filter. Even if your scalar function is in the select part of your query, this is going to actually drive you to do what we know as row by row processing.
So this is actually going through on here. And for every single little row, it is running the code inside of this function until this completes over here. So this is definitely not a fun thing and this is something I see repeated over and over and over again. In fact, even recently, to kind of piggyback of something Martin said, who just presented last, there was an ERP system that I was helping looking someone with a view, and they basically had a scalar function that was converting date times. And they didn’t even need it at all because everyone works in the same area. And I just told them, if we pull this out, this query ended up going from well over a minute to a much, much shorter time because we had hundreds of thousands of rows that you would see in profiler here being collected for one single select statement running. So I’m going to go ahead and pause this because we don’t really need this anymore. The whole goal of running profiler today was just to show you the row by row processing there. And hopefully, that will speed this guy up.
So okay, so what have we done so far with this example? We’ve identified a pain point. Please, whatever you do, try not to use scalar functions here, especially if you do not know how much data is going to be involved in the query because as someone who loves hip-hop, one of my favorite rappers would say, if they did SQL Server, more rows, more problems because, as you pump more things inside of here, this is just going to get worse and worse. I’m actually just going to kill this today because we’ve got a lot of fun stuff to go through. We have the recorded results in here, and of course, everyone can grab the sample code.
Let me see if I can go get our estimated plan for this to show what I wanted to show you here with the plan. And that’s basically just the fact that you’re not going to see anything at all from our function in here. This whole sales order enlarged detail table – and by the way, if a table is named enlarged, it’s probably big on purpose – you’re not going to see that anywhere at all in this execution plan here. And that’s the whole point here. You look here and it seems like it’s pretty fast. And we look at the I/O when this actually runs; it’s only four. But we saw, normally it takes about 30 seconds, and that’s where a lot of people who see this for the first time start to get really confused because you kind of know by looking at the code, you’re expecting something, but you don’t see it at all. And that’s because it’s row by row processing and you’re just going to see that in a profiler trace.
Alright, folks, so how do we make this better? Because our whole goal today is to help you make your code go faster. The easy way here is actually to take that scalar function and see if you could push it into a table function here. So here, this is actually known as an inline table function because we’re just returning it straight out of here, instead of a multi select table valued function. If you’re curious about what those two are, don’t worry; I mention it because we are going to go through it here next. But first, we’re just going to go through here and show you that by doing this, we’re actually going to be able to push a set of data instead of doing that row by row processing and have this run a lot quicker.
So all we’re doing here is a cross apply because I want to take my set of product IDs, push it in here, and get a set base back of those max quantity here. So if I run this guy here, this guy here will end up seeing actually all of our I/O here and we’re going to see that this normally would take a second or two. Tell you what I’m going to kill another VM here, just to see if that helps us. There we go, I think I found the performance problem.
Erik Darling: I bet that VM is doing a Windows update or something.
John Sterrett: It’s doing something over there. But here, there’s a couple of things to note. First of all, we have arrows. So we’re able to go parallel over here. And then also you’re going to see, we actually have our reference over to the enlarged detailed table over here. So everything is baked in here. We can see our I/O, even though that normally takes about a second here. So, the lesson learned here is, using a table function could be your best friend here because this could be a way whereby you can get out of that row by row processing and actually do set-based logic off of that as well. And if you really don’t like cross apply, there’s another example in the demo; you could do a right join as well.
Alright, so to recap here on this one, we identified that scalar functions are evil, and why they’re evil, because they’re doing row by row processing. And as a developer, because I started as one, it makes a lot of sense on why you’d want to use it. You’re like, oh it’s a reasonable code object, easy to use. You could use it for a single object or a whole set. But the optimizer really doesn’t like that and forces it to do row by row processing.
Erik Darling: And every other language out there in the world, and functions are great things, functions are wonderful, and then you get into SQL Server and it’s like, nope, I’m pulling that rug from you.
John Sterrett: Exactly. Alright, so now we’re going to dive into something that I think is pretty cool and I hope everyone enjoys. And this is going to be looking at the difference between – now, we know table function is better than scalar, but we know there’s two main options of a multi select table function or an inline select table function. And we’re going to go through both of those today because I realize that more people are moving to 17, but there’s still quite a few people out there on 2005. So I want to show you something that can be helpful for you as well here. So we’ll go through that first and I’m just setting this to before 17. Don’t worry, we’ll come back to 17, I promise.
And we’re going to use this really cool database that’s not Stack Overflow. It’s called WideWorldImporters here, because it is AdventureWorks 3.0. So we’ve got that in here. Okay, so now, into this multi select table valued function. And this gets used a lot. I see this all over the place as well. And the big difference here is, you’re defining a table and then you are doing logic. So you can actually have more stuff in here if you want to. But the end goal, at some point, is you’re inserting some kind of data into here and then you’re going to return that data. That’s the whole concept between a multi statement table valued function, in case anyone didn’t know.
Alright, so we’re going to actually now turn on our execution plan, which I’ve got there. I love these notes to remind me what to do. It’s like I’m telling myself what I need to do.
Erik Darling: I have those too and I never follow them… I feel dirty just looking at that function.
John Sterrett: Me too. I mean, that’s the other thing. Oh boy, I could rant about this forever, so I should probably stop myself because I know we’ve got code to get through. So basically, now we have that table function, we are going to use it and we’re going to join it to other data. And we’re doing aggregates and this is just where the optimizer wants to go drink, because this is not fun here. But we’re going to go ahead and run this here because there’s some things I definitely want to point out here.
So this is going to go ahead and run and do its thing here, and if you’re running on 17, you’re going to see some interesting things. But even before then, you’re going to see some interesting things. So the very first thing here with a multi statement function here is you’re actually going to see that pop up in here. Now, the other thing that I want to focus on here is the estimated statistics that you’re going to see here is going to vary based on you major version of SQL Server. So before 2014, this would always be one. For some reason, in 14, this was changed to be 100, because 100 is 99 greater than one. I believe that math is correct. Thanks, I got confirmation from Erik, so I know I’m good.
Erik Darling: I’m not a math guy, but it makes sense to me.
John Sterrett: Alright, so our problem here is we’re actually going to pump in 231,412 rows into a plan that’s really expecting and thinking there’s only going to be 100. And this is where things don’t operate well. So we talked about a nested loop already, and I’m going to show you one of the worst cases with a nested loop, and that’s when you’ve got a big fat line like this going into this. So remember, this is your for loop, so for everything here, do what’s underneath it. So that’s the first thing that’s real interesting here.
The next one is that warning, warning, warning sign, and this is where I want to point out, we only granted about 1MB of memory here. And the reason why is because of the statistics. And the optimizer, when it built the plan, only thinks that 100 rows are coming in. but obviously, we’re pumping a lot more than that. so this is where I’d hope that Brent or Erik could cue up the toilet flushing sound because we’re tempdb in here because we have to because we estimated the small amount of memory and we need a lot more that what we’re estimating here. And so that’s a problem over there and you can get confirmation of that too by going over here and looking at – so you can see here that you have basically a 1MB memory grant here. So we can go ahead and look at the results here, which, by the way, I’m going to do my basic math and just call this a million. We’re going to round it up a little bit here, guys, keep it real easy.
We took about nine seconds here for this whole thing to run. So just to recap here – and by the way, I’m correcting my own notes in the middle of the demo, you get 100 rows on 14 and 16, not just 16. And now 17 I can add to as well, and probably 18 and on and on and on. The whole point here is we have our table spill here. We’re using, basically, 1MB of memory. So we can see, we’re probably not getting a good plan because we’re not having good stats, just by the way how our table function is going to operate and how there’s basic assumptions made about it.
Erik Darling: I always love showing people, with the stats I/O on those, is when you see that temporary object in there, that’s the backing temporary object for the table variable. So everyone who thinks that table variables are in memory only, they’re not. They get stuck in tempdb just like everything else.
John Sterrett: Yes, they do. Alright, so now, we’re going to take a look at inline table value functions here. So this is similar to the example we had in the last demo. And all we’re doing is selecting joining these two tables, so let’s just do that. We don’t need to create a table. I mean. Who wants to write more code? I mean, this is just real basic and we’re going to see a huge difference between these two. So this exact statement here, there’s nothing different other than the fact we’re using the inline view versus the multi select. So just to recap again, the inline select is us just having our select and returning it; nothing else, no table creation. Where if we go all the way back up here, this is where we’re actually declaring. We could do a whole bunch of stuff. Somewhere, we’re going to insert in here and then return. That’s the big difference between the two.
Now, we’re going to see the database engine and what it thinks and why it’s very different. So this guy is going to run and we’re going to notice quite a few different things. One, the actual inline table function here is going to act like a view. Did I do that right? It helps if I run the right one. Alright, take two here.We’re actually going to run the inline one there. Actually, that was a good demo because I was showing you that in the multi statement one, you see the multi statement again in the execution plan. That’s what happened there. I’m just going to wink because we all know that’s not true.
Going back over here to where I wanted to go though, we’re going to see a couple of things here. This is your inline function. Notice there is no reference to the function. This is just like a view for you. And guess what, you have this cool thing called statistics. So because we know we’re pumping in a lot more data, we’re not doing the for loop here, we’re doing a hash match join over here.So this is actually allowing you to use the optimizer to its fullest. And once again, if we actually go look over here, we’re going to see we have pretty good stats. Are they perfect? Maybe not. But they’re a lot better than a hundred here and because of that we’re actually getting hash matches over here as we’re joining.
Now, this is the other cool thing here. Remember the basic a million I/Os here? Here we’re seeing that we’re pretty much under 300 here and instead of nine seconds we’re about at a second. So this is something that before 2017, you don’t need this AQP thing we’re going to talk a little bit next. This will automatically help you out and make life better before then by just knowing that if you’re going to be joining to other tables and doing aggregates, you definitely want to use an inline table function instead of multi select. Really in general for most things, I always want to really use an inline. Just me being lazy I hate writing extra code when I don’t need to. The optimizer kind of struggles at actually giving you great plans with them too.
Alright, so I’m sure most people have heard that when you upgrade the SQL Server everything just goes faster, right? It’s this big slogan marketing and I’m going to show you a case – actually kind of can. And it’s pretty cool to see the way how execution plans are being generated are going to be changed over time, and there’s actually going to be some intelligence to say you know, I’m going to stop during part of building this plan and I’m going to do some things first and then I’m going to continue. And that’s kind of where they start here with what I’m going to show you on 17 here with the exact same queries here.
So first we’re going to go ahead and we’re going to take a look at that multi statement table value function here, and just to remind everyone, when we did this before changing the compatibility mode, what we saw there was very bad stats, we saw 100 rows that was there in the stats, we saw that nested loop, we saw that we were missing memory and we had to go to the tempdb, which, you know, if you have that on a USB drive or here’s a better example. If you’re running multiple VMs on a laptop, it may run really, really slow. Just like how some of the demos are going slow on mine when I had multiple going there. Because I’m actually needing to go to disk, there’s the whole point that I’m trying to get to.
So we’re going to run this and see how is this different by changing just our compatibility mode. So saying we’re running this under 2017 compatibility mode. One, it’s running a bit faster, but notice things have gone away. We actually have this cool concept here where while the execution plan is being built, it notices there’s a multi select table value function. Maybe we should hit the pause button, get this cool thing called statistics, then build out the rest of the plan. So this is like, really cool because as a developer, you’re not changing any code. You’re making the DBAs upgrade your database and it’s running faster. But the main thing to point out here is even with the multi select table value function, this gets better because we’re actually going to get the stats here first, and that’s going to allow us then to get the right join operator here. So we no longer have that nested loop. We’re going to do a hash here and if we actually go look at our memory here, we’re going to see that this is quite a bit different too. So we went up by a factor of four there, so we went up to about 4MB instead of the 1MB there. And obviously, there’s no [spool] to tempdb as well. So all good things and that’s without changing a single line of your code here.
So the next thing we’re going to end up doing here is we’re going to look at that inline select function here and this is where things are going to get pretty wonky if you haven’t seen an adaptive query plan operator here change. So I’m going to get into that in a second. So the cool thing that used to always know for sure, if there’s anything we knew about SQL Server – and I’ll go to an example here is we have operators like this. You always have two coming in, one going out. This is like, basic, all type of the three table join operators, right? Guess what? You probably already notice seeing that’s a little different over here. So we got this really cool thing and this is because I’m using columnstore over here, where the first one there you didn’t have to have it but here you have to have columnstore and this is doing an adaptive join here, where basically this guy is always going to come in. Then we’re going to have a threshold here, so I like to always explain this like parallelism because a lot of developers and DBAs understand that. When a query goes parallel, it’s because there’s a cost threshold number, the cost needed a higher number to that so that made it eligible to go parallel.
We have a similar concept with this adaptive join where there’s actually a row number, and if the input there is higher than that, then it would do a hash. And if it’s lower than that, then it would do the nested loop. The main thing here that’s a little tricky is these two, only one of them will be used but the plan will be built for both of them. And we’re going to go look at some of the properties of that too. So as I mentioned here, think of this – for me, the easiest way to relate this to people is think of it like parallelism because this is your cost threshold. Here we call it adaptive threshold rows. Our actual rows is higher than that so we are actually going to do a hash match there. And that’s basically breaking it down as simple as I can on how is it picking between the hash and how is it picking between a nested loop.
And then once again, if we actually go over here, we’re going to see actual number of rows, zero, that’s because it didn’t use that because it would use that if it was doing a nested loop. And here it’s actually going through and scanning across it all for the hash match. So hopefully that didn’t confuse anyone, I don’t know, Erik, we could take a question or two on that if something popped in people were curious about. I don’t want to go into a lot of detail because we have a lot of other patterns and this may be a great future whole hour presentation in the future.
Erik Darling: It’s a huge topic, advanced query processing. I hear it’s only going to get wilder. Maybe that hour presentation is a good place for it.
John Sterrett: I would think so. The other thing I want to point out is I we kind of have the opposite effect of this. So remember before we were using tempdb because we only had 1MB totally, here we got roughly around 73 – I’m not a math major and yes, I know 1024 times the number here, but the point I want to throw out here is another thing that’s added, if you run the same plan over and over again – I should have put a little go here but I’ll just run this a few times. We should see that number change. So a nice thing too is it’ll actually over time here – but what should happen is it’ll actually adjust its memory usage for you there as well. So…
Erik Darling: Advanced, they can ruin demos in real time.
John Sterrett: Yes, it definitely can. There we go. I just didn’t run it enough. But there you can see we went from 70 to 50 so you could see – it’s actually going to help you with memory usage, so that way you can use your memory a lot more efficiently as well. So with that, we kind of quickly touched on to three different parts of adaptive query processing. But the main thing I wanted to show you is even before you get to 17, using an inline select table value function can be very, very helpful for you. Where you can instead of a table, our multi select table value function, especially when you’re joining it to other tables and doing aggregates on top of that, it ends up working a lot better for you.
So with that we’re going to go ahead and jump into this very old concept called sargability. And we went over indexes to start here because now we’re going to show you reasons why you might not be using those indexes you created yourself. So first example here, let’s say if we want to find all the people that have Smith, if you put wildcards around that, logically the optimizer isn’t going to know what it can skip so it’s going to have to scan everything. On the other hand, if we start with actually values here, we can go through our B-tree, assuming that you are using a B-tree based index here, which unless you’re using columnstore, you are. You can see that we’re seeking here and we’re scanning over here and very basic, this isn’t 100% true, but in a lot of cases, and I gave you one where you don’t see everything in here, seeing 97% of your cost up here and three down here doing the same thing, it’s easier to understand this is probably your bad one. And you can even see from the I/O as well that we’re doing almost 10 times more I/O here than there.
So for time, we definitely won’t run and hit through all of these but I just want to show them to you and once again, everyone can download the code and play with this on their time. Another perfect example is if you wanted to start by first letter or last name. If we actually have a function wrapped around your column here, this is going to change the way how the optimizer’s going to work too. So once again, we’re actually going to have to get this – I’m even going to change this to red here just to highlight that this is not a good thing here. We have to get this, then we have to do this substring here before we can actually compare your value here. What you really want to do is flip this around and have nothing around here if you can and your literal or your parameter over here, that’s where you want to put your logic and you don’t want to break where it starts like we did at the beginning with the wildcard in front of it.
Erik Darling: Do you want to take a question from a Webex chat or from GoToWebinar chat, rather?
John Sterrett: Sure, we can do that.
Erik Darling: There’s a question from a Siti – I’m sorry about that last name, I’m not going to try to do that, it’d probably just be disrespectful. But it’s have you seen 2017 consuming more memory compared to 2012 or asking for bigger memory grants than 2012?
John Sterrett: There is cases where – it’s a good question. There are some cases where I definitely have seen cases where you can have different memory grants. My answer to you would be – and this is one I recommend for all people that would do upgrades is actually do workload replays. So one thing I always encourage a lot of my customers, especially the ones that have forward facing websites with lots and lots of traffic coming through where a lot of money is dependent upon that traffic, you never want to upgrade and not know what you’re going to get when you do the upgrades. So I would really encourage for you and your workload to actually capture some of that replay with a tool like distributed replay, measure and compare those two and that way you know exactly what you’re going to get for your workload. Not sure if that answered the question the right way but that would be my recommendation for you Siti.
Erik Darling: Yeah, just be careful with the new cardinality estimate there too. If you see big swings in memory you’re asking for then maybe keep that database in the older compatibility level.
John Sterrett: Yes, exactly. And this isn’t a query store talk but you can also use query store to flip it, see what inverted, and then magically try to hope that you can pin back to the old plan if you didn’t go through the process of planning and testing.
But alright, so next we’re going to talk a little bit about dates here. And this is because this is another example I see over and over again. We have this cool thing called date time but people really only ever want a date or a time, so they end up using instead of the right data type, they have some fun logic like this that’s going to go through and convert it. So once again, this guy here, not much different than this guy, only this guy doesn’t have stuff wrapped around it so this one would use your index but the top one wouldn’t. Then, of course, this bottom one I added here just because it’s actually different logic. Sometimes people misunderstand it that this is inclusive so this is going to include that first time value on the next day, you might get some different rows there.
Looking here for year, same concept. Top one you’re going to be scanning, second one you end up being seeking. Interesting one, I’m going to talk about this a little bit here when I get to my reporting query that I want to focus on here. We’re going to actually go over a case where we have parameters coming in and here I’m keeping it very, very simple. Really only have one and I’m actually literals here. But we have coalesce here because basically, I want to use this, I want to see if we have data that equals this here. But if something is null for this column, well then I’m just going to force basically a one to one. We’re going to look at the same things, we’re going to get that. This is a fancy way of writing this. Or this. The big difference between these two, there’s two differences I see. One is you can add more things in here. So we can have different columns and go on and on if we wanted to where here, you just have one. It’s either this – if it’s null then we want you to use this.
But from a performance standpoint, there’s actually a difference here. Remember where I said the fact that you have this wrapper here and that’s going to cause you to actually scan, and in this case because this is a T-SQL function, we’re actually going to be able to seek because it’s smart enough to know that and work around that. So just wanted to also show you this case where there is some functions where you can actually wrap around that and it’ll still seek because they are T-SQL functions that the optimizer’s built around to know how to handle them. And this is no different than that.
The last one here, and anyone who works with ORMs, you probably know this better than anyone, especially if you’re on the DBA side and that’s implicit conversions here. Going through an example here where basically, you have a column that you’re going to filter against, and you’re going to use a different data type. So in this case, we’re doing nvarchar versus varchar and we have the opposite here. And we’re going to see the bottom one here, you’re going to be forced to scan and you’re going to get your nice warning over here when this finishes as well. So that big nasty warning, warning, warning, and this is telling you you have an implicit conversion time here and that may give you some bad performance there with that as well. Same thing here, we can look at the I/O and you’ll see that there.
So hopefully you guys here have understood some basics about sargability and just because you have indexes doesn’t mean your code is always going to use them and you might be shooting yourself in the foot by wrapping things around them there or how you’re actually doing your comparisons there. So hopefully that is helpful with that.
And let’s see, where are we? The next thing I wanted to jump into was table variables versus temp tables and we’ll kind of go over this one here pretty quick because we already talked about it a little bit and a lot of the stuff with table variables kind of applied with some of the examples we saw before here, but I’m just going to go ahead and jump here. We’re going to get this guy going. That’s not good. I would like to connect, pretty please. I think we’re back in there.
Alright, so table variables, there’s a lot of myths and blog posts and maybe half of them are right, the other half are just flat out wrong. So part of this demo is to kind of go over some of those like for example, people think when you have a table variable, you can’t have an index. Well, guess what we’re doing here. We’re having a clustered index that you can have there. But we’re going to go ahead and run this and basically we’re inserting some data into it and then we’re joining across it. And we already talked a little bit about bad stats based on what you’re doing and here we’re going to actually show you and we’re looking at this guy. This is another example of us because reason table variable, it’s going to assume lower rows than you actually have. And we can see here we’re doing a nested loop join off of the other part over here.
So the other thing to point out is a lot of people think, well, I can’t get good stats. Well, you can get better stats if you want by using this thing called option recompile, even with a table variable. So I just want to go ahead and show that, and we’ll see that that will improve. And we go look over here as well. So at least now we see that we’re getting better stats there. But now we’re going to flip this over to a temp table here. So exact same demo, the only difference here is I’m doing a temp table here. So we’re going to see we have good stats throughout that, and part of the thing to point out is you can see that our operator is even changing because of it.
So once again, keep in mind here that if you’re going to use table variables, you want to do that where you know exactly how much data you’re always going to have. It’s very, very small. If you’re not [nested] there, it’s usually way better off with doing temp tables. Because you’re going to end up having good stats and end up building better execution plans there.
So with that we’re going to go on to the next one here, and this is CTEs versus temp tables. And the reason why I want to go through this one is because people think, you know, CTEs are either great or they’re bad. They could be both. It’s really how you use them. And the problem I see is a lot of people love to use them by chaining them together, and that’s where things can get wonky here. So I’m going to start off here with real basic CTE, and the main point to show you is there’s no difference at all between these two statements here. I’m using a CTE and selecting from it, well, guess what, I’m doing the same thing here. The plan will be exactly the same. People that say CTEs are evil and you should never use them, that’s not really true. It’s how you are using them.
And just quickly showing you here, these are exactly the same, no hidden smoke and mirrors, nothing different here. Same index recommendation across both. One of these days I’ll do some indexing over here but then my demos won’t run slower, which is what I was wanting to do and have more I/O. But alright, now we’re going to get into where CTEs can get scary. And I bring this up because I see this all the time in different ways. I mean, there was one time I actually got a call from a company, they were needing to run a process, they’d had a CTE and I looked at it and it was 30 levels deep with recursive all of it, and you’d think that’s bad enough. But then it went into a merge on top of that and I was basically told that this query ran for three days and then they killed it. Not it finished in three days. It’s just after three days they killed it, and then I looked at it and I just asked them you know, “Have you guys tried temp tables? Let’s do that and see if it works.” And so we went from all of that to the whole thing finishing in under five minutes without doing anything else.
So the whole point I’m going to show you here is this is where I get worried. When I see comment and then other CTEs and this thing keeps going on and on and on, especially when you’re doing real basic level stuff to be pretty that you could probably do in the beginning statement on its own, this is where things get bad. And it gets really hard to look and troubleshoot too. Most of the plans I’ve shown you up to this point have been pretty easy to read.
Well folks, that ends here. Because when we look at this one here, we’re going to see that we’re going to actually need to scroll and this is going to be a pain because we’re first here, we’re getting an order quantity, then over here we’re doing sales quota, looking at that, then we’re going through here and doing net sales average calculating and really, the whole goal here is to get a pretty select statement at the end. So this is better than merge but there’s not really a whole lot of value that we’re getting into this here.
Erik Darling: Just start billing by the scroll.
John Sterrett: Yes, bill by the scroll. That’s almost as good as trying to bill by I/Os saved but it’s – and it’s close. Anyway here, so hopefully this guy will run. The whole point is you can see this is very, very bad. I’m going to cancel it here and I’m just going to see if I can get this to estimate a plan here. Once again, everyone can grab the code here. also, I did check before we started and there is the link on the GoTo site so if you actually go to this session over there, the link to get everything is up over there too.
But the whole point here is I just want to show you that this plan is very nasty. It’s pretty hard to actually troubleshoot and read because you’re scrolling all over the place here. In fact, they have this nice little plus thing here that you got to use just to get around it all and if anybody actually likes working with plans like this, I wish you a lot of fun with these because I hope to not see these. Because these just get harder and harder to troubleshoot. So we already know that temp tables from our last example is better. I included the table variables here just in case anyone wanted to play around and look with it. You can do so.
But the next thing here, we’re just going to do the exact same thing just with temp tables here, and I’m going to go here and hope that this guy finishes. And we’re going to insert the steps into it. So really not doing anything to make it run faster by changing the process other than the fact that it’s now just table variables instead of temp. I’m sorry, temp table instead of table variables. So once this guy gets going here, what we’re going to end up seeing is this is all broken down step into step at least. So one, it’s going to be real easy for us to identify okay, where’s the pain and where in this massive process do we really need to focus on. Then it’s also going to break it up into those smaller steps, which is going to make the optimizer works faster because hey, this guy got to run and finish. I didn’t kill it like our first one. And the other thing here is look, 100%, big fat lines, I may want to start here. And the other one we saw this big massive thing that was kind of really hard to troubleshoot and dig into. So we’re going to go ahead and end that guy there.
And yeah, I’m sure there’s a few people watching this and they’re probably wondering and thinking right now like, who actually does this and why would we need this, but you’d be surprised how many times I see patterns like this repeated. So with that, we’ll go into our last one here, which is what I would like to call my reporting query. Because Erik, I think we got about five minutes and then time for some questions? Is that about right?
Erik Darling: Yeah, you can do five or 10. The next one starts at 2:15 so if you want to talk a little bit you can.
John Sterrett: I might sneak a bonus one in there, we’ll see.
Erik Darling: Everyone likes a bonus one.
John Sterrett: This is true. This is true. So I talked a little bit earlier about having flexibility, right? Everyone wants flexibility in their code so that way they don’t have to write a whole lot more of it. And one area where I kind of see this get real interesting is what I would call kind of a reporting query. To simplify this, I only include a couple parameters, but there’s cases where this can be like, hundreds of them. And the whole real goal is that the user can use one or many of these to filter data they get back. And depending on how you do this, the optimizer can go insane. So we’re going to kind of cover a couple ways on how this works. Our very first one here is basically we’re selecting, we’re doing a lot of left joins because we may filter on them, we may not, and we’re going to use just coalesce here because we’re going to say hey, if I have this parameter, let’s filter off it. If not, we’re going to filter off the left hand side so it’s always going to equal each other there, unless it’s null and we’re not looking for that. But for that here, we’re going to go ahead and I’m going to free the proc cache, I’m going to go ahead and get rid of all of our plans because we’re not in production, and I’m going to turn on my statistics here, and then I’m going to run these two twice back to back here.
Erik Darling: You’re missing the one thing that all of those queries start with. Distinct. All select distinct.
John Sterrett: Yes, yes. I think we’re also missing this thing called the procedure here.
Erik Darling: Little thing.
John Sterrett: That didn’t give away – there were a little few red lines here.
Erik Darling: You’re a master. That’s why. Context is everything.
John Sterrett: Yes it is, yes it is. Good catch. Good catch.
Erik Darling: I’ll contribute today.
John Sterrett: There we go. You get internet points here, I like this, thank you. It’s team work.
Erik Darling: I already spent them at the bar.
John Sterrett: Well, then I guess maybe you won’t join us after this then. Seems like you had pregame.
Erik Darling: If you’re buying, we’re cool.
John Sterrett: Alright. I can do that. Let’s go here. Alright, back to the actual demo, folks. I’m going to run these two back to back here and the main point I want to drive home here is they’re different parameters and there’s really much different queries that should probably run because of these here. As this is going, we got left joins here and we may or may not use filters at all based on what’s going on here. And so once this finishes and does its thing here, we’re going to see another great interview question, and that is when you look at execution plans, do you start from left to right or right to left? And the answer is it kind of depends on what you really want to look for. And the main thing I want to show you here is our parameters that we’re using here. So when we first kicked this bad boy off, we’re using Smith here as our parameter and that’s what’s used here and everything’s good and happy there. I mean, we saw the hash match here because we’re going across a large amount of data here and we’re not able to do everything that we need there.
The next one though, we have that same plan. Even if we have different parameters and we really only want to touch a small subset, we have the same plan because that’s one of the things that usually makes SQL Server great is you’re not building the execution plan every single time a statement runs. So we’re going to go over here and I just want to drive that home by looking at our parameters here, and we’re going to go ahead and see that – zoom in on that so we get our fun little fact here – that when we built the execution plan, it was all built around only having Smith and nothing else. Well, now we’re using completely different parameters here so we’re not having a good execution plan and on top of that, in case you didn’t notice, if we go look back at our queries, we’re still joining to other stuff we don’t even need in the first place because we’re not filtering off of them. So all kinds of interesting stuff going on there.
So we’re going to go ahead and take a look at this next one here where we’re going to use the super evil thing people say you should never use called dynamic SQL. And here’s a case where it is correct, it can actually be your friend because we can actually join tables, not do left joins when we actually need them. Another nice tip I like to do whenever I’m using dynamic SQL because I hate trying to figure out where the code was, I like to be sneaky and throw a comment that helps me that I know exactly like, where to go digging for something in there too. But the main point here is we’re doing the select statement, we’re joining here, and then we’re just based on the parameters, if they’re used, we’re going to add those joins in. If they’re not used, they’re not.
Same thing on the tables there. If the tables are used for a part of parameters and they’re not needed, we don’t need those joins. So let’s only do exactly what we really need to here. The other nice thing that you’re going to see here is that I actually have the parameter in the dynamic SQL. So this will parameterize it for us so that way we’ll have one plan for every set of variables that are used here. The other nice thing here about dynamic SQL, using the execute SQL statement is you don’t have to use them all. So even though you declare the and you put them all in there, when you actually execute them, you can have a subset of them as well.
So with that, we’re going to go ahead and do our same thing here and we should see different results here. Hey, this guy ran faster. I noticed that. So we notice our plans are different too. The first one, we needed a lot more of the tables, we’re going across them here. But on our bottom one here, we’re only touching exactly what we need here, so this is a lot more efficient there for us. And it’s still parameterized so you’re not going to go through and compile and run it every time with the same set of parameters there that you’re using.
So the last one I wanted to show here would be kind of the worst-case scenario, and I do see this as well. And this is where you’re opening yourself up for a lot of trouble, especially someone who wants to pull a bobby-tables on you here and manipulate your actual parameter string that you have in there. And that’s where you actually – you’re doing the dynamic SQL thing but you feel like you really, really need everything as a string so you’re converting it into the string and this is where people can get a little interesting with running statements you never knew were actually running. So definitely wanted to point that out there as well for you.
Alright, so with that, I think we got one time where we got a little bit of time to kind of go through another one here, which I could call the running totals query. And with this, we’ll go ahead and we’ll talk about it. We’ll see how quick this runs. It may run too long for us to go through the bad part of this, but basically what is happening is we want a running table. So to show you kind of what we’re going after here, I’ll run this statement after it connects. I should expect this when I say we’re going to do the bonus query. We get one extra one here and we’re going to spend some time on Management Studio freezing up here. There we go.
But so the whole goal that we’re wanting to do here is basically we just want to have a running total that’s going. So for every one of these rows, we’re going to kind of add them up as we go along so you can see that. Kind of pretty important in areas where maybe money might be involved as an example. So here’s a neat way on how you could do this, and yes, I added this filter here just to try to lower the amount of rows that gets processed. That’s the only reason why that number is there. But one way you could do this is by joining the table to itself and then summing up like this as it’s iterating and going through itself. And this one, it’s no fun as you can see here by all the I/O that’s going on.
Next attempt here, and this is a real cool one because I always kind of like hearing people say generic statements that they think are always true and you can find areas where they’re wrong. A lot of times people say especially DBAs never use a cursor. Well, this can actually make it run faster, and if you’re on your older versions of SQL, this might be a better way for you to go here. And this is just processing – we’re only reading it once so we’re doing a fast forward and going through and then doing its actual thing to sum up your total and get that there at the end.
Now, where this actually gets better is with windows functions. So I just wanted to point this out that you can actually do this in one statement here as it’s going and we’ll go ahead and run this guy here. Once again, in case anyone was late and they missed it, all the samples, you can go to the GroupBy website, if you actually go to this session at the bottom there you’ll see there’s a link for session resources and that will get you all the code here. But the main thing I got to show you here is we got one easy statement that ran very, very quick. We got this cool windows [spool] thing here and that’s because we’re going ahead and we’re using windows function here. Basically, I want to sum up my cost as I’m going over ordering by my transaction ID. And this gets me the same thing as those there, except a lot faster. In fact, just for fun as we’re talking here, I’ll go ahead and fire up this guy here so we can see it choke. But you’ll see this may get mega seconds over here, something that ran under 300,000 seconds. And while we’re doing that Erik, I will turn it over to you, might as well while we’re sitting here waiting to see if this ever finishes would be a good time to go through some questions.
Erik Darling: There weren’t many for your session. I think you did a good job explaining things. People were enwrapped by it. They weren’t – no questions on that. There was one from GoToWebinar but that was it.
John Sterrett: Okay. Oh, looks like it will finish today. 30 seconds, so maybe show you guys here the execution plan. Well, once again, like I mentioned here, you can go to procureSQL/GroupByconf, which is the same hashtag everyone’s using for the event, or I tell you what, if – I think I got this here, if we go to sessions for today, this is an ad hoc demo, folks. This was not planned at all. Something we’re just kind of winging here. But if you go over here and let me see, today’s sessions, you go click here, and I really want you guys to benefit and learn and take this to your advantage because this is all stuff that you can start using tomorrow. I just want to point you right here. So if you click here, this will get you a link to where you can go through and you can download all the resources. There’s some other videos and stuff out there for you as well. But hopefully this is something that was helpful to you guys that you all were able to use for your advantage and when you actually do get time, because I know everyone has 10 billion things they have to do today and tomorrow, when you go get to the demos, if you have any questions, feel free to reach out at me over Twitter, commenting on the blog here or any way. Like I said, everything is right here waiting for you guys to consume and use here. So hope that’s helpful and hope you guys learn, hope you guys had a great conference here and look forward to speaking on another one in the future.
Erik Darling: Cool, thanks a lot John for hanging out with us. Thanks a lot for the great sessions.