Name: Introduction to SolarWinds Plan Explorer: SQL Query Tuning Done Right
Uploaded: 2026-03-10T16:46:07.371Z
Duration: 25 min 24 s
Description: Introduction to SolarWinds Plan Explorer: SQL Query Tuning Done Right

Transcript for "Introduction to SolarWinds Plan Explorer: SQL Query Tuning Done Right": Hi, everyone. Thank you for joining us for an introduction to PLAN Xplorer. Today, we're gonna take you through some of the capabilities and features of the product and how it can make your query tuning process so much easier and faster. My name is Kevin Klein, and welcome to the session. I've done a lot with databases and SQL over the many decades that I've been working, and so I hope to share some knowledge that I picked up over the years with you here today. Hey. I'm Richard Burke here. I'm senior citizens engineer with SolarWinds, responsible for the database portfolio within SolarWinds, and hopefully having a discussion with Kevin, around the planet explorer. Excellent. Right. So we're gonna hustle through some quick lessons learned, And we're taking a look at this from the standpoint of someone who is rather new to query tuning. But there's lots of features and capabilities in the product that are excellent, even for skilled query tuners and performance experts. So we have a lot of special cases, which we will not have enough time to show you all of them, but show you several, and enough to make sure that you feel very, very comfortable that this is a great product to add to your library of tools. So let's talk about red flags and execution plans. There are a number of different kinds or problems that you might have with a SQL statement. The SQL query optimizer chooses to do different kinds of work on your query based on how much data is stored in the database. That's called cardinality on how much and how many indexes you have and what kind they are. So there's a number of different choices that the optimizer makes trying to give you the very lowest cost execution plan. And over many years, I've talked about different red flags that exist in queries. And so let's take a look at that in action. Yes, one question I have for you, Kevin, would be while reviewing execution plans, I find it quite challenging to identify where to focus my attention on. Right? So it's hard to tell where key lockups, table scans, other critical operations are happening. Right? So I was just curious if there was some guidance on how to better interpret these details and what tools or techniques could help me simplify the process. Yeah, exactly. The main tool that we use to do query tuning typically is Management Studio, and Management Studio has improved enormously over the years. And for an occasional bit of work on execution plan tuning, it's adequate. However, I'll show you some of the differences between Plan Explorer and Management Studio, and you can tell me if it helps you with those red flags. So if we look at a, let's say a query plan that's very large, and here we have returned a lot of data. So you can see we have a lot of different items to the select item list. We have some joins happening, our where clause and so forth. So when we execute a query like this, and by the way, this is an intentionally kind of bad exit query, you'll see here that we have quite a large execution plan, and it goes on for quite a while. And normally if you're in Management Studio, what that means is unless you get a warning at some point, which will pop up a little triangle here over the operator, a little warning sign triangle, that means you've got to look at each of the different steps. Oh, here's one that costs 15%. Right? So we have to look at each different step to see what's happening. And if we wanna get a idea of how big the execution plan, the only feature we have is right down here in the lower right corner, there's a tiny little plus sign. And if you click on that, you could see the full size of the execution plan. And then you can move around on that tiny little surface to see what's happening, right? So it's not easy to determine where your costs are. You just have to look through everything and stumble across it. Whereas if we look at the same sort of thing in Plan Explorer, Now, one thing you can also do by the way, is if you switch back and forth, you can even right click on a, if you have installed Plan Explorer properly, it will integrate with Management Studio. So, you could just say view this in Plan Explorer. And so, it will pop open your query text and your execution plan, and you'll be able to see that in Plan Explorer. Let's see. Here we go. So here is the query. And the first thing to notice, you had mentioned some red flags. So one of the first thing to notice is here at the very top, we tell you that the estimated rows and the actual rows are very, very different. And the first thing that that tells you is that the index statistics that were used to compile this execution plan are very stale. They've got bad numbers, basically. It thinks it has 10,000 rows when it really has 20,000 rows. And so the execution plans you get are gonna be impacted by that accordingly. Also notice down here as we look at the diagram, we have some pretty colors. So right off the bat, we can see, all right, 15.4% of the cost is on this clustered index scan. And that could mean every row in the table. It could really slow you down in this situation like that. Ah, but notice over here, we now have something that's also in red. So that's, you know, even easier to detect where my, where my costs are. And here we have a full index scan. So we can look through and instead of muddling along in Management Studio, we can very quickly find what our most expensive steps are. If the execution plan is extremely large, then we can come down here, and I like to use this top operations tab. And so this shows us all of the, I'm rearranging my space here, this shows us all of the different operations that we see in the execution plan, except now it's giving us even more feedback on each of those steps. So we can see that these are scans that have more than 100 per row. Here we can see that we have a big variation in the actual and estimated rows. And so it just helps us determine very quickly where the most expensive steps are, and perhaps we want to do something in that execution or in that query, to for better execution plan, or maybe change the query so it retrieves that information more quickly. And if there's one particular step that we are most interested in, we click on it here on top operation, we can go back to the diagram and it will show us where that operation is. Is there a way to display better or view better how my CPU and memory usage is or my IO? Right, well, that's another great question. One of the things we run into a lot when we work with queries is that in looking at these execution plans, we see that SQL Server tells us there's a specific cost, But what does that really mean? And in Management Studio, it's a balanced formula algorithm that balances CPU and IO. And that can be great if your system has balanced bottlenecks, I guess you could say, if CPU and IO are equally restricted. But here, we can be more specific. So on a right click on the diagram, we could say I would like to see my costs by just CPU bound, or just IO, or the default, which is both of them like in Management Studio. So if we switch to CPU, notice that all of the red flags shift. These index seeks are where we have a lot of the cost as opposed to the scan. So we're, looks like in this case, we're not as CPU bound, and we're more, I'm sorry, we're not as IO bound and probably more CPU bound. So let's look at it by IO just to flip it. Yeah, there's just a few problems. And those are the ones that we saw earlier when we first looked at it. Now also notice that these lines here by default in Management Studio and here in Plan Explorer show you the number of records returned. But related to your IO and CPU question, what if this 168 records all had a blob file or a big XML or JSON file? And so it's only 168 records, but each record is 100 megabytes to a gigabyte each. We can shift that around too, instead of what Management Studio gives us of rows per step, we can now just change it over to the number of bytes, kilobytes, megabytes. So this is really helpful when we, you know, don't have a good idea of how much data is in each of the records. You know, one of the easiest performance enhancements you could do is stop using select star and start using select column one, column two, column three. So this is a very good way to make sure that you're focused on the resource constraints that are top of mind. Another thing to look at is see how a lot of these steps, it might be hard to see, but a lot of these steps have a cost of 0%. So we have trouble seeing all of this slide, I'm sorry, all this execution, steps in the plan. One of the things we can do here is if we need to see everything at one time, we can zoom out to see it all. We can also, change the shape. So if you like a different style of looking at your execution plans, we can flip it around. That's an inversion of the normal approach. This is a tree shape. So if we go back up here to the top, we can see it's quite unusual with a of a long tree shape. This one is quite nice, and you could even, if you maybe had multiple unions that had very similar versions of a single query and you wanted to compare them, you could even like flip it so that it's top down. You know, quite quite handy, I guess you could say, in the sense that you can get exactly what you want to see in your plan layout. And going back to the part about 0%, another thing you could do is on the filter slider here, we could say, you know what, I don't wanna look at anything that costs zero. So let's just filter out everything that is 0%. It's got to have a cost of at least 0.1%. And now we have a much smaller execution plan. Could even, you know, when you have huge execution plans, you might want to filter out everything that is less than say 5%. And that way you only see the really big expensive steps in the plan and you can more easily get to the of the heart of the matter, if you will. So those are some just very simple, quick, easy ways to focus in on your trouble spots, those red flags you mentioned in the, in the execution plan. Is there a way to tell if this is the actual plan or an estimated plan? Right. So that's a good point. When we're, when we are in management studio, you actually have to do separate work on each of those. So we would have to select a certain button right here and say display the estimated plan. And that tells us what SQL Server thinks it will do. And then separately, we'd have to come over to this one and select the include actual plan. And then by running those two separately, we would be able to see if we had that big mismatch between what the actual and estimated plans results were. And you get that automatically in Plan Explorer. And so right here, as we mentioned with the estimated rows and actual rows, it's all right there. Awesome. And how about parameter sniffing and nested views? Right. Now let's let's talk about that as well. So we went through some of the the basics about, you know, looking in a a query to see where there were some costly operations. And for example, maybe we are doing a lot of table scans, we're doing seeks, we have a table where we shifted past the tipping point, and now it's doing scans when we thought it would do Seeks. There's a number of those special cases too, just like you said, where we have parameter sniffing. Another really common one that we see is you'll have a view that you were querying thinking it was a table. And it turns out that that view is based on another view, which is based on three other views. And so what happens is SQL Server has to spin up each of those views and then collect the results through them, sometimes recursively. Let's talk about some of those difficult situations. So when we are looking at alternatives in Management Studio, I'll pull that one up as well here. We can execute multiple versions of a query. And when we do that, we get the execution plan. Well, assuming I actually click the Execution Plan button here. So let's do that. Oh, estimated, here's actual. So we go ahead and execute that. We'll see the different queries all in that one tab right here or pane. And we can compare them just based on the total cost right here. So this one's 16%, this one's 28%. And finally, the last version, which is not equal to $2.83, we can see that it's the most expensive. But we have a lot richer means of doing that in Plan Explorer. So, and a good example is with parameter sniffing. Let's look at the command text here so you can see what we have happening. We've got a query here that we're comparing two different approaches. These two, let me change that reference. These two stored procedures are a typical approach in which we use, we pass parameters. We pass a parameter for the last name. But we also have to make sure that we return results of last name as null. So you'll see that we have lots of different clauses here for each of the parameters to make sure that we will bring back something if it is null. Or if it's not no, we'll process the query according to the parameter provided. A better way to do this is to use dynamic SQL in which we have the base version of the query. And then we simply append a WHERE clause if we have the last name variable, if we have, a store ID and so forth. And then we can execute these different store procedures and compare them all at once to what we have in Plan Explorer. So here we can see the essential query of each of these different versions of the same query. They all have parameters, and some of them might have parameter sniffing. So A, we can compare. So you see right here, we see right off the bat, we have a mismatch in the estimated versus actual. And then if we go to the parameters tab, then we will also see if there are any differences that are pretty significant in the way it operated. So at compiled time, the value was Unicode ER percent, our wildcard, and then in the runtime, it was Z percent. So that's probably enough of a difference to cause parameter sniffing. That situation I had mentioned with view built on top of view, can see that right away here. We've just said select top 10 from what looks like it could be a table, report real time. And we can see that the execution plan is very, very big. So we can just come down here and take a quick look at the join diagram. And sure enough, we can see all the tables that are joined and one of those tables is used recursively, and probably generating a ton of extra resource consumption. Right? And then finally, you know, one of the things that some people do like to experiment with, and it's a little bit more of an advanced technique. But we can show you a lot about the indexes that are being used in a particular query or in a particular situation. So let's take a look at index analysis here. Is there more I can do at indexing, using Planet Explorer, Kevin? Yes, there is a lot you can do, and this is something we'll save for a detailed discussion at a later date because it's a very rich feature. But if you go to the indexing tab for execution plan that you have collected, notice that we have in the middle what we call index analysis. And then at the bottom, we have your histogram and statistical analysis for your indexes. So if we look at the different data that we have collected on the different indexes, we'll get a different histogram for each of those. And it will tell us the different parameters used to compile and runtime. And then we can even play around here in the index analysis component and propose alternative or optional indexing based on some hypothesis, which say, oh, what if we wanted to add one more column to that index? Or what if we wanted to include a column? So that would give us an alternative way of indexing this particular set of tables that we have in the system and give us better query performance overall. Can we see that? That's really- Oops, sorry. Go ahead. I was going ask, can we see the index text from there as well then? In fact, yes, you can. There is a button here for the script. If you want to script that out, we can also update the statistics. So remember where we had all of these situations where the statistics were not fresh. So we can click on this and we will get the index script to create whatever experiment has showed us the best version of it is. It's pretty awesome. Yeah. Is there So we're out of time. Yep. I was just gonna last question for myself just to see if there was a way to have continuous insights then into the database activities past just the snapshot provided by the execution plan. So for example, if I were Right. Happening in real time using DMVs, can I see that? Right. Excellent point. So the this tool is used as a point in time. But if you want to have a full picture of everything that is happening in your SQL Server, including finding the queries that perform poorly, this is exactly what you want from SQL Sentry. So with SQL Sentry, it monitors 24 by seven. It keeps lots of information about how indexes and queries themselves perform over time, a version of query store, if you will, if you've ever used that in SQL Server itself. And so we have so many additional capabilities over and above what we have with the plan explorer tool. In fact, with SQL Sentry, plan explorer is integrated right into the product. So when you look at the top SQL page, which shows all of your SQL statements running at a given time, it will even allow you to jump right into Plan Explorer to tune a particular query as it happens. But yes, you'll be able to see all the queries that consume resources, and you can adjust the amount of time that the query needs to run before it's captured. You don't want the very quickest of queries that don't put load on the server to be part of that capturing process. But yes, you can get all kinds of deep monitoring into things that you might not find in any other tool. For example, it will detect Pneuma node imbalances. It will give you really deep information on availability groups and failover, clustering and things like that. So yes, if you need to know when an important production server is having any issues, perhaps you need to know before the end users do, then you definitely want SQL Sentry. Awesome. Thank you very much, Kevin. Well, I hope that was a decent and useful primer to what Plan Explorer can do for you, Richard. I will say that there are so many other features that we haven't gone into. It really does have a lot of capability over and above what we've just shown. But for me, many of the things that I just showed are now essential. It's hard for me to do without. And so every time I install SQL Server Management Studio, which is also essential, then I also install Plan Explorer right there with it. So with that, thanks so much for your time. I really appreciate it, and hope that you found this valuable. Love to see you download the free Plan Explorer product. And if you are able to, please take a look at SQL Sentry. We have an online live demonstration that you can look at through the web portal. And then we also have the full product you can download with a thick client that you can use in your shop. Give it a try. It's free for fifteen days, and so you can see if it will actually help you in a powerful and productive way like I believe it has helped me over the years. Thank you again. Awesome. Thanks, Kevin.