- Host: Steve Stedman
- Topic: Execution Plan Analysis
- Recording Date: May 19, 2026
- Listen on Spotify
Execution Plan Analysis Stedman SQL Podcast Sn 3 Ep 10
Steve Stedman discusses analyzing SQL Server execution plans and features in Database Health Monitor that aid in this task. He explains that execution plans are SQL Server’s roadmap for query execution, detailing how SQL Server will execute a query, including index usage and data sorting. Stedman differentiates between estimated and actual execution plans, noting discrepancies can indicate outdated statistics. He highlights the importance of execution plans in identifying slow queries, bad joins, and expensive operations. Database Health Monitor provides graphical representations of execution plans, making it easier to identify performance issues and optimize queries.
Podcast Transcript:
Steve Stedman 00:16
Hello, podcast listeners, and welcome back to the Stedman SQL Server Podcast, I’m Steve Stedman from Stedman Solutions. Today we’re going to talk about analyzing execution plans and some features in Database Health Monitor that make that an easier task. Yeah, so no guests today, I’m just flying solo, but that’s okay. We’ve got a good episode lined up for you. Have you ever written a query that looked fine, but suddenly it took 30 seconds or longer instead of 30 milliseconds to run? Well, if you’ve written queries long enough, I’m sure you’ve seen that happen. So we’re going to take a look at how you can track that down and figure out what the problem is. SQL Server execution plans are one of the most important tools for understanding SQL Server performance. We’re going to talk about what execution plans are, why they matter, and how tools like Database Health Monitor can make them easier to analyze.
But first, would you like to be a guest on our podcast? Are you interested in being a guest on the Stedman SQL Podcast, if you are just reach out to us and we can talk about what topics, things you want to cover or talk about anything relating to SQL Server, Database Health Monitor, or about our company. You can reach out to us at https Stedman solutions.com/guests or go to the podcast page and click on the guest link, fill out the form, and find out if you’re going to be a guest on one of our future episodes.
All right, so first off, what exactly is an execution plan? Well, an execution plan is SQL Server’s roadmap for how it’s going to execute a specific query. What SQL Server does when a query is getting analyzed and compiled is that it needs to figure out how it’s going to do that work. It looks at what indexes are going to be used, how those tables are going to be joined and connected, whether data is sorted or scanned or filtered, and it puts all that together into what’s called the plan, which is exactly what SQL Server thinks it’s going to do in order to go run that query.
Now, when I say it thinks that that’s what it’s going to do, well, there’s an idea or a concept of an estimated execution plan or an actual execution plan. The estimated execution plan is simply what SQL Server plans it’s going to do, and what it thinks it’s going to happen. It looks at things like statistics and table structure and indexes and all that kind of stuff to figure out what is its best way to go and do what it’s going to do. Well, then when the query gets run, you end up with an actual execution plan, which is a record of what really happened during that execution. Well, why would they be different? Might be that mistakes were made by the query optimizer because of bad information that it might have been given. For instance, if your statistics are out of date and the query optimizer thinks there’s 12 rows in a table, but there’s really 12 billion rows in that table. Well, you might get the wrong plan for working on a table like that. So, when you see things that are dramatically different between the estimated and the actual execution plan, that’s usually indication that something is out of sync on statistics. Okay, so think of your execution plan, as like GPS and map navigation for your query. SQL Server evaluates all the different routes to go and do what it can do to get that data back, and then it tries to choose the fastest one. So, what are executions plans used for? How can we use them? Well, every query on SQL Server ends up with an execution plan. They’re useful in identifying slow queries for finding bad things that are happening in those slow queries, like table scans and missing indexes. Hopefully, everyone knows that an index is a good way to get faster access to your data, and that maybe scanning an entire table or going through every single row of a table might be slower than just seeking and finding the rows that are needed through an index.
Steve Stedman 04:23
It’s also useful for finding bad joins or expensive operations, and we’ll look at some examples of that in a little bit, and but also it’s good for understanding why CPU or IO is high. I mean, imagine if you’ve got an implicit conversion, like we looked at on a previous podcast, and we’ll take a look at some stuff related to that today too, but those are things that there’s a lot more work being done that usually leads to more CPU and more IO. There’s a lot of other causes too that we can find from plans, but you can also use it for troubleshooting parameter sniffing and. Other cardinality estimating issues, so a couple of things that we commonly run into when we’re looking at execution plans are things like index seek or table scan. What an index seek is, is it uses the index to quickly find the rows that are needed for your results. Oftentimes it might be a really low number of page reads that has to do that, it might read, and a page is an 8k chunk of data, it might read two or three or 15 of those to get what you need, whereas a table scan that implies that it’s going to look at every single row in the entire table, and that might be hundreds or 1000s or more of page reads in order to find your query, find your query results, and with that, the less page reads there are, the faster your query is going to run. There’s other things that happen that we’re going to see in the plans, things like sorting. Sorting is one of those things that’s really one of the slower operators that we see on SQL Server. So, anytime you can avoid, avoid a sort, you can be able to make things run faster. Okay, so the thing is, execution plans can help us stop guessing and start seeing exactly where SQL Server is spending time and resources. I mean, that’s one of those things. Have you ever looked at a query and just sort of stared at it and thought, “Hmm, that doesn’t look right, that’s going to take a while. Well, you’re purely speculating until you look at the plan and figure out what is exactly happening when that query is running. Okay, so why Database Health Monitor helps.
Let’s take a look at this. So we’ve got a number of areas in Database Health Monitor that we can view a different graphical representation of the execution plans from what you see in standard SQL Server. With that, we allow for faster identification of extensive operators, and it’s really useful to be able to go in and pinpoint exactly where the problem is, instead of digging through raw XML or jumping between tools, or even just all the scrolling that you get when you’re looking at a big execution plan. Database Health Monitor can help you spot those important details really fast. So, let’s take a look here. We’re going to break down some execution plans step by step. We’re going to look at some common performance issues, and we’re going to show how Database Health Monitor can analyze and tune queries faster than you would do alone with Management Studio, but first, a word from our sponsor:
Is your SQL Server slowing down your business? Slow queries, bottlenecks, and unexpected issues eating up your time. Introducing Database Health Monitor, the powerful tool built by our team at Stedman Solutions. Get real-time insights into performance with over 100 built in reports, index analysis, weight stats tracking, and proactive alerts. Quickly diagnose and find problems before they impact your operations with an easy to use interface for DBAs and developers. Monitor unlimited servers tune queries and keep your databases running healthy and fast. Download a free trial today at DtabaseHealth.com and take control of your SQL Server performance. All right, so just a quick overview of what an execution plan is.
If you saw our implicit conversions episode that we did a few, I guess, a couple months back now. You might recognize some of these queries and some of the plans. So this is an example. If we highlight this query, we turn on actual execution plans up here, which we use with the control M key or this button on the toolbar, and then we can run the query.
Steve Stedman 08:39
We see our results, but we get another tab here that shows the execution plans, and this first one took 100% of the overall cost, and the second one took 0% So that’s saying the second one is a heck of a lot faster than the first one, and you can see things about here’s an index scan, here’s a key lookup, nested loops, all that kind of stuff that goes into providing the results from that query. Now we’re not going to go into a lot of detail on this, but you’ve probably seen, if you’ve done anything with performance tuning, you’ve been able to go in and see execution plans like this. Now one step further you can go is you can from an execution plan, you can look at the XML, and there’s a whole lot more information here behind what goes into that, what goes into that plan that’s not being shown, but unless you’re really into reading XML, so unless you’re really into reading XML and digging through all of this, this is probably not the most optimal way to go about analyzing your plan, so in Database Health Monitor, we collect execution plans in a lot of different locations, a lot of different places. One of them we have is Active Query CPU History. This is one of the many tables that we have in the DB Health History Database, where plans are kept. I have a query here, if we run this, it’s going to look and see. Okay, what are the high CPU queries that have been run in the last 10 hours? You could go change that, but we have one column. We have here is the show plan XML, or the query plan XML. You can click on that and get access directly to that by poking around in the Database Health Monitor, and this shows you the plan the same as it would have looked had you run it through SQL Server Management Studio with an execution plan to turn on. Through here you can scroll through and say, okay, well, here’s 96% that’s probably most of it. Then we go down to this big chunk and squint our eyes and scroll around until we find, oh, look at that, 43% there, 8% that’s where all the work’s being done, and those, those bigger, bigger items there. Looking at this, this is what I’ve done for almost 35 years, and going to be not that long, because in the beginning we didn’t actually have the visual execution plans like this, they were text based, but this is the way we’ve analyzed it for a long time, and I finally got a little bit, I don’t know, fed up with trying to have to explain some of these things, and go through and say, well, here’s the problem, and here’s this or that.
So, what we did is we built into Database Health Monitor some different ways to look at this. So, let’s take a look at that now, and what we’ve got a couple things that I’m, we’re going to take a look at here. One, we have just plain old Database Health Monitor application. There’s a lot to it there, but we also have our query loading ramp, which is a tool that was released in 2026 that allows you to run specific loads against your SQL server. I’m going to run this load right now, it’s got as we look at this, there’s about six or eight queries up here that are all specified. We can look at them that are doing a number of different things. Some of them are designed with some really poor SQL in order to be able to show some bad execution plans. So, what we’re going to do is, we’re going to run this load test, 200 sessions, we’re going to run ramp up time of one minute, a run time, let’s go for two minutes, a total of three minutes here, and we’re going to run that load, so we’re just going to let that run in the background, sort of like when you watch the cooking show, and they just put the food in the oven. We’re going to come back later and see what it looks like when it’s done. So, while that’s running, what we’re going to do is go look at a couple other areas.
Steve Stedman 12:19
First off, in Database Health Monitor, there’s a number of different areas that we can look at to find what’s going on with different queries. If we go to, like, blocking by hour, we have here a chart showing, oh yeah, here’s the busy times of the day, Monday through Thursdays, it looks like, and there’s a lot of blocking going on. Well, what we can do is drill down on one of those specific times. Let’s look at this one here: 83% on Sunday. That’s an odd time for it, and we can see here’s the query that was running. It was stuck on a wait for delay, and if we look at the plan analysis, here’s our Database Health Monitor interpretation of the execution plan. What we’ve got here, it’s taken the statement and broken it down. Instead of having to scroll through all the different parts of the plan in Management Studio, we can quickly see that this one here select count from that, that’s where 100% of the work’s being done. If you want to jump right to that, we can click that query, we jump right to select count star from, yep, there’s it’s doing a table scan in order to get that, and that’s something where there are a number of things that we could do to probably optimize that, like maybe have proper index on it. It says table scan. A full scan reads every row in the index or table. For large tables, it’s expensive. Consider a selective index if nearly all the rows are returned. Scan may be intentional. So there’s a lot of description in here, and it gives you a rating too, overall, like this query, or it says the plan for this query is given a score of B on an A through F grading scale.
Now, with that, we also have, if you take a look at this, and there’s something that you don’t know what it means, like stream aggregate, oh, what does that really mean? Well, at the bottom we have this operators and concept glossary, where we can go down and find stream aggregate. Well, that groups and aggregates on presorted rows, some count group by very efficient processes rows one at a time without a hash table. Okay, the stream aggregate in this case is probably what’s being used for the count, the count star, and just a description of what all this other stuff is in the query. Let’s go look at some other queries, and we’ll come back to that in just a moment and see some other information there. So, if we look, go back to the blocking by hour, and let’s look at something that’s blocking in the last hour. There’s a different one, and this is a server I’ve got set up with a specific blocking test. Yeah, this is the same query we’re seeing here. Okay, so let’s go and look at instead of blocking, let’s go and look at our long running queries. So here we have long running queries, we can go to pick one of these hours that has a lot of long running queries in it, or maybe even one with less. Let’s go look at this. Just the last hour, what have we seen? There’s 8% There are, yeah, few dozen, a couple dozen queries here, but if we look at these, we can go click on the query and then go click on plan analysis here, and it brings up the plan analysis. Now that’s the same one we looked at. Let’s go over here and look at the queries and see if we can find. Okay, because this is the test server, we’re seeing a whole lot of them that are all the same thing. Let’s go look at a different point in time and see if we can find something different. Okay, here’s a different double click on the query plan analysis actual execution plan. This one is given us a grade of F on a scale of A through F. It’s doing one thing, it’s this one query select from login history, cross apply this, cross supply that, where this, and this is one that I specifically wrote to use some of the things that we commonly see when we’re doing performance tuning that people do that causes problems. So, let’s look at this one query. What is going on with it that’s taking the time?
Steve Stedman 16:38
Well, there’s a stream aggregate, there’s a clustered index scan. Stream aggregate has 2.6 million rows that it’s analyzing. The index spool has 13 million rows. Sort has 14 million rows. So, there’s a lot of rows being processed here, but what are the things that are causing problems? And we look here, and we can see some of these that are highlighted in pink versus kind of the I don’t know, yellowish color here. So things that you need to fix, these are the things that you should fix first, if you can. Nested loops, high inner side execution count. Well, why are these running so many times when we look at things like how many times things are being executed well. Cross apply, that’s infamous for taking the results from one query set and passing it through to another query and running that sub query n times, where n is the number of rows in the original query. Okay. Implicit type conversions. Okay. Var chart 10 on login date. Well, let’s look at that. There’s right here this convert var chart 10 on login date. This is something that we actually saw in a real performance to an engagement where they were taking an actual date variable, converting it to a var chart 10 formatted in a specific syntax to be able to compare month, month slash day, and out to a specific number of very specific number of characters for the month for the date comparison. There really inefficient way to do it, and that’s why I put it in this query, was to show it in the plan. So this is really quickly showing what’s bad, so here’s the other side of the var chart. 10 convert, convert implicit. Now that’s one of those that we did a whole podcast episode talking about those, and that’s on transaction date, and where is that? That would be probably just this one that’s happening down here, and then another implicit convert functions applied to a column in the predicate. Okay, what is the predicate? The predicate is the where clause. So functions applied in the where clause leading wild cards and the like in the where clause, let’s see what those are and why those are bad. Well, here’s a function, so here’s one where clause, and in there the user email comparison is probably okay, but the char index on user in email, it’s trying to find the offset to where the at sign is in the email, and it’s trying to find where one of them is bigger than the other, for some reason. Well, in that case, the only way it can do this is to call that char index function in order to be able to scan through every single user email in that table to be able to do that analysis and to do the comparison where that is greater than the other one, it also has to scan through every single row in the other table in order to perform, perform those chart indexes. Functions in the where clause are one of those things we typically refer to as code spells, and we did a podcast, I think, earlier this year or last year, where we talked about functions in the where clause, and kind of how big of a problem that can be. Let’s see what else we’ve got down here. Leading wild cards with like predicates. Okay, what does that mean right here? Where user email is like percent 7% we’re trying to find any user who has a seven somewhere in their email address. Okay. Why, I don’t know. That’s the thing. When we look at queries, we don’t always see the why behind it. We just see what people are trying to do.
Steve Stedman 20:29
The reason that this is inefficient is because when there’s a wild card at the beginning of a like clause on the search that makes it so it has to do a full index or a full table scan, it makes it so that index seeks can’t be used, because the only way to determine if there’s a seven anywhere in it, it can’t use the index structure to get there quickly, it can use an index, but it has to scan every single row in the index to find that, or it might have to do a full table scan, depending on what indexes are available, so this is a really inefficient way of doing to do things, and when I see things like that, I always ask, try and ask people a question of, well, why were you doing this, and is there some way we could do it differently in order to be able to make that query run faster or more efficiently. Let’s see, we got a bunch of other stuff, blocking operators TempDB pressure high risk active spills detected. Okay, that means there is a whole bunch going on that’s dumping into TempDB. In this case, it’s almost two gigs, 1.8 gigs of that was granted, but it wanted just over four gigs, so if there’s things that can be done to make this query more efficient and filter stuff out earlier, it’s going to help prevent so much use of TempDB. TempDB is that one shared resource that is used across all databases on your SQL server, and having extra pressure on it is going to get worse and worse when you have lots of other databases on your server competing for that TempDB space, so on that we can see also there’s index scans, execution order walkthrough. Well, this is showing kind of the order of what’s happening. It’s doing an index scan on login history, it’s then computing a scalar, doing a hash aggregate parallelism, and all the way down, and it shows how many rows are being hit out all the way going through here. So to be able to look and see, well, how can we make this faster? Well, you can look at what are the places where there’s lots of rows being used and figure out, well, how can we filter those or reduce the overall load there.
So that was quite a dive into this one single query here on the execution plan, but let’s take a look. Remember earlier we started that load test. Let’s go back to those load test results and see what that looks like now that it’s finished. So this load test ran, it ran 200 sessions, it ran it for three minutes, it completed 1500 queries, there were some errors, there were some blocking. Okay, that’s all expected. This chart down at the bottom is showing the query latency, or how long it’s really taking each query to respond out of the seven queries that are being run there, and we can go look at the results, which we will cover in another session. But right now, what I want to look at is plans. We’re on execution plans, so let’s look at the peak plans. So, at the highest CPU load that this load test was running, these were the plans that were captured in memory that were actively running queries at that point in time. So, if we scroll down, we can see, okay, well, update test table one, it’s doing that. Update test table one. Oh yeah, there’s a lot of lot going on, there’s probably a lot of blocking going on that, because they’re all doing the same update, is there as well, but if we double click on one of these, we can look at the plan, and this plan has been given a score of B on an A through F scale, grading scale, it’s only doing one thing, well, it’s doing a table scan, so if there was some way that we could see, there’s aware if we could have the right index in order to be able to quickly return the rows that are needed for updating. Here, this one could be sped up quite a bit.
Steve Stedman 24:13
A big 39% of the plan is being used to find what rows need to be deleted, and then 59% of the plan is being used to actually delete them, so we can might be able to trim close to 40% of the overall cost of this query by adding an index that is the right place for that to go find based off of an id equaling a specific value. Okay, so let’s take a look at where else we can see these in Database Health Monitor. We also have another tool called the Performance Viewer, which I’ve had running in the background here. Oh, and just a hint, if you’re in Database Health Monitor and you want to run either the SQL Performance Monitor, which I just showed, or the query loading ramp, which I just showed. Or our plan viewer, you can access them here from the tools menu, but let’s go and take a look at the actual performance viewer. This has been running, tracking, monitoring for the last bit, since before I even started recording of this podcast episode, and what it’s doing is it’s capturing information here on blocking queries, long running queries, high CPU queries, those are kind of the areas that we can find plans on, and let’s see if we click on some of these, if we can actually see the plan view execution plan. We can see that this is, oh, this is one we already looked at before, one of the first ones we looked at that we found in a different area, but if we go and look at things like high CPU, well, what have we got here? Some of these, not all of them necessarily have the execution plan available here, but if they do, we can look at it. Here’s one view execution plan. This is one of those queries out of Database Health Monitor that’s purging and cleaning up some data as the query runs, and we can see that this is given a score of F, and trust me, I’ve been working to improve this one, so it used to be worse. I don’t know what worse than F is, but it used to be worse. So we’re looking here, 44.6% is being taken on this one thing, and this is where we’re collapsing or rolling up data over time, like beyond a certain number of days. So a lot of the work there is being done on an index scan. We’re seeing stuff like functions in the where clause, which we know are bad. We looked at that earlier. Index scan, sorting, blocking operators. Yeah, just kind of an overview of the whole thing of what’s going on. And keep in mind at the end of this report, we also have, like I showed earlier, this is a long one. There’s a lot of queries in here. Oh, here it’s finding there’s some statistics out of date as well, and maybe updating these statistics might help this query run faster. And then, of course, we have the glossary at the end, which tells us what all these things that are covered here are, and I’m just looking at where else we have this. Yeah, we’re working on getting this into more and more places in Database Health Monitor, so you can just click quickly click through, and hopefully one day, wherever you can view a query, you can go and view the plan as quick as that, so execution plans don’t have to be hard. Hopefully, what we’ve shown here with the execution plan viewer that we’ve built into Database Health Monitor will make it easier than the old way of doing it by either browsing the XML or clicking around and dragging the plan in Management Studio, yeah, we’ve, in the time since we added this, we’ve, I’ve already used it on several customer environments and been able to find very quickly where some performance issues were that needed attention, so yeah, I guess that that wraps it up. Hey listeners, if you’re loving these deep dives and mind-blowing insights on the Stedman SQL Podcast. Imagine getting even more exclusive episodes, behind the scenes bonus content, and premium interviews you won’t hear anywhere else.
Steve Stedman 28:13
Head over to our YouTube channel right now and hit that subscribe button, turn on notifications, so you never miss out on the content dropping every week, join 1000s already unlocking the full experience, and don’t get left behind. Subscribe on YouTube today. So, if this is, if you’ve been using Database Health Monitor, and you haven’t seen this, it might be that you just have an older version that you need to update. This was available in Database Health Monitor version three dot 1342 which came out in May of 2026 If you’re running an older version of that, just go in and hit the menu and the help menu and choose update to the latest version, and you can get the latest version of Database Health Monitor, which has these features included in it, this is one of those things that over the years we just continue to grow and grow and grow the functionality of Database Health Monitor, so that hopefully we can get to the point that it makes your life so much easier than not using it. So, yeah, so I guess at this point I would recommend you download and give Database Health Monitor try you can get to that at DatabaseHealth.com and just want to thank everyone for watching. Have a great day, and keep your queries running quickly. Bye. thanks for watching our video. I’m Steve, and I hope you’ve enjoyed this. Please click the thumbs up if you liked it, and if you want more information, more videos like this, click the subscribe button and hit the bell icon, so that you can get notified. Of future videos that we create.