Introducing Performance Studio Free SQL Server Query Plan Analysis Tool

Posted on by Erik Darling

Introducing Performance Studio Free SQL Server Query Plan Analysis Tool

Summary

In this video, I introduce my latest tool, Performance Studio, which I’ve been working on and refining for a while now. Performance Studio is a powerful, free tool designed to help SQL Server professionals analyze and optimize their queries more effectively. I walk through some of its key features, including a query editor that allows you to run arbitrary queries against a SQL Server, and an execution plan viewer that provides detailed insights into query performance. I also highlight the integration with SQL Server’s Query Store, which helps track and analyze query performance over time. Throughout the video, I demonstrate how Performance Studio surfaces important information like runtime summaries, weight stats, and parallelism effectiveness, making it easier to identify areas for optimization. I encourage viewers to check out the tool on my GitHub repo at code.erikdarling.com and provide feedback to help it continue to evolve.

Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video we are going to debut on YouTube, it’s been debuted in other venues for a little bit now, my new tool called Performance Studio. Now, this sort of spawned from the plan viewer that I added to the monitoring tool stuff, but I didn’t want to add all this stuff in there. I think that sort of focused, and it seemed like this would be a nice sort of spiritual successor to Plan Explorer, which has been sort of left on the shelf for a very long time, and has not seen a lot of investment or work or new development. So, we’re going to go through some of the sort of top-level features here today. Hopefully this will convince you to go get it. Now, this is all available on my GitHub repo. It’s at code.erikdarling.com. So, if you’re interested in this, you can go there, check out the releases section, and you will find the latest and greatest from what I have here. Now, the first thing that you’ll see usually when you open this is a query editor. I do allow you to run arbitrary queries against a SQL Server of your choice, so don’t do anything here that you wouldn’t do in SSMS. You run the query, it’ll discard all the results and just give you an execution plan back. You can get either an estimated or an actual plan, depending on what your needs are at the moment. But here we have a simple query, just select, just connected to Stack Overflow 2013. If you get an actual plan, we get this back. Some of the stuff that I surface, you know, sort of top-level is, you know, things that hopefully help you figure out if the query in front of you is, you know, worth dealing with. So, you know, it gives you some server context, gives you a runtime summary, and over here it’ll show you weight stats for the query in front of you.

So, you can sort of graph those out. There’s not a lot too much, there’s not too much interesting in this plan, so I’m not going to spend too much time here. We have some other interesting plans loaded up that have a little bit more to them, so we’ll do that. Another thing that’s built into this is query store integration. So, you can connect this. Right now, I just have it set up to grab the top, you know, like some number of plans, some number of hours back from query store, and you can either load all those selected plans into new sort of plan view or windows, or if you just right click, you can go grab one and say load plan, and it will bring the plan from query store up in there.

So, we do have a little bit of stuff in there. On the query store thing, I am going to add a bit more to that. Some filtering mechanisms, query ID, plan ID, procedure name, query hashes, and whatnot. Stuff that you would expect to see in my store procedure SP quickie store, to sort of help you locate the query plans that you care about. But, I have some actual plans with some actually interesting things in them.

And before I get too far along in this, I want to show you some of the buttons. So, we have this bad time SQL plan up here, and if I hit human advice, I’ll get this tab that comes up, and this tab will give me a sort of human readable analysis of what went on in the query plan, things that you might care about, you know, how long different operators ran for. One thing I’m particularly proud of in this is that, and it’s probably not spot on perfect just yet, there is going to be some edge cases and things that I need to deal with, but in Management Studio, we’re in row mode, and if you get an actual execution plan, the operator times are cumulative going from right to left.

I’ve done my best to sort that out, so you just see the per operator times like you would with batch mode operations. But, down in here, you just, you know, you get a, this thing, you get a breakdown of, you know, operators in the plan that ran for a long time, you get information about weight stats for the plan, assuming that, you know, you’ve run the plan, you’ve gotten the plan in a way that collects those things, and then we’ll give, then I break down all the plan and operator warnings, and if there are any missing indexes, we’ll surface those. One feature I’m working on right now is to, if we have an actual plan, I’m going to be getting rid of that silly impact percent number, and I’m going to replace it and try to match the missing index request to the operator that it happened on, and sort of give you the operator CPU and duration for that.

That’s another thing that I added to this, that SSMS is sadly lacking, is if you look over here, you know, SSMS only shows you the duration number. I’m also showing the CPU number for this, so you can not only see how long something took, but you can also see how much CPU it burned. So there’s a lot of good stuff in here that, you know, I think, surfacing extra good information about the query that ran, so that you can get sort of a good, sort of high-level visual, like, understanding of where you need to focus.

Up in the runtime summary for this one, I got a little tooltip on that, up in the runtime summary for this one, there are some things in here that I’m going to show you in other plans where it’ll change, but, you know, stuff that I wish I kind of knew about when I was looking at a plan, you know, 24 of 32 threads used, we had 8 inactive CPU threads on this. We got granted 1.89 gigs, and it calls it out explicitly, like, hey, we only use 4% of our memory grant.

