Performance Monitor is Going Enterprise

Posted on by Erik Darling

Performance Monitor is Going Enterprise

Chapters

Full Transcript

Erik Darling here with Darling Data and in this video I’m going to talk about where the free SQL Server monitoring tool is headed in the near future and go over kind of what I’ve been working on, why I haven’t done a release in a couple weeks now. So when I first started working on this, my original goal was to make something very easy for people to set up and run depending on their preference or depending on certain requirements. There was what I call the full dashboard that created a database on your SQL Server, logged a bunch of things there, and you could query it, you could back it up, you could send it to someone, you could do whatever, just about whatever you wanted with it.

But there were some problems with that setup. One was that a lot of people thought that that would be the one database that everything got logged to that they were monitoring. They didn’t realize it was one database per server and that database had a tendency to get fairly big.

So I had to take some additional correct… When I first made the whole thing, you know, I made sure all the indexes were compressed and took various steps to try to keep things neat and tidy. But, you know, certain…

Collection elements like query text and query plans and blocked process and deadlock report XML tended to bloat things out. So some additional corrective steps I took were to use the compress and decompress functions on those things. And that cut down on the size quite a bit.

But the… And I think I haven’t gotten really any complaints about that stuff in a while. So hopefully that solved the problem.

But… The additional sort of friction for me was having to maintain two different code paths. If there was a bug in one, there might be a bug in two.

And getting my various clod army to remember that there are two and fixes need to go to both and not defer or downscope things randomly was a challenge. So that wasn’t fun. So my first idea was I could make a lot of the stuff…

Between the two monitoring tools into sort of shared libraries. And that way fixes would only need to go to one place. That didn’t quite go as I planned.

So… And I still had this sort of additional friction point of, you know, light being very, very popular. But, of course, light is a user application.

And if you close your laptop or shut down a VM that you have it running on… Or, you know, you close the dashboard, it stopped collecting. That was what full tried to sort of get rid of.

But full had its own problems that I’ve already talked about. Full was just agent jobs running, constantly collecting stuff. As long as your SQL Server was up, data was flowing. So that was…

So, like, neither one really solved the full picture. So what I’m doing now is I’m essentially deprecating the full dashboard. I am keeping the sort of…

Portable light version of that. It’s kind of like when you download Crystal Disk Mark. You have the full installer or just the zip that you can crack open and run. And choose your favorite anime girl theme. That’s the best.

So it’s sort of like that. Minus anime girls. Unless… If anyone wants to donate or, like, add some themes in to include anime girls, I’m totally fine with that. If you’re into that sort of thing, skin it up.

But, you know, it’s not my thing. No pillows with faces on them for Erik Darling. But…

So the direction that I went is… One, I wanted something that would still be free. Or at least as free as possible. And so I chose Postgres with the Timescale database plug-in.

Or… What do they call them? Extension in there. The reason for the Timescale thing is it offers incredible compression.

Like… Postgres on its own does compression pretty well. And they have this toast thing that kicks in for string columns of a certain size. And in Postgres 18 you get this crazy LZ4, I think, compression on stuff.

So, like, Query Text and Query Plan XML and the Deadlock and Block Process Report XML was already doing a pretty good job of staying nice and small and tidy. But we have the additional sort of Timescale thing kicking in and keeping things even smaller. Which is wonderful.

So Postgres is the chosen backend. That means you don’t have to pay for another SQL or worry about having to pay for another SQL Server. I know a lot of y’all out there really like putting your monitoring tools on Developer Edition. I’m not the licensing police.

But I’m just saying… Might be a little dodgy. Like, in terms-wise. But… You know… That’s…

Between… That’s between you and Microsoft. I got nothing to say about that. I am just a lowly consultant out in the world trying to make a difference. So, yeah. So it… The Postgres part is free.

You will pay the… Potentially pay the cost. You can bring your own Postgres, too, if you want. So if you already have a Postgres server running somewhere that you’re okay with having a monitoring tool database in, you can bring your own. Otherwise, you would just need a new VM or something that has the Postgres instance on it.

And what this thing… What this uses is a headless Windows service. Headless meaning it is not tied to a user.

It is part… It is running on Windows regardless of… Well, I mean, unless you shut the VM down completely. Right? If it goes to sleep or you log out or something, it’s fine.

But… If you shut the VM down, you… I can’t… I can’t help you with that. So it’s running constantly the way that the agent jobs in Fullwood to collect data and put it into Postgres. And it’s sort of…

And it completely detached the collection service. The collection service from the viewer. So if you have multiple users… This is another friction point was getting this so that multiple users could all ping in and see the same set of stuff all at once.

You can do that. This also allowed for some neat security stuff where there is like an owner account that can do anything. There is an admin account that is allowed to change schedules and collection stuff and alerts and whatnot.

And then there’s just a reader only. So if you have folks who shouldn’t be… Who you don’t trust to tinker with those things, they can… They have a read only path to see the monitoring data without being able to mess with anything.

