Free SQL Server Performance Monitoring: Lite Dashboard

Posted on by Erik Darling

Free SQL Server Performance Monitoring: Lite Dashboard

Summary

In this video, I dive into the Lite dashboard of my free open-source monitoring tool, which has garnered significant attention with over 10,000 installs based on GitHub repo stats. I highlight its user-friendly nature, especially for consultants or those who can’t install software on client servers, as it allows you to collect a wide range of performance metrics without the need for a separate database. I also showcase how DuckDB, an embedded analytics database, powers the Lite dashboard, ensuring fast query performance and efficient storage through Parquet file compression, making it an ideal solution for monitoring Azure SQL databases and other environments.

Chapters

Full Transcript

ErikMonitoringToolMogulDarling here. I suppose my rates are reasonable for this free open source monitoring tool as well. So, you know, that’s nice. But I want to talk about the Lite dashboard a little bit more. This is far and away the most popular download based on GitHub repo stats. I do keep an eye on those because I care and I want to make sure that people are using this, getting stuff. I want to say that we’re very close to 10,000 installs, or at least that’s what it would seem like based on the numbers that I’ve seen over the past week or so. So that’s pretty exciting. Big monitoring. I’m coming for you. Coming for your customers. I’m going to eat you alive. Anyway, the Lite dashboard is fun because you don’t have to install anything. You just open it up, point it at a SQL Server, even Azure SQL database, and it’ll just start chewing away collecting data and making your performance tuning life easier. It’s got a lot of the same tabs and collectors that the full dashboard does, but it’s also got some kind of neat stuff internally.

And the thing that I really love about it is, you know, I’m a big fan of DuckDB. I think it’s one of the coolest things that’s happened in databases in a long time. And so what I wanted to, I wanted, when I was first working on this, you know, doing this sort of design spec, you know, almost anything where it’s like embedded database, you’re like, oh, SQLite. The thing is, I’m not doing SQLite work here. All right. I’m not doing tiny little transactional things. I’m like inserting bunches of rows and stuff. And then I need to like, you know, run reporting type queries off them. And DuckDB is a no brainer for that. So I have DuckDB embedded in this thing. And what I do is I load up DuckDB until it has about 512 megs in the tables. And then when it, as soon as I hit 512 megs, I archive all that stuff out to Parquet files, which have insane compression on them. And that helps to keep the, helps to control the size of the database over time. The Parquet files are very, very well compressed. It’s a very cool little thing that goes on in there.

So like I said, it’s got just about all the same tabs and whatnot that you would expect to see in the full dashboard. I tried to replicate as much of it as possible. But there are a few things that I’m just not quite keen to do, like run SP health parser to look at the system health extended event. And what’s the other one, I don’t set up a custom trace in this one. So there are a couple things that I left out of light just to make it a little bit lighter. But hopefully, the set of things that I collect in here is useful. enough for you that it sort of makes up for the few things that are missing. So like I said, there’s no system events tab. That’s SP health parse or looking at the system health event. I don’t look too much at the default trace stuff. But there are some very cool things that I do have in here that I think make this a unique sort of proposition. One is Azure SQL database support. The full dashboard obviously doesn’t work there because it creates a database and store procedures and all the other stuff. So it just can’t function with Azure SQL database. Whereas the light one absolutely can. But they both have the MCP servers built in those are opt in only so if you if you want to have one of your robot friends talk to you your performance data and just your performance data, you can have a chat away with the light database and that I think that’s even kind of in a way better than the full dashboard because it is confined to what has been collected in DuckDB in the parquet files and it doesn’t have to go like run any queries against the database where the data lives. Right. So like with the full dashboard, you have the performance monitor database and that’s collecting stuff. And the MCP servers talk to just that. But this is just talking to DuckDB wherever it lives. So that’s kind of neat. It has the same alerts and email notifications system tray stuff like that.

CSV export dark theme all the things that I have in there. My dear contributor Claudio added some light theme support recently. I haven’t used light themes yet because you know I’m a dark mode kind of guy but it’s cool that they’re in there just in case you want them. So DuckDB, I chose DuckDB because it’s an embedded analytics database. I don’t need a separate server process. It lives inside its own executable. There’s no external dependencies and it is by default columnar storage. So it’s a So all of the stuff that I put in there, all the queries are very fast against it. And like when they go out to parquet, there’s like almost no hit on query performance.

When it, when my date, when the, when the, my, my DuckDB, when your DuckDB database gets to be about 512 megs, I bump everything out to parquet files and like just sort of empty the tables out and start over again. Uh, and the compression on those parquet files is amazing. Uh, the footprint on them is very, very small. Um, it’s all very portable. Uh, you can, you know, take it wherever you want. And, uh, there’s no SQL Server dependency for storing historical data. It’s all captured within the executable.

So, uh, I’m going to show you a few things poking around the, um, the, uh, the light dashboard. Uh, this is primarily aimed at, um, you know, just anyone. I mean, it says consultants, but if you can, if you’re not allowed to install stuff on client servers, um, or like, you know, your servers at work and you don’t want to like have a database sitting on there collecting stuff. This is really perfect. Just a way to like spin it up, collect, collect a whole bunch of great performance metrics.

And especially if you’re on Azure SQL database, and this is the one that supports it. Uh, but you know, you can literally run it from anywhere. Uh, if you’re in a locked down environment, we really needs minimal permissions for stuff. So it makes life a lot easier. Um, uh, that’s not what I wanted. All right. Let’s go to the light dashboard. There we go. All right. So this is connected to SQL Server 2022.

Uh, like I said, it has just about all the stuff that you would want a dashboard to collect. Uh, you start off on the wait stats tab. Um, over here, we’ve got the list of servers and their sort of general, um, you know, presence in the world. Uh, we’ve got an overview tab showing all the usual stuff where, uh, you know, what CPU is, you know, if there’s any blocking or deadlocking, what memory’s up to, uh, if there are any alerts that have been fired off.

I don’t think I’ve had any alerts recently. Um, I’m working on this FinOps tab because, uh, a lot of people have been like sort of behind the scenes asking me about this because apparently a lot of managers and bosses really care about this. So I’m trying to get this stuff going, uh, so that, um, I can sort of make, uh, on top of it being a free monitoring tool, uh, it can be something that points you to where you can save money, especially with Azure SQL database cloud stuff. Um, if things are, you know, over provisioned, under provisioned, all that.

So, uh, I’m working on this, uh, it’s a work in progress. Uh, it’ll be out soon, but it’s, you know, gonna be a few versions before I think it really finds its footing, but I got to start somewhere so I can get some feedback and start getting some, uh, real sort of real, um, you know, scenarios from it other than what’s going on in my local test servers. But just, just to make sure it all works, we get this stuff first, but anyway, we get weight stats.

Uh, again, it’s all the weights that, uh, we, that we look at in the same way as the full dashboard. So like your top 10 weights, plus the sort of usual suspects, plus the poison weights. So if anything terrible is happening, you can see it pretty quickly.

Uh, under the queries tabs, again, I’m such a developer. I don’t have any stored procedures running, but, uh, you know, you sort of get a look at query duration from, uh, the plan cache, from query store, and then, uh, executions is what’s behind me.

Um, you can get an active queries tab. This is not run SP who is active. This just runs something. This just runs a simple script from, uh, SP pressure detector, uh, to get a whole bunch of information about what’s currently running on the server.

Uh, there’s a top queries by duration. And, you know, just like with everything else, um, you know, you can grab the execution plans, you can see the queries, you can open them in the plan viewer. Um, you know, everything that you would come to expect from other, other, other parts of the tool.

And then there’s the same thing for query store. Um, the plan viewer, I’ve talked about that in a few videos. Um, we just say view plan that pops up in plan viewer and you get, you know, sort of, you get the, this is just the estimated execution plan for this, which is not terribly interesting, but you know, that’s okay.

Uh, estimated plans are not meant for real, real use anyway. Uh, we have a CPU tab that will show, uh, CPU usage, um, sort of, you know, over time, just, you know, same thing as in full. Uh, we have a memory usage tab up here, uh, where we have the overview.

We have memory clerks. We have memory grants, uh, file IO, uh, broken down, uh, first by latency, right? So we have read latency and write latency, and then we have throughput numbers.

So, uh, latency is time and throughput is like data. So these, these are all measured in megabytes and you can see like over time, just sort of, you know, when things spiked up and when you might want to take a look at things. Uh, we have a temp DB tab, of course, uh, we have blocking and deadlocking and all the other stuff.

Uh, I don’t think there’s really been any on this system lately. We have some lock weights, but nothing has really been getting jammed up. Uh, so this section is not going to be terribly interesting right here, but, you know, on your systems, it might be much more interesting.

Uh, we collect perfmon counters, just like in the full dashboard. Uh, we collect information about running jobs. Uh, I tell you about your server, database, server and database configuration, database scope configurations, and the active trace flags.

Uh, sort of a daily summary of what’s been going on on the server. For this server, it’s been pretty quiet today because I’ve been working on other stuff and, you know, trying to get ready to record these videos. And then finally, there is a collection health tab and the collection health tab is nice because this will tell you, um, if, uh, anything has been going wrong with, um, your servers.

When thing, when, when the collector has been running now, you’ll notice that there are a bunch of errors in here. And that’s because I had a wifi blip earlier and everything went weird for a minute. So the, but the wifi blip has since been, uh, since been fixed.

So you can just ignore these errors for now. Uh, there’s also a collection log that has sort of like, um, more specific breakdown of, um, like when things failed and all that other stuff. And then, uh, a duration trends tab, which will tell you, uh, which collectors, uh, sort of take the longest.

And if you look at this one here, apparently our query store query could use a bit of tuning. Cause at times it takes 1.5 seconds to grab that query store data. So I’ll be having a look at that when I’m done recording this video.

Anyway, this is just a short tour of the light dashboard. Uh, we’re going to go more in depth on, uh, other parts of this over the next couple of videos. But, uh, anyway, um, again, this is all at code.erikdarling.com.

Let me get to a slide where that actually shows up. There we go. That’s wonderful. Uh, code.erikdarling.com. Uh, it’s totally free open source.

Uh, it doesn’t do anything weird or funky. Um, you know, no requirements on email, no phone home. No telemetry, nothing like that. Uh, just wanted to put something kind of cool out there in the world to help you find folks, uh, with your SQL Server performance tuning efforts.

Anyway, thank you for watching and I will see you over in tomorrow’s video. All right. 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