Free SQL Server Performance Monitoring: Full Dashboard Installation

Summary

In this video, I delve into the installation process of my free SQL Server performance monitoring tool, addressing a common issue where users have questions about setup despite having access to detailed documentation. I explain that there are two main installers: one for command line use and another with a graphical user interface (GUI). The command line installer offers various options such as reinstalling the database, choosing encryption levels, managing server certificates, and handling password input in different ways. Additionally, I cover how the tool runs through 52 installation scripts, including community dependencies like SP WhoIsActive and my own scripts for data collection and analysis.

Chapters

Full Transcript

Erik Monitoring Tool Mogul Darling here, back to talk to you about, well, my free tool that monitors your SQL Server’s performance. And we’re going to talk a little bit today about installation because I’ve actually moved this up in the lineup because I have gotten a bevy of questions. I have a readme file that documents all this stuff very nicely, but I keep getting questions about installation. things. People are, like, impervious to reading, right? Not even comprehension just won’t do it, right? So here’s a video where we’re going to talk about installation options. So there are two installers. There’s a command line installer and there’s a GUI installer. That’s a graphical user interface. The command line installer has options where you can choose what you want to do. Like you can reinstall, which will drop your old database and make a new one. You can choose your encryption level, right? Which that’s optional, mandatory, strict. You can choose to trust or not trust the server certificate. And you can choose how to hang, how to put in your password. There is an environment variable that you can use if you’re uncomfortable sending your password across a connection like this. You can also use Windows authentication. There are many wonderful things that you can do with this.

I promise you. I promise you. I promise you. I promise you. It’s there. Wait. Those words just waiting to be read. But the performance monitor database runs through, well, 52 install scripts and then some extras, right? So I’ll show you what that means in a minute. It will also go and grab some community dependencies, right? So, you know, like I wanted to make sure that this was a very community oriented tool and that people would get some behaviors and some results back that look like what they might find in community tools with a whole lot of pretty like graphs and charts on top of them. All of these collectors run off agent jobs. The collection job runs every minute. Not every collector runs every minute, but the like some of them do, some of them don’t. But the collector agent job runs every minute, goes through and figures out which like what schedule each collector is on. And then if that schedule meets the criteria, then it will run them.

There’s a retention job that will run at 2 a.m. And look for old data to get rid of. And then there is a third agent job that looks for the monitoring tools agent job being stuck. And if it’s stuck, it’ll kill it and say you did a bad job. You have to start over again, right? So that’s how that works. Behind the scenes, we create a performance monitor database. That’s where all the tables and views live. This is again for the full dashboard. The light dashboard is very different. We talked about that in the previous video.

And then all the collector store procedures that go through and look at your metrics and job monitoring, those will all get installed. There’s a there’s a neat store procedure that calculates delta frameworks, right? So we need to like, like, like, like charting raw data is kind of weird. So we do all this. We I do all this in my head. It’s like, what’s the delta?

There’s a store procedure that does all the delta calculations for things that we want to graph so that we can we get an accurate view of what change so that the spikes in the graph makes sense. Again, the community dependencies we SP who is active goes in there all the the SP blitzes all get installed for you and all of my scripts. And this is just in case you want to use them. The performance monitor does use some of them for some stuff like SP who is active goes for the active queries.

Of course, SP blitz lock, which I wrote most of at this point that shreds the the deadlock XML and then my store procedures, human events block viewer and health parser also do some work in there. Again, we have the three agent jobs responsible for collection, data retention and looking to make sure that those jobs don’t get stuck. There’s a bunch of like metadata tables that keep track of like installation history and stuff in case you’re on the upgrade path and then but 55 scripts in total get executed.

But there are three of them that are sort of not involved with that. But let’s take a quick look at what the installer looks like. So this was this is this is an installer run that I did where it just completed.

It’s not fun to watch like, you know, a log fill up live, but I can I can hit install again and we can we can watch it go. But this was SQL 2016. I keep instances going back for all the supported versions so that I can test stuff and make sure it works.

And on this main screen is where you put in the server you want to connect to you want you choose between Windows and SQL off. And if you, you know, I use SQL Server authentication because I’m lazy and I have VMs. And this is also where you would use the SSMS like settings about encryption and trusting the server certificate.

So this just like the monitoring dashboards uses Windows Credential Manager. So you are not getting anything like like this is the same connection methodology as SQL Server Management Studio. If you don’t trust SQL Server Management Studio, fine.

But if you do, if you like, OK, SQL Server Management Studio, you can connect to my SQL Server. This uses the same thing behind it. So there’s nothing weird.

There’s a big warning down here, right? The big warning says if you perform a clean install, we are going to drop the performance monitor database and start fresh. There are a variety of reasons you may want to do this.

If you’re on the upgrade path, you probably don’t want to do this. But there might be a reason for you to, right? There’s all sorts of things that you might don’t want to say, let’s just start this over with. So there’s that.

There’s a somewhat newer option to reset the collection schedule to recommended defaults. The reason why this is here is because as I’m working with the monitoring tools and I’m kind of tuning and tweaking things, I might change the collection schedule now and then when I find that certain things work better at certain cadences.

And so if you want to use the cadence that I recommend, you can hit that button and you’ll get the schedule that I recommend. You are free to not check that button and keep your schedule. I am totally cool with whatever you do with it.

And then there’s another checkbox here to run some validation tests. The validation tests basically just run all of the stored procedures in there a first time to make sure that everything runs without error. Because if you run into something there, then you’re going to run into that probably every single time the collector runs and you don’t want to do that.

So you want to make, if there are any errors, report them on GitHub. Let me know. I will do my best to fix them. But down in here is where we sort of walk through everything getting created and all these things happening.

And at the very bottom, like this is like, again, just, you know, installing up, like installing all the stored procedures. Down here is where we start installing the community dependencies. Right. So everything that, you know, who is active, my darling data, and then the first responder kit scripts.

And then this is where we execute the validation thing. And if anything weird happens in here, well, let me know. I will do my best to fix it.

And then, of course, you get, you know, all of this sort of, you know, stuff in here. Right. So if anything happens, it tells you where the log is. So if you get an error and you want to report it to me on GitHub, you can include these helpful details.

I would love it. Like, you know, more details are better. So put everything you can in there.

You can maybe leave out like your, you know, computer name, like whatever, whatever this is. But, you know, everything else in there is good. And then you will get sort of like an installation summary over in the corner that I’m helpfully standing in front of.

There are other buttons. There’s the, oh, I got to move this way. There’s the install button.

There’s the troubleshoot button. And there’s another button that says view report, which will bring up a text file with all of your log information in it so that you can share it with someone who might care to troubleshoot it like me. There’s also a close button over under this armpit.

Hold on. This armpit where it says I’m never going to get this mirror camera thing right where you can close things. And that’s how you exit the installer. So that’s pretty much it there.

Again, all of these options are available in the command line as well. And I highly recommend that you read the documentation about these things before emailing me the same question over and over and over. All right.

Thank you for watching. And I’ll see you in tomorrow’s video where we’re going to do a bit of a deeper dive into some of the charts and graphs in our wonderful free open source SQL Server 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: Full Dashboard Installation appeared first on Darling Data.