Free SQL Server Performance Monitoring: Dashboard Overview: Server Health, Query Plans, and More!

Summary

In this video, I delve into the exciting world of free SQL Server monitoring tools, specifically focusing on a dashboard I’ve developed called Performance Monitor. This tool is designed to make life easier for DBAs and developers by providing real-time insights into server performance, resource usage, blocking issues, and more. With a user-friendly interface that includes various tabs like an overview, resource metrics, and query analysis, the dashboard aims to simplify complex monitoring tasks. I also highlight some of its unique features such as the plan viewer, which leverages Microsoft’s SQL Server extension for Visual Studio Code to provide detailed execution plans with actionable insights. Whether you’re a seasoned DBA or just starting out, this tool can help you keep your SQL Server environment running smoothly and efficiently.

Chapters

Full Transcript

Erik Darling here with Darling Data, here to talk more free SQL Server monitoring, make all the people of the world happy, or at least the SQL Server people of the world happy, as happy as they can be, being SQL Server people. So, I want to give a little bit of an overview of the dashboard. I’m also going to, at least at current, you know, when this video publishes, these features might still be in dev, but if you’re watching this well after the fact, they’ll, you know, just look at the date on the video, you can see some cool new stuff that I’m going to be releasing. So, the full dashboard has a bunch of different tabs in it, because people love tabs, graphs, and all that stuff. I’m not saying that, like, you know, it’s for everybody, but, you know, a lot of people who like monitoring tools like this sort of thing, or need monitoring tools, they’re like, oh, look at that pretty line that shows me where everything went wrong. So, I have, like, an overview, a bunch of stuff in it, of course, a lot of stuff about performance, resource usage, how memory is getting used, a bunch of stuff about blocking and deadlocks. And then, my store procedure, SP Health Parser, which goes into the system health extended event, populates a lot of system events, but I’m going to tell you something, if these graphs are empty, it’s a good thing, right? Because you don’t want, like, ideally, like, you want the system health extended event to be like, I got nothing, we’re healthy, we’re good. No news is good news.

Also, you know, kind of, you know, some standard stuff, like a Nox style landing page, sidebar with all your servers in it, and you can pick favorites if you want. Everyone has favorites. And, of course, a whole bunch of ways to alert you. There’s email, there’s, you know, like, little pop-up alerts, stuff like that. There’s also, like, little alert badges on different charts and graphs when we find bad things. So, like, the Nox style health cards, they basically just tell you, like, kind of like a short story overview of what’s going on with the server. Is it up? Is it online? Like, CPU usage? Is there blocking? Stuff like that. And then all of the, like, the overview tabs that you see, like, all the other tabs that you’ll see going through the dashboard will break out other resources, usage, queries, stuff like that.

There are a bunch of UI controls that you can use to look at different time periods. I tried to put some simple ones in there that help you sort of figure out, you know, just, like, last one hour, 12 hours, 24 hours, week, 30 days, stuff like that. Because, like, no one likes fiddling with, like, you know, like, calendar pickers for everything.

There are calendar pickers in there in case you need to fiddle with those things. But in general, people kind of want, like, like, show me the last day or something. Like, show me, you know, the last, like, since, what happened since this morning.

And there’s also a bunch of stuff that you can do to get data out of there and, you know, things like that. So, like, when you right click, you can, like, export stuff to CSVs, you can save graphs as pictures, you can copy cells and rows. Again, you get, like, system tray and toast notifications when things go wrong, like, in the middle of whatever.

And there’s also a way via the dashboard to edit schedules so you can manage how things, like, what cadence things get collected at. I put some pretty aggressive defaults in there because I wanted, like, it’s like, like, let’s start, create, let’s just get stuff. And then you can tone that down based on your environment if you want.

Like, I have some stuff that I would be like, look, if we’re going to figure some stuff, if we’re going to figure out some performance issues on your server, we’re going to have to collect some data, right? Because you can’t see a problem, you can’t solve a problem. But, so, I start pretty aggressive there.

But if you want to tone that down, you are absolutely welcome to. If you want to check out any of this, go to code.erikdarling.com. Everything is in a repo called Performance Monitor. The way to download it, because I’ve gotten a lot of questions about it, the way to download it is you go to the Releases page.

It’s a pretty easy to find link at the repo called Releases. There are zips for everything. There’s the Lite dashboard bundled separately, the installers, and, of course, the full dashboard, which is what we’re going to be looking at today.

So, if we come over here and look at stuff, this is basically what you’re going to be greeted with when you first open it. It looks like this. So, you have these server cards that show you sort of everything that you kind of want to know just at a glance about a server.

Like, what’s CPU? Like, how many threads are in use? Like, what’s memory allocation look like? Ah, I got a little thing there.

That’s hanging off the edge a little bit. I’ll fix that. But this tells you sort of the difference between, like, the buffer pool and query memory grant. So, if you have queries using a bunch of memory on there, it’ll let you know.