I’m going to probably tweak that rule a little bit for, but I just wanted to start with, like, a 1-gig grant to get started. But then there’s this other thing, and actually, let me show you this in a different query in a minute. But the parallelism effectiveness is another cool sort of thing that I look at.

And a lot of, some of my videos where I talk about, like, parallel plans that actually just run on a single thread, I actually measure stuff out to give you warnings about that if you run into this situation. If you hover over the select or the root operator in the plan, you’ll get a sort of high-level overview of all the sort of warnings that got called out in the plan.

Stuff like, you have a large memory grant, apparently, and an excessive memory grant. We currently have two giant forms of memory grant in this. Scans with predicates, so indicating that, you know, maybe an index would help you out somewhere.

Things like that. And, you know what, let’s go to some other plans in here that have other stuff in them. Oh, wait, I was promising to show you some buttons. Under robot advice, if you want to paste this to the robot of your choice, there is a JSON schema of all the stuff that ended up in the human advice.

And there’s also two buttons over here. One to copy a repro script. So if we hit that and we paste that in here, you’ll see that, like, we got the full query so that we could run this, or we could edit this query and try to tune it.

And also, if we hit just run repro script, then this thing would run and, well, we have to get it connected first. But if we run this, then we’ll go get an actual execution plan for it. But while we’re waiting on that thing, right, because who knows how long that’ll take, there are some other things in here that are useful for us.

So where I was talking about the sort of parallelism effectiveness, note this thing ran at DOP 8, right? But the CPU was only about 3x or a little bit more than 3x the elapsed time. So DOP 8 was not terribly effective, right?

We had a lot of parallelism, but not a lot of parallelism helped, right? Because in an ideal world, elapsed time, or rather CPU should be like, if it ran at DOP 8, elapsed time should be like 8x, or rather CPU time should be like 8x elapsed time. So we did not quite get there, right?

We only got to about 3x. So, I mean, not saying this plan shouldn’t have gone parallel, but maybe like even like DOP 4 would have been just about the same there. Some other neat things that go on in here.

You know what, let me get to this one. Something that I have wished SSMS would yell about a lot more loudly is when you have a many-to-many merge join that actually uses a work table and does a bunch of stuff, because this is not a good time. This is a real performance nightmare.

So this is one thing that I decided I really wanted to show you in query plans. If we look at the parallel skewed plan, this is another thing that I wish SSMS would have been more loud about. But if we hover over this index seek, we’ll see this warning here.

Ah, there we go. Parallel skew. I’m all in the way. Let’s see if we can get that to a slightly better place on the screen. All right.

There we go. So parallel skew. Come on. Zoom it. Cooperate with me, would you? All right. Parallel skew. 100% of the rows. All right. All went to one thread.

So if this happens in a parallel plan, I will be loud and clear about it. So another nice thing that you can use to sort of figure out where things are going wrong in a plan. Some other stuff that I wish SSMS was louder about.

Really surfacing when we have a non-parallel plan reason. So this will tell you if it can exactly why a query was not allowed to go parallel. And then if we hover over the clustered index insert right here, which is to a table variable, we’ll see this lovely warning down at the bottom here that says table variable.

Modifying a table variable forces the entire plan to run single threaded, replaced with the temp table to allow parallel execution. All right.

So there’s all sorts of helpful stuff for you as query tuners to just very quickly get a lot of information without having to do a lot of digging into query plans. Now, the last thing that I’m going to show you here today is just a quick plan comparison thing. So if we hover, if we, rather, if we select this query plan and we hit compare plans and we change this to do-do-do-do and you and we hit compare, rather than have like a big complicated like picture of what’s different, this just breaks down all the stuff that changed in a plan and got better.

Right. So, you know, runtime went and CPU time went from like two and a half seconds to 639 milliseconds. And, you know, you get, you know, sort of good information about what improved in a query.

So, you know, that’s like a nice textual breakdown to give you like, oh, when I did this, when I made these changes, here’s how the plan got better. Anyway, this is, you know, this hit v1.1.

So there’s still a lot of work that I’m going to be doing on this alongside the work that I’m doing on the performance monitoring tools. Hopefully you will try these things out.

So stick around and see what I have in store for them. I’ve got a lot of neat stuff that I want to do, but I was rather proud of where I got this to in a pretty quick set of time. So, you know, I wanted to get it out there in the world and hopefully get some people trying it and giving me feedback and, you know, or maybe just telling me that it’s cool or, I don’t know, maybe telling me that it sucks.

I don’t care. The lovely thing about free software, it costs just as much to use it as it does to not use it. So there you go. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope you’ll try this tool out. Again, it’s at my GitHub repo. If you want to get there, it’s code.erikdarling.com. And if you run into any issues or problems, questions, anything like that, that’s the place to leave me feedback.

Thank you for watching. And I will see you over in tomorrow’s video where I suppose we will talk more about SQL Server performance monitoring issues. All right.

Thank you.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts