SQL Server Execution Plan Operators

welcome to your guided tour of the most frequently seen and most important operators in an execution plan before we get started I want to get a couple of things out of the way with regards to terminology in the way that I describe things in this video first I'm going to use the word reads and it may not be the most accurate term but I'm using it loosely speaking because when an operator like a clustered index scan or index TKE goes to retrieve rows from storage pulls them into memory when that happens its accessing and retrieving and examining those rows it does have to look at them at some point so don't get too hung up on the fact that I'm using the word reads which gets thrown around a lot in other sequel server avenues the second thing is when I talk about what an index seek or a scan accesses I'm ignoring the fact that there are filtered indexes I'm talking about a typical non-clustered index that covers every row in a table not just some of the rows the way a filtered index would so just keep that in the back of your mind as well with that said let's move on to the first of our operators the humble table scan a table scan is an operator that you only see running against unordered structures or heaps things like tables without a clustered index temp tables or table variables when is this a good thing not very often there are times where it's okay to have a table scan when you see it in the execution plan you say that's kind of what I expected I'm good with that times like these include if you have a smaller table be it a physical table temp table table variable whatever if you're talking about hundreds of rows that's fine you can deal with that being a table scan in most cases there are other times where table scans aren't so bad either say you have an ETL process that flushes and fills a table with no clustered index on it it could be that that process will run a lot faster if you do have a clustered index because things don't need to be ordered as they come in and out it's important to point out in that case that you don't want to be deleting from that table you want to just truncate it outright because a delete will have far more logging activity involved in a truncate will plus heaps don't always behave well when it comes to releasing the disk space involved with the rows that were deleted in any case the scenarios in which using a table scan will be satisfactory are fairly limited which brings me to the down side of things and there are a lot of downsides to table scans obviously having to search an entire table for whatever sequel server is looking for is not an optimal strategy sometimes you can get away with it if it's a small table there's very little performance penalty between doing a table scan and a clustered index scan but as the table gets bigger this is going to get worse another problem that you can run into is that table scans because they're coming from an unordered source may result in the ordering of that source there are times where predicate Slyke exists will force data to be sorted in order for it to be compared if you need that data to be compared it may need to be sorted if it needs to be sorted then you probably would have been better off with a clustered index scan now let's take a look at what a table scan looks like in an actual execution plan what we're looking at here is a good table scan one that when we see it in an execution plan like this we're happy with it let's say that we've got a table called Obi Wan's and what we're going to do is create a table that just has an ID and a display name and then we're going to make a table of all the people who have obi-wan in their name now we don't know if they use spaces or dashes or nothing in between the Obi and the one part so we're just going to say where did this play name is like OB and also happens to be like one and let stackoverflow figure out the rest in our query down below we'll select the ID in the display name and then we'll also find out what is the total number of answers all of their posts have received so we'll select from this temp table and join it posts and go from there when we look at the execution plan it's really not that bad the table scan itself is pretty cheap it's got almost no cost to it because this is a very short list if we look at the results yeah pretty short there's only 8 people that fit this table scan is inexpensive the clustered index scan makes up most of the cost where it goes through the post and then gets matched up there's only one sort operator and the rest of the plan is relatively cheap now this could be improved upon with a non-clustered index that missing index is actually on the post table sequel server wants to get rid of that clustered index scan it's not so much concerned about our table scan this is a pattern where if you need to reuse a small set of rows the way we're doing here then a temp table is a convenient way of doing it and if it's small you can get away with not having it sorted you don't have to put a clustered index on your temp table or anything like that just roll with it the way it is so in this case we're doing exactly that and it turned out fine if we wanted to make this query faster we're not going to accomplish that by swapping out the table scan we're going to accomplish it by getting rid of the clustered index scan on the post table instead here is a bad table scan now I'm not showing you the plan right away because I actually got a worse plan when I ran this on a different server so I'm going to show you the plan that I got from over there and then I'll show you what we can do to make this even slightly better so with this query what I'm doing is selecting the post ID and title and a count of the number of comments and I'm going to sort this record set by the number of comments in descending order so the most popularly commented posts will be at the top I've removed all of the indexes from dbo posts so that this will be a table scan on hosts we're joining it to the comments table by post ID when we look at the plan we can see that there's a lot of problems that come from using an unsorted data source we're doing a table scan over here which is pretty modest it's really only six percent of the entire cost but the problem is the havoc that it causes downstream we've got a sort here on that table scan and then we've got another sort on the clustered index scan so they can be put together in the merge-join then they're sorted again and then they're sorted one more time and you may notice that all of these sort operators have the little yellow triangle warning you that something bad is happening in each case the operator has spilled over into temp dB meaning there wasn't enough room in memory so it had to write this stuff to disk if we add up the cost of these sorts 22-21 thirteen and fourteen that's seventy percent of the cost of this query plan just spent on sorting and resore today fear we're doing a group buying an order by so yeah some of that is going to come from the fact that we're doing those two operations but they're still sorting that happens just because we had unordered data enter the mix one more thing we'll look at here is the cost of this Big Daddy forty two hundred and sixty that's a really expensive query so what we're going to do is to see if we can improve upon this we're going to make just one simple change we're going to take this table scan and make it a clustered index scan all we're going to do is make sure that the data that comes from our post table is now sorted in storage it doesn't even matter if it's the right order as long as it's an order if we go back to our original query here and we reveal the plan this is what it looks like when we add a clustered index now we've still got a clustered index scan over here like we had before in the comments but now it makes up a much larger share 48% of the entire query cost our clustered index scan on the post table replaces the table scan and its cost also went up but that's because these are relative to the rest of the plan and look at that there's only one sort operator now and that sort takes place after all of our data are put together so this is if we hover over putting things in descending order this is the order by count so the only sort that we're encountering here has to do with the order by that we put in there's no sort taking place on either of the two clustered index scans anymore so you see how introducing ordered data from a clustered index can make a big difference in the query plan versus having unordered data one last piece of proof if we look at the Select statement now instead of the 4260 subtree cost we're down to 1470 make no mistake that still stinks it's just that it's nowhere near as bad as it was before so this is a particularly atrocious table scan not just for the fact that it has some expense incurred but also everything that happens downstream of the table scan is bad too because the table scan was there a clustered index scan is just like a table scan only the table scan is unordered the clustered index scan is ordered of course the order here being the order that the clustered index goes in the columns that are defined in that index define how its ordered in storage a clustered index scan isn't always the best solution in fact it usually isn't but it is a useful operator in certain situations situations where you say clustered index scan I'm fine with that for example when your table is smaller just like with the table scan you're probably going to be ok with a clustered index scan something that covers hundreds or maybe even thousands of rows it's probably going to be alright also when you have a materialized index view materialized simply means that you defined a clustered index for that view or if you've got a clustered index on a temp table or table variable you can actually have a clustered index on a view attempt table or a table variable although the rules for defining those kind of very other times where it's good to have a clustered index is when there's a low relative cost if you see that in your query plan you've got a clustered index scan that accounts for 4% of the total cost it's probably not a big deal you can shrug and move on to the more expensive operators remember in a previous video how we talked about scans seeks and solubility this is one of those times where if you have a nonce R double statement you can expect to be sent back to a scan as opposed to getting any kind of a seek out of it this may be a clustered index scan or a non-clustered index scan just depends on how your indexes are built but it's a kind of thing where if you're getting the clustered index scan it's really up to you whether to determine if that's going to be worth trying to get a regular non-clustered index scan versus a clustered index scan one more instance where it's acceptable or ok to have a clustered index scan is if you're seeing it against lesser used columns say we don't have a lot of indexes on this table and it's not worth indexing just to make this one low-cost clustered index scan into any kind of an index scan or index seek if that's the case sometimes you just roll with it and say it's good enough again it depends on the relative cost of that particular operator which brings me to when is it a bad thing to see a clustered index scan there are three occasions where maybe we should look to improve upon the situation if you see a clustered index scan against a table that has a whole lot of rows millions of rows then it could be that you could improve upon the clustered index scan even if it's not an extremely high cost operator there's still room to narrow things down a little bit potentially another time where you don't want to see a clustered index scan is if you have room to index say you've only got one or two non-clustered indexes there's no reason you couldn't add more to cover this and it's part of an execution plan that's tied to a very problematic or expensive query if that's the case then yeah seek to get good pun there seek to get rid of the clustered index scanner you place it at least with some sort of index seek and then the final time where you would want to pay attention to this is again if it's a high relative cost if you've got a clustered index scan that's brutal in terms of the percentage of the cost of the plan 50% or more then you may want to look at reducing that by changing things around adding an index that will get rid of the clustered index scan now let's take a look at a clustered index scan in a real-life execution plan all right so let's take a look at a couple of clustered index scans the first one we'll look at is a good clustered index scan so we've got select ID and type from dbo post types where the type is like and then somewhere in that string the word answer or at least the six characters that make up the word answer why is this a good clustered index scan for the simple fact that it's cheap this is a teeny-tiny table and you look and it only had to read two rows and only returned one actual row and the costs are almost zero so this is a clustered index scan we really don't care about and obviously we can't do much to improve upon it because it's just so cheap to begin with this is the kind of query that if I see it in someone's environment and it's running thousands of times per minute then I'll ask okay can this be cached or you know made part of the app somehow as opposed to asking sequel server this question repeatedly but if it's not avoidable or if it's not part of an app if it's just something that I see running from time to time I'm not going to worry about this at all it's so cheap just don't split it here is a bad clustered index scan one that if I were to see it I would want to try to improve we've got select ID from dbo posts where title is like and then problem is somewhere in the title it can be beginning and middle doesn't matter but we're looking for the word problem in the title because this statement isn't sorrowful its the wild-card operator at the front of the word problem that makes this nots are doable we know that we're going to be stuck with a scan it's going to be a cluster two next scan or a non-clustered index scan but a scan just the same why do we want to avoid this particular clustered index scan it's all about the cost if we look at the cost of this plan the estimated i/o cost for the clustered index scan is 461 that's pretty steep the problem here is that because it's a clustered index scan on posts we're bringing all the other stuff that comes with the post table into the fold and if we look at what's in the post table we see that there's stuff like body which is n text and we've got title which is n ver care 250 and tags and very care 150 we've got some pretty wide fields in there that we really don't want to have to deal with if all we're looking for is the ID and we're just trying to look things up by the title so the way that we will go around this is to create an index that will satisfy this particular query and give us a way around that clustered index all right so our index is created let's go back and run this again what should happen is sequel servers should pick up on the fact that we've got a much better index to use and avoid the clustered index and there we go now this is still going to be expensive relative to the rest of the plan as far as the index scan goes because there's just not much else going on there but if we look at the cost now rather than having the i/o cost be 461 we've managed to shave a whole lot of i/o off of this by just leaving unrelated columns out of the equation so now our io cost is sixty nine point six that is way better than for 61 we were confronted with earlier so the short story here is that because the clustered index involves some very wide columns we want to cut those out of the equation and just have an index that is relevant to what we're looking for a clustered index seek is kind of like an index scan only in the sense that they go in two different directions what I mean by that is that the index scan contains a subset of columns not every column a clustered index seek contains all the columns but only a subset of rows not every row I don't know what I was doing with my hands just then but if you just listen to what I'm saying that's probably going to be good enough in any case a clustered index seek will only target specific rows as it looks for whatever it's trying to match up with this is a good thing because now we're not hitting every row in the table this is good to see for things like small tables lookup or reference tables for example like post types if the table is narrow then it means that we're getting the benefit of seeking out only certain rows but we're also not bringing back a whole bunch of stuff that comes with each row sometimes it's good to have a clustered index seek for example when you have limited indexing opportunities either you've got a lot of indexes that you've created and you want to keep them all and the table is so big that you don't want to add another index to it or maybe you've got a vendor app where you're not even allowed to touch the indexes and you look at the execution plan and say I could try to fix this some other way other than through creating a new index but I'm just going to let this one go here goes again if the operator has a high relative cost to the rest of the plan then yeah you probably want to do something about it other times where a clustered index seek is not such a good thing is when you have a wide table because we're doing a clustered index seek even though we're able to target a subset of rows we still have to get every column and for a table like posts for example there are comments and that comment field is pretty wide if we don't have to pull that into memory why should we let's try to get a non-clustered index that doesn't involve the comments field to satisfy that instead now let's take a look at a real-life example of a clustered index seek in an execution plan here's an example of a good clustered index seek we're selecting some IDs from posts and we want to join the post types we're just kind of bringing it along for the ride here ignore the fact that it's sort of redundant in this case but we're searching where post type ID is 1 and creation date is 2013 September 6 if we look at the execution plan the clustered index seek is taking place on the post types table down here this is taking up almost no cost whatsoever compared to the rest of the plan and if we hover over it it's actually reading just one row so this is one of those situations where the table is small enough that we really don't care if it's having to do a clustered index seek we're not going to try to make that better with a straight up index seek the table is so small that it really won't matter now let's take a look at a bad clustered index seek here's a clustered index seek that I don't personally like but it's not as though we absolutely have to replace it again we're hitting the post table in the post table is kind of wide the good news is here we're not selecting every single row we're doing a seek because we've got the clustering key in our where clause if we look at what this is doing it's bringing back half a million rows which is not the most high cardinality seek we can imagine but just the same it's selecting only certain rows and it's seeking to find them when it looks for them in storage looking at the estimated IO cost it's really not that bad it's 14 per half million rows ok we can probably live with that okay there are a few reasons why we might want to improve on this seek even though it doesn't appear to be much of a backbreaker performance-wise the first reason is we've only got a couple of columns involved here ID and title remember that the post table is pretty wide and it's got a lot of other stuff that comes along for the ride whether you want it to or not whenever you hit the clustered index the other reason why this may be something we want to improve upon despite its seemingly low cost is this might be the kind of query that runs a lot it could be a very heavy use query and although the cost 14 doesn't seem all that large it may be the kind of thing that racks up a lot of resource consumption volume wise so in that sense we could try to make this better by turning this clustered index seek into a non-clustered index seek let's do that we'll create an index just on posts title and the ID all right our index is done so what we'll do is we'll run this again and recompile and it should do an index seek on the index we just created all right there's our half a million rows and sure enough it did an index seek I remember the original cost was 14 let's see what it is now okay so it's down to about two and a half this is significant for a couple of reasons one we just made a query about seven times faster but the other thing is if you have a cost threshold for parallelism that ends up being the dividing line between the clustered index seek and the index seek you may be able to sort of reclaim some CPU by not having this query go parallel so in that sense we've taken a bad clustered index seek and turn it into a good index seek index scans are a lot like clustered index scans they access every row in the index however they don't access every column because an index a non-clustered index that is is made up of a subset of the columns in the table so by definition you can expect that an index scan will be smaller and use up less resources to go through than a clustered index scan would just to clarify at this point an index scan and an index e Quenya see them in an execution plan refer to the non-clustered indexes not to the clustered indexes rather than specifically say non-clustered index scan the execution plan will just shorten it the index scan will traverse through the index in order of whatever that indexes order has been defined to be so if it's ordered by post type ID last editor user ID whatever that's the order in which the scan will take place there are good and bad times to see an index scan one of the good times that you want to see it is when you have a non soluble statement something where it's unable to seek out what it's looking for because it's just not part of the ordered structure kind of like the example where you looked at like obi-wan where there could be characters in front and characters behind an index scan is about the best we can hope for there being that an index is a subset of all the columns that make up the clustered index we can kind of deduce that it'd be better to have a smaller footprint and go through the index than it would be clustered index there are times where an index scan is a bad thing one of the times that we should be unhappy to see an index scan is if the indexing question has a really wide column in it say for example in N Mayer care 4,000 or and ver care max or something really large XML image whatever if we can avoid hitting that column then maybe it's better off that we create a different index even if it's a different index that we scan through because what we want to do is avoid taking up a bunch of resources by pulling in large columns in domen and pulling large columns from disk remember the name of the game here is to make your footprint as small as possible so in that instance we may be better off creating another index even if we're stuck getting an index scan from that new index it may just be a better option overall another time where it's not really going to be satisfactory to have an index scan is when that index scan has a high relative cost to the rest of the execution plan we look at execution plans see that the index scan is 50 70 90 % of the plan that tells us that there's room for improvement there just like with our other query tuning efforts when we see an operator that's taking up most of the execution plans cost we want to find out why and see what we can do about it so now let's take a look at an index scan in a real-life execution plan here's a situation where we have what we would consider to be a good index scan one that we would see in an execution plan and say we're happy with that we've created an index to make sure that this actually happens on an index to get the scan obviously and we're selecting the ID and display name from users where the left three characters of the display name are obyeye now it seems kind of dumb that sequel server can't make this ace arguable statement but if you think about it it has to bring in the display name and then do some thinking about it and that breaks the Sarge ability in a case we're stuck with a scan because of the where clause we've chosen the reason why this doesn't really bother me too much is that this index scan is fairly lightweight it's having to read through all 2.3 million rows that's unavoidable again with our statement here but at least the cost is fairly low it's down to like 7 and even though it takes up a fair amount of the relative cost to the rest of the plan it's still hitting an index that only has the ID and display name I don't have any ones about me being brought along for the ride here so it's fairly straightforward here this is an acceptable in Dex can because there's not much more we can do to make this more lightweight let's take a look at where that's not the case in a bad index scan this is an example of a bad index scan I created an index called IX big display name because it's the display name but now we've brought the about me column along for the ride the about me column let's find it here is n ver care max uh-oh that's going to take up a lot more space especially if people are really vigilant about filling out detailed biographies and their user profile let's hope that that's not too damaging in this case so we run this again we look at the index scan the results and for one thing it's now big enough to go parallel that's not a good sign and the estimated IO cost is thirteen point eight five that's significantly larger than when we did our good scan and we got seven point one so basically by choosing the wrong index we've doubled the IO cost associated with this query it's important to be able to recognize this because sequel server won't always make the best choice when it comes to what index to hit or it could be an indicator that an index you thought was there has been disabled or deleted entirely so it's important that when you troubleshoot execution plans and you look for index scans that you make sure that they're hitting the best index they can and the last of the five major data retrieval row set building operators is the index seek under most circumstances this is the operator that you want to see because it will be the most efficient it's the most efficient usually because it accesses just a subset of columns that is the columns in the index not the clustered index and a subset of rows meaning if it's doing a seek it's not touching every row so it has the smallest footprint of the five that we talked about so of the table scan clustered index scan clustered index seek index scan and index seek index seek is usually going to be your best choice one thing that's kind of curious about the index seek is that it's usually good even if it has a high relative cost because it could be that at that point there are no other operators in your execution plan that are going to be expensive by nature and that the data retrieval part is just going to be the most expensive part no matter what in that case it's actually okay to have a high cost index seek there are however times where it's not so good to have an index seek one such time is when you have the wrong index being sought on or seeked on or whatever the word is for that if you are looking at an index that involves again like a huge comments field or something and you want to avoid that column being part of the equation then you may be better off creating a new index or having to hint to an existing index that sequel server just isn't paying attention to because again you want your footprint to be as small as possible here and it could be that the index seek that's taking place is happening on an index that is really not a good choice overall there's one other occasion to we're having an index ich is not a good thing in fact it may even be beaten by a clustered index scan that's right it may be beaten by a clustered index can let's say we've got a table so we've got columns and we've got rows of the four operations we talked about there's the clustered index non-clustered index scan versus seek we'll go through all four now the clustered index scan touches every column and every row a clustered index seek touches certain rows but every column an index scan touches certain columns but every row and an index seek is sort of the best of both worlds between the clustered index seek and the index scan in that it only touches a subset of columns and a subset of rows so how in the world can an execution plan be worse off with an index seek which only touches pieces here and there versus a clustered index can seems kind of crazy doesn't it well like everything where there's a rule there's bound to be an exception so let's check out an execution plan now that actually shows you may be better off with a clustered index scan than you would an index seek here we have a bad index seek it's not so much that the index seek is to blame for it being a big problem query it's the kind of stuff that gets partnered with the index seek in this case in order for us to measure the impact of having a bad index seek and be able to say yeah I clustered index scan will do better than an index seek we have to use a different metric than what we've been using we've been looking at estimated i/o cost to this point now we're going to switch over to CPU time in order to do that we'll set statistics time on and it will tell us the CPU time and elapsed time now what we're going to do is run this query where we select post type ID from posts where last editor user ID is some parameterize value this last editor user ID of 44 only returns five rows and if we look at the CPU time involved it's practically instant here's our execution plan and in this case it's a good one we want an index seek and a key lookup and nested loops because these are all small tiny operators and we're only talking about five rows we want to see really skinny arrows like these guys here whenever we see index seeks and key lookups paired together with nested loops if we run this again against zero or with a parameter value of zero the zero kind of represents nobody in the stack overflow there's no record in the users table that maps to that zero so if we run this exact same statement only we give that parameter value zero we're going to get ten point seven million rows back but if we get that many rows back do we still want the index seek and the key lookup let's find out okay so our query is done let's take a look at the execution plan okay this is not good we've got the index seek and cute lookup but we've got some really fat arrows going from those two operators into the nested loops if we look at the key lookup we'll see that the number of executions was ten point seven million now this doesn't mean that sequel server went to storage to get the clustered index and pulled it into memory ten point seven million times what it does mean is that sequel server pulled the clustered index into memory and then referenced it ten point seven million separate times so it's not bad bad but it's still bad this is only half the story however because I teased earlier that a clustered index can can beat an index seek we've seen now that the index seek is not good let's go get an actual number for that all right exactly thirty-eight seconds so don't pay attention to the elapsed time here this 85 seconds a lot of that was just time spent writing out that ten point seven million rows into management studio that's sort of out of our hands so let's just focus in on the CPU time something that we can much more directly control let's find out how we can beat this now using a clustered index scan first thing we'll do is free the procedure cache to make sure that that plan is blown out now what we'll do is run this query again and this time when it formulates a plan hopefully what will happen is it will use a clustered index scan or some other method that is more efficient than the plan it had been or reusing so let's run that and find out if we can get lucky all right so we've run this query let's see if we got a better plan oh we didn't we got the same plan as before and it still took a long time all right so that won't be enough it's clear that even though there's a clustered index can the sequel server could choose for this it's not doing that it's making a bad choice so what we're going to do is because this is a development environment I'm not going to disable an index I'm not going to hint that it used a different index I'm just going to drop that index that it's using make sure that's the same one ix last edit or user ID yep there it is right there so I'll drop that and now the time to be is 38 seconds flat here goes all right so in real time it finished in 44 seconds let's take a look at our execution plan all right so there is a clustered index scan here and make no mistake it's expensive from an i/o perspective and if we were to create this recommended index that would help quite a bit 99.99% in fact according to sequel servers guess but what really matters did our clustered index can beat the index seek it sure did twelve seconds so this ran roughly three times faster than the index seek and again it's not so much the index seeks fault it's the fact that it got paired with a key look up and then nested loop join let's see if we can now go back and make this a happy query by taking the missing index and implementing it so I'm just going to call this the last editor user ID and then include post type ID I'll create this index and when it's done we'll go back and run our query again and see how much faster we can get then the twelve seconds we got from the clustered index scan all right our index is done let's hop back over and we'll free the procedure cache again and now we'll run this one more time the mark to beat is oh it's gone already the mark to beat is twelve seconds okay that took 36 seconds of real time but it only took four seconds of CPU time there are two lessons here the first is that not all index eeks are created equal it could be that you will get the wrong index seek in this case we had one that worked really well and then one that didn't do so hot so when you see an index seek in your execution plan you do have to cast a bit of a critical eye on it to see if it's really put in the best situation where the index seek failed us in this case was where it was paired with small timing operators like the key look up and the nested loops operators that totally buckle under the weight of having to do ten point seven million operations the second takeaway here is that sequel server will do something called parameter sniffing where it will reuse a plan and reuse the operators that came with that original compilation that compilation is based on certain assumptions about the number of rows you're going to get back in this case it made an assumption that small-time operators were appropriate for the number of rows that we are going to get back and that was true at the outset we only got five rows back but when that plan was reused for ten point seven million rows those small-time operators buckled under the weight this concludes part one of our tour through all the operators that you'll see in an execution plan coming up we'll look at operators like joins and the key look up stay tuned we'll return after these messages

Channel: Brent Ozar Unlimited

Views: 58,899

Rating: 4.9558825 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: soCqOINuSTA

Length: 42min 7sec (2527 seconds)

Published: Fri Jul 29 2016