Also, like, when the last time you had blocking or deadlocking was, and if your collection health is okay. So, that’s all that stuff. There’s also an alert history tab.

So, if, like, I haven’t had any alerts fire off in the last 30 hours, but if I change this to seven days, we’ll see, like, a history of alerts that went out. You know, you can dismiss these and, you know, do what you want with them. But, you know, I just think it’s nice when people say, hey, like, hey, like, maybe I was away from my computer for a little while.

Maybe I have an email, like, let’s set up email alerts. Maybe I just look at what fired off that I might have missed. But once you dig into a server, once you open up one of the server tabs, this is what life generally looks like.

You know, like I said, there’s a resource overview there. So, you get, like, CPU. And if you hover over this stuff, you can see all the little numbers and, like, you know, what was going on and what your weights were.

So, like, just sort of a general overview of things. There are other tabs. They’re not going to have anything interesting for me in there right now. But, like, you know, like, this will give you a summary, just a one-line summary of, like, if your server is in normal health, what your total weight was and how much wait time was spent on it.

How many, like, you know, like, long-running queries and deadlocks and blocking and stuff we found for that. And then, you know, like, I’m going to be expanding on this. But this gives you sort of a breakdown of, like, critical issues on the server.

It basically runs the code from my perf check procedure and looks for terrible things happening on your server and reports about them here. I have some stuff from the default trace in here as well. So, like, some of the, like, high-level stuff that comes out of there.

Also, a tab that tells you about your current server configuration. That’s at the server level, database scope configuration stuff, and any trace flags that someone might have enabled. This will log configuration changes.

So, at the server database and trace flag level, if anyone has changed anything about the server, that’ll show up in there because we’re just checking to see, like, every day or so if things change. Collector health tells you how things are going, like, if anything’s failing, getting messed up. And then the running jobs tab.

This tells you about active agent jobs and stuff like that. So, like, if you’re, like, come in and you’re, like, why is CPU crazy? You can come in here and look. You can say, oh, well, this, you know, this stupid index maintenance job that the dummy DBAs won’t get rid of is screwing things up. So, I’m going to start backwards a little bit here.

System events. Again, this is all the system health stuff. And, again, you don’t want to see things in here. Like, I have some stuff in the severe errors tab because, like, you know, it’s killing queries at some point. But all the other stuff on this server, at least, is pretty clean because I’m not, like, I just not collect, like, there’s just nothing to collect in here.

The locking and blocking tabs. This will talk about, I mean, I don’t have anything in the last 24 hours, but I do have some lock waits. I just didn’t have any blocking.

Right? So, like, I don’t, like, there’s nothing showing up in here, which is fine. That’s okay for me. But down in here, I’ll get these populated when I’m going in closer on those. But then, like, this will be the deadlock XML report.

This will be the block process report. And this is just sort of blocking trends where there’s, like, you know, how many blocking and deadlocking events we had and then, like, the duration of them. Under memory, there’s all sorts of stuff about memory grants, memory clerics, the plan cache.

If there was any memory pressure going on in the server, it would get noted here. And then, under resource metrics, there’s some neat things, right? So, this is, you know, like, CPU, 10 dB, and, you know, like, some perfmon counters in here.

Just to kind of get an idea of, like, server busyness, like, you know, anything weird going on. I got wait stats over here so we can, like, you know, get a good idea of, like, you know, sort of, like, how, like, did anything spike up? Is anything bad?

Like, what should I look at? What, you know, what time frame is, like, would matter to me? You know, like, usual 10 dB stuff, 10 dB usage by different consumers, 10 dB latencies, like, all the file I.O. latency that you’d be used to seeing. You can see, like, the different database files down here and the different log files, different database files here and here.

And, you know, this is just read and write. Under perfmon counters, something that I just added, which I’m very, very proud of, because I am always forgetting exactly which perfmon counters I should be using for different things.

I’ve added these sort of, like, perfmon packs or, like, perfmon templates. So, depending on what you’re looking at, right, you might care about, you know, like, memory pressure, CPU, I.O. pressure, 10 dB, locking and blocking, stuff like that. So, if you want to explore a specific scenario in here, you can change these and it’ll select all of the perfmon counters that, you know, you would normally look at for this without you having to go through a whole list and click all of them.

Because that’s annoying and you’re going to forget and you’re going to miss one or, you know, I don’t know, maybe you do this when you’re drunk and forget stuff. But, you know, there’s just all sorts of things in here that make sense to look at and, you know, just to dig in from a perfmon level. This is on the dev branch now.

This will be out sometime this week. No promises because I’m still kicking stuff around. But the thing that I added in here that I’m real, real proud of is the plan viewer. So, Microsoft released the MS SQL extension for Visual Studio Code, which is MIT licensed.