So all good there. And now we have, behold, a viewer with some improvements, I think. And if you’re asking how this solves the double bug thing for me, it doesn’t completely but it does make it easier.

Because much more of the viewer code… Viewer code path stuff is shared between light and this thing than with the full dashboard. So that’s good there.

Anyway. What was I saying? Yes. Good. So now we have this. And I’ve made some visual improvements and some performance improvements to things along the way. So you start off with what you would normally see when you open up light pretty much.

Again, some improvements. We’ve got some new stuff up here that show you the number of servers. How many are healthy and all that stuff. And then you have the normal sort of NOC style dashboard in here.

I believe HammerDB is… No, I think maybe… No, HammerDB probably finished on 2025. That’s why things are all green and happy over here. But going into the monitoring data itself, we can see…

Actually, let me set this to the last like four hours so that things are a little bit more zoomed in to when we had HammerDB running and doing stuff. So this is the sort of normal stuff that you see. We have our overview here.

We have our weight stats here that includes top weights and, you know, like the top weights that you have in here along with some of my favorite weights to show people. There are some neat additions when you like click on stuff and look at things in here. But you can just as always, if you want to, you know, see queries causing a problem, you right click, you get to those queries.

And under the queries tab, we have all the… Again, just same user experience as light. Just with a different back end that is a constantly running collector to get stuff from.

So all the stuff that you would want to see in here. The UI is a lot snappier too. I think clicking around through here, sometimes there were some long pauses on things I was always mad about.

But now everything seems pretty snappy. There are some new collection metrics in this one as well that, you know, give you a little bit more information. You know, there was some stuff in full that I liked.

That I wanted to get further into. So I’ve got all this stuff in here. And there’s no memory pressure events on SQL Server 2025. But over on 2022, if we look, we can see some memory pressure events if we go back to the last 24 hours.

You can see where, you know, SQL Server 2022. So SQL Server 2022 is my old prod VM that has been downgraded to like 2 gigs of memory and 4 cores or something. This thing gets beat up a little bit easier.

But 2022. 2025 is where I do like all my new development work and stuff. Now it seems pretty safe and worked out. So we’ve got all this stuff in here.

All the same stuff that you would expect. One big thing that I corrected on the advice of the lovely and talented Kendra Little was TempDB has been unpropercased. And it is now in its proper state.

Right? All lowercase. Good. Right? That’s nice there. But everything that you would want to see around blocking and everything. Right?

That stuff. Current weights. Look at all those wild spikes. Look at our blocking stats. Look at our block process reports. And our deadlocks. And our perfmon. Right?

And perfmon. Remember perfmon has all the different collector packs in here. So depending. So like you don’t have to remember all the stuff that you want to see. All the counters that go into certain perfmon things. And other stuff that was brought over from full into this that wasn’t in light.

We have stuff like session stats so you can see what things were running. Like sleeping background total. All that good stuff.

We have our usual agent job things going on. Our usual configuration and configuration changes stuff. One thing that I did change.

So daily summary used to be just a one line about today. Like this isn’t all lit up obviously because I haven’t. It’s kind of new. Right?

So it’s like new data. But one thing I changed in here is I made this a calendar view where you know like you can see the good days and the bad days. And then you can like sort of click and see what was going on with the good days and bad days down here. And you can you know it’s like oh you had a lot of deadlocks and blocking and crappy queries.

So you can go and look and stuff in there. So this is an improvement I think. System events.

There’s not a lot in here because this is all from the system health extended event. And quite frankly once again. The oh crap section. If you have a lot of stuff in here. You might want to hire a young handsome consultant with reasonable rates like yours truly to come help you with your server.

We’ve got some latch and spin lock stuff in here that got promoted in from the full dashboard. Because some people despite best efforts still care about this stuff. And of course the collection health stuff where you know I get to tell you how well my performance monitor collector is doing.

So this is. This will be in the next release. Available I want to say in the next few days or so.

Most of the work is done. There is just a little bit of polish that needs to be completed. And once that is all done.

Sorry. So mid week or so. Well actually you’ll be seeing this on Thursday. So it might have even been released by the time you see this. We’ll see how that.

We’ll see how that plays out. Anyway. This is where things are. This is where things are headed. Much closer to sort of an enterprise monitoring tool than the previous versions. This.

If I had to put a high number on it could probably support about 500 servers. If it needed to. If you have 500 servers. God bless.

And again this is totally free. There is no cost to you. There is no per server or anything. If you want to donate money to this project you can. If you want to. If you need a support contract that stuff is available.

But otherwise it’s just totally free monitoring. You can get it at code.erikdarling.com. That will bring you to my GitHub repo. And it’s just under the performance monitor section.

So anyway. That’s where things are at. Thank you for watching. I hope you enjoyed yourselves. I hope you’ll use the monitoring tool in its full enterprise glory. And if you have any questions, comments, concerns, problems.

That’s what GitHub is for. Otherwise. Thank you for watching. Goodbye.

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