Free SQL Server Performance Monitoring: Query Performance Tabs, Including Query Store
Summary
In this video, I dive into the query performance tabs within my full dashboard, a free and open-source SQL Server monitoring tool. I highlight key features such as the QueryStore subtab, which provides detailed insights into query execution plans and regressions over time, helping you identify and address performance issues more effectively. Additionally, I explain how the custom trace patterns tab offers valuable data for safeguarding against overlooked performance anomalies, ensuring a comprehensive view of your SQL Server’s health.
Chapters
- *00:00:00* – Introduction to Query Monitoring Tool
- *00:07:17* – Detailed Execution Plans and Metrics
- *00:10:29* – Conclusion and Future Updates
Full Transcript
Erik Darling here with Darling Data, bringing that big monitoring tool mogul energy that the SQL Server world was so clearly missing. I want to talk today about some of the query performance tabs that are in play, at least in the full dashboard. The full dashboard actually has one extra tab that the light dashboard does not, and that’s the query trace patterns. So that’s, like, sort of a custom trace that I spin up in the background. It’s, of course, completely optional. You can turn it off, you can get rid of it if you don’t want it.
But something that I use to sort of, like, as a sort of, like, safeguard to, like, make sure that I’m actually getting some stuff that other things might be missing. But it collects from all the normal sort of data sources that you would look at when you’re doing a performance analysis. So, like, you know, like the query stats, procedure stats, trigger stats, function stats.
It goes to QueryStore. Now, a lot of paid monitoring tools out there do not touch QueryStore. And quite frankly, the vendor should be embarrassed, right?
It’s 10 years old and you haven’t put it in your product yet. Just close down, right? Just stop, right? Like, you don’t care, right? You’re coasting, right? It sucks and people pay you for this, right? So they, like, made me angry enough to do all these things.
So the tabs that you’ll see in the full dashboard are these. You’ll see all these except the query trace patterns one in the light dashboard. I mean, slight differentiation.
This is just something that’s a little bit easier to manage with the store procedures and stuff that I have in the full dashboard that run everything than it would be to do with the light version. So just a small difference there.
But like I said, like, all the query grids that you see will have a pop-up, like a drill down menu. So if you double click, you can get more information from them. There’s also all sorts of stuff in there that you can do that will give you sort of an experience, like looking at the QueryStore GUI, where you can, like, change metrics, except I don’t actively hate you and I want you to enjoy looking at things.
So, like, you know, I try to be a little bit kinder. Anyway, the QueryStore subtab, we also have a QueryStore regressions tab, right? So there’s one that just looks at general QueryStore stuff and one that points out queries that have, like, regressed in some way over, like, a period of time.
So you do get some of that, like, like, oh, cool. Like, like, yeah, that thing did get worse. Like, I should go look at that.
And then you can, like, see all the plans for stuff that got worse. But a lot of the DMVs I discovered are just sort of cumulative over time. So if you just keep logging data from them, like, the numbers just kind of keep going up.
And if you, like, it sucks, right? Because you just keep, like, oh, yeah, it’s like, oh, my God, it was 60. And now it’s 60.
And now it’s seven. Ah, crap. So I calculate deltas for some of the DMVs that are cumulative. Like, QueryStore doesn’t really have this problem, but some of them do. And you have to do some math.
And I got some help with the math because I am most assuredly not a math person. Where I do some sort of per second rate normalization, which means that I basically divide the change between one collection and another for a query or a procedure or something. And sort of, like, they divide it by the elapsed seconds between collections, which gives me sort of a consistent line, even if collection intervals vary a little bit.
But it makes comparisons a little bit more meaningful when you’re looking at time ranges. So, again, if you want to check all this out, it’s at code.erikdarling.com. It’s in the performance monitor repo.
If you want to download it, it’s all going to be in the releases section. That’s where all the zips are that have the EXEs in them. So, you can go in there.
You can get it. You can try it out. And, again, if you go to unzip it and you can’t see an EXE, sometimes that’s Microsoft security stuff. You might have to, like, unblock it to get it to show. But let’s go look at the actual dashboard because I’ve been talking about looking at PowerPoint too long.
My eyes hurt. So, when you first go in to look at queries, you’ll have the performance trends graphs. Apparently, I really am turning into a developer because I have not executed a stored procedure in 24 hours. I don’t know what’s wrong with me.
My head’s spinning. But, sort of behind me here under this armpit, this is where you just see sort of query executions a second. So, like, spikes and, like, when, like, activity really ticked up. That’s this graph over here.
Apparently, we had a real high point at 4.5 a second. Whew! Yeah, I’m thrilled with that. And then we have query durations from, like, the plan cache over here and from query store down here. You can see that there is some stuff that the plan cache got that, or rather, the query store got that the plan cache didn’t, which is fascinating to know about, right?
Plan cache is unreliable. That’s why I prefer query store. This is why monitoring tools that don’t touch query store should go just, like, head in the oven goodbye.
Over here is the active queries tab. This basically just runs spwhoisactive and logs it to a table. You know, like, all the mechanics of that are boring.
Ah, crap. Sorry, add a mechanic is not boring. The mechanics of how that works is boring. So, if you want to look at what’s currently running on a server, you have a current active queries tab. So, if you push the refresh button, this will actually run one of the queries that I use in sppressure detector to just, like, execute against the server, grab whatever’s running now, and pull it back.
This is just a little bit easier, because, like, if I wanted to do that for spwhoisactive, it’s, like, you start thinking about, like, exposing all the different parameters, and then you have, like, a dynamic column list, and, like, hey, it gets to be too much. So, like, I just wanted to give people, like, a fairly simple, like, what’s running right now query to sort of back up what shows up in spwhoisactive. Again, there’s nothing running on my server right now, but, hey, I pushed the button and it didn’t crash, so we’re in good shape, right?
We have the query stats over here. This is what’s pulling from the plan cache. You know, you can sort this any way you want, like, up and down by any of these columns.
If your, you know, code came from a stored procedure, which clearly mine in the last 24 hours has not. I am fully ad hoc developer. You know, the object will show up in here.
But, you know, you get all the sort of information that you would want from all this stuff, right? Like, you know, like, CPU, reads, memory, DOP, like, everything that you would want. The query text.
And then if you go and you right click on something in here, right? You know, I showed this yesterday, but I’m so excited about this, I’m going to show it again. If you go in here and you say, like, view plan, you can see the estimated plan for something, right?
And, like, again, like Microsoft making that MS SQL plugin for Visual Studio Code really made all this very easy. Because I had all the assets I needed for the icons and all the XML information I needed. And so, like, I could get, I could really just plug this in basically.
If you come over here and you click get actual, I’m going to do this for a slightly less annoying one. Say get actual plan. Then this thing will execute the query against the server.
It will throw away the results and it will produce an actual execution plan, right? And so, here it is. And, again, you get all of the execution numbers against it. Now, something that I’ve, you know, I’ve said Microsoft should do for a long time.
Is when you get an actual execution plan and you have, like, all they show you are the durations. I also expose the CPU time, right? Because you get CPU counters per operator as well as duration per operator.
So, I expose CPU time in there as well. Just to zoom in a little bit closer so you can see, right? Like, the top number here is the normal duration number that you would always see.
But underneath you have the CPU time, right? So, like, this, this operator, you know, let’s just pretend it ran for one and a half seconds. The CPU time is 1.13 seconds because we had some time spilling, right?
So, that’s what that indicator up there shows you, right? That we, like, you know, something happened. So, we have that and then coming back to queries, though.
Procedure stats, again, I haven’t run any procedures lately. So, that’s empty. Query store shows you all the query store data. Now, if you click on a query row in here, like, we can saw that one had, like, 60 executions for it.
This right here is what you get when you click in and drill down. So, you can see, like, all of the, like, per execution things in there, right? So, every time this query executed, like, how long it, like, all the different execution metrics.
This query apparently hasn’t changed really since at all since it did anything. So, that’s nice. But up here in the top corner, this is where you can change sort of how the graph shows things, right?
So, you can change it to any one of these and you’ll get a slightly different view of it. There’s no difference between CPU and duration. So, not a lot of fun to look at, to be honest.
But, the drill downs are the important thing here. When you double click on them, you get all of the query executions, right? So, that’s important, right?
You might want to see more than one thing there, right? But then, coming a little bit further, this is query store regression. So, you can see, like, some query that did worse over time, right? You can, again, you can drill down into any of those.
And then, query trace patterns is just stuff that I capture with my sort of special, like, performance trace of, you know, bad queries. Anyway, that’s sort of an overview of all the different query tabs in there. Again, if you want to check out this completely free open source SQL Server monitoring tool, it’s at code.erikdarling.com.
Again, go to the releases section, download a zip. There are EXEs in it, I promise. If you don’t see them, your security software is messing with you.
So, you should do all that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where I will continue talking grandiosely about this wonderful monitoring tool of mine. It is completely free and should hopefully put every other monitoring tool company that makes you pay for their garbage out of business.
Alright, 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.
Okay. Where do you get 👀 it?
I’ll give you three guesses.