And it just happened to have, crazily enough, a whole bunch of assets that I could use for graphics and also for, like, you know, making sure the XML is all in there correctly. Right? Like, the XML is all documented.

I can make sure that what I get from the XML is exactly what Management Studio would get from the XML. And I can add my own stuff to it. I am a hog in heaven because all the, like, you know, plan analysis and indicators and stuff that I would want to see when I’m looking at a query plan, I can put into these query plans. Right?

So, check this out. Right? So, like, if you right click on any one of these rows and you say get actual plan, you’ll get a little warning. It says you’re going to execute this query. Now, this does something that I always loved a lot from SQL Sentry, which was you can execute a plan.

It’ll throw away the results, but it’ll give you the actual execution plan. If we zoom in a bit, you’re going to see that it has all the execution numbers. I break things out a little bit further.

So, you get the duration number that you’re used to in SSMS, but you also get the CPU numbers, right? So, if we look over here a little bit, we can see, like, this was 633 milliseconds of wall clock time, but 3.366 seconds of CPU time. Now, if you’re looking at the little yellow bubbles up there, those are parallelism indicators.

I chose not to put those over the icons for one reason. If we have multiple things on a plan that I want to show you, like, down here is a good example, right? We have, sorry, the zoom is a little slow on me.

We have a warning, and we have the parallelism indicators. You can see them both, right? So, it’s not like, oh, was that parallel or not? Is it just a warning? What happened?

Right? And if, like, but, you know, this plan is kind of boring, right? Now, we have, like, a, you know, missing index suggestions up here. We can actually see both of them, right? Isn’t that crazy, right? You can see all the missing index suggestions.

It’s wild. But the real thing that I wanted to show you is in this plan, right? You know, this thing runs for about 20 seconds. So, I didn’t want to sit there and make you wait for 20 seconds. But this has two things in it that I have been dying for SSMS to have that Microsoft just wouldn’t give you, right?

And that is, if we zoom in a little bit, right? Let’s get this over here so it’s sort of a little bit more out in the open. We have an eager index spool.

And if you hover over the eager index spool, it has the index that you should create to get rid of the eager index spool, right? So, like, there’s a warning here that’s like, yo, SQL Server is creating an index for you. Here’s the index it’s creating.

If you go create that, this thing goes away, right? And then over here on the clustered index scan, because something I always talk about when I’m talking about eager index spools and parallel execution plans, is that they make, is that like when the spool is getting built from the clustered index scan, SQL Server only uses one thread to do it.

So, when you get an actual execution plan and you have skewed rows on parallel threads, I can warn you about that, right? Thread 5, process 100% of the rows, right? This is fantastic.

The amount of stuff that I can put into this to make your lives better and easier is going to blow your mind. I have way more stuff lined up. So, you know, keep an eye out and all that. But otherwise, these query tabs, you know, they kind of give you the normal set of information that you’d want to get.

You know, like all this, like, you know, sort of like resource uses for the queries, the query text. You know, if you want to just say view an estimated plan for something, you don’t have to get the actual plan. You just click on view plan.

You get all that. You can save the plan if you want, right? Like all this stuff that you can, like, you know, normally do with SSMS. And this should be a pretty SSMS-like experience, right? At least from viewing it, like the perspective of viewing an execution plan.

Another thing that I like that I could put in here was warnings on filters, right? And, you know, stuff like, you know, like filter up, like just warning you, like, hey, if there’s a filter, you’re sometimes leaving this, like, you know, like, granted, this is like a query store query. So what am I going to do?

But like I can warn you, like, hey, you know, that filter operator that discards rows, I have to like way later than you maybe want to. So I can like bring, I can point that stuff out. So this is just a small, like a, like intro to some of the stuff that I’m building into this. Again, it’s totally free, totally open source.

I have received one single $25 contribution from some nice person who was like, wow, you’re doing a lot of hard work on this on GitHub. So thank you, JiHuan. I apologize if I said your name wrong.

If you’re out there listening, you can give me a phonetic typing of it somewhere in a comment, maybe. But anyway, I’m getting pretty happy with this, where it’s going. Again, I’ve got a lot of other stuff lined up that I’m not quite ready to talk about yet, but soon enough, soon enough, soon enough.

Anyway, again, if you want to check this out, it’s at code.erikdarling.com. You can download it from the releases section. It’s where all the zips are.

If you download a zip and there’s no EXE in it, sometimes that’s Windows blocking stuff. You might have to unblock that, but it should all be pretty easy and straightforward once you go through the readme, which I have carefully crafted to make your life much, much easier.

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 we will talk some more about this wonderful, free SQL Server performance monitoring tool. All right.

Thank you for watching.

 

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.

The post Free SQL Server Performance Monitoring: Dashboard Overview: Server Health, Query Plans, and More! appeared first on Darling Data.