- Recording Date: November 6, 2024
- Topic: New features released for Database Health Monitor
- Host: Shannon Lindsay with guests Mitchell Glasscock and George Stedman
Stedman SQL Podcast Episode 6
In episode six of the Stedman SQL podcast, Shannon Lindsay hosts George and Mitch to discuss new features in the database health monitor. They introduce a Quick Scan check for unscheduled SQL Server Agent jobs, which is particularly useful in cloud environments like RDS and Azure. Two major additions in release 1074 include a performance history instance report and a database-level file size over time report. The performance history report offers detailed charts for monitoring SQL Server performance metrics, while the file size report helps predict storage demands by tracking database growth. Additionally, color blind mode has been implemented, and real-time blocking and active queries charts are now available for each database. Future plans include line charts and a VLS page.
Watch / Listen Now
Podcast Transcript
Shannon Lindsay 0:18 Hello and welcome to the Stedman SQL podcast episode six. Today, we have George and Mitch with us, and we are going to cover some of the new features and releases things they’ve been working on for database health monitor. So like I said, this is episode six. I’m Shannon. I am Steve stedman’s Assistant, and some places you can kind of watch the podcast episode. We link it live every week to YouTube @SteveStedman, and then we are still in the process of finding the best kind of podcast landing streaming place we want to put it. I didn’t have any exciting new Stedman news this week, but yeah, let’s jump into it. So like I said, we have George and Mitch with us today. Hello gentlemen, thank you for joining.
Mitchell Glasscock 1:24 Thanks for Thanks for having us.
George Stedman 1:26 Yeah, thank you.
Shannon Lindsay 1:29 All right, what has been you mentioned there was a new Quick Scan check you tell me a little bit about what that covers?
Mitchell Glasscock 1:39 yeah. So we, we both came up with a new Quick Scan check. Basically, it just checks if you’ve made a SQL Server Agent job to run, but when you were making it, you might have forgotten to schedule it. So we’ll go back there and it checks to make sure that it is scheduled. And then if it’s not, it will notify you in the quick scan report. Yeah, that one’s, I mean, a lot of we got that one. We got the idea for it because we’ve had a few clients where we work with them, and, oh, we see that certain jobs aren’t being run when we do our other reports. And then we notice, well, we thought we scheduled them. And then some some environments can cause them to either get on disabled, like I think RDS or Azure can have some extra jobs and some stuff that runs in the background that can sometimes disable stuff, but it’s not really clear when stuff gets disabled, so just having an extra reminder that says, hey, these cleanup jobs aren’t running can be really helpful for keeping stuff nice and clean and tidy.
Shannon Lindsay 2:46 All right, what you said there was one other thing that you had worked on. What exactly did that entail?
Mitchell Glasscock 2:52 So we have two pretty big additions to this latest release, 1074, so we added a new instance reports, which is performance history. And then we also added a new database level report, which is file size over time, where you can monitor file growth, backup growth, all that file stuff. And then if we want to pull up the stream, we can start showing that off. Yeah, alrighty. So we’ll start with performance history. This is just a instance report, so we’ll jump straight into that. And this has a lot of good charts that are added now. So this is all historic monitoring, so you will need to have your historic monitoring enabled on your database to be able to access this instance report. But each of these charts shows a ton of helpful information over time where you can change the time frame and the granularity to look at it. But all these charts have tons of helpful information for looking at performance monitoring. And then, do we want to kind of dive into each one of these, or what we’ll do? Yeah, great. And then George, feel free to jump in at any point if you want to get into an explanation. So top one batch requests. This is just how SQL batches up queries and sends them all off to do these, to do all of them at once, you should be looking for a pretty average batch request, anything like this, where you’re getting a bunch of batch requests. It’s abnormalities in this chart. So this, I had a bunch of queries running at the same time, so I had a bunch going off here. Cache hit ratio, that’s going to be where it’s pulling out of memory. So you want to make sure that you’re pulling most of that out of the cache and not relying on other resources. Again, this is just another one you should be monitoring and making sure that it’s pretty average across all the time. Um memory grants pending. This is when two, two things, two or more things, are trying to take up too much memory resources. And if you have any of these, this might indicate for resource allocation. So definitely keep an eye on this one, deadlocks. If you have deadlocks, it definitely needs investigating at that point. So this is not just if you see something investigated on that page, lookups, reads and writes are all pretty explanatory. Just depends on your server workload, how much you’re putting in, how much you’re pulling. So these should all be pretty average. If you’re putting in more, sorry your your reads and writes might be a little higher at certain points, compilations and re compilations. So SQL Server compiles all its requests. This is another one where just checking for averages is pretty good. You want to make sure that it’s not getting too out of hand. But if your your compilations look fine, but your RE compilations start shooting up, that’s when you need to start investigating more for that kind of stuff. Then finally, we have target server memory and total server memory. This is again, another resource allocation chart, these should both be pretty average in a production database, or they should both equal out to each other. If your total server memory is getting any large spikes like I had here, you need to look into your resource allocation and make sure that it’s all configured properly, and then transactions is just how many transactions are going through a second. And so one other thing that we added here, this is kind of just a cool feature that we’re going to start implementing everywhere else, but you have the ability to collapse charts that you might not want to monitor all the time, and this will stay like that between sessions. So you can collapse charts moving up and down depending on what you want to monitor more frequently or what you want to see at the top when you open it. So anytime you collapse or move a chart, it will stay in that spot once you leave. And then George added the file size over time.
George Stedman 7:17 yeah, the it’s kind of similar to the the one Mitch that instance report, but it’s a per database file report, so you can see how each database is growing over time. At the top, you can see there’s also the time frame and granularity sliders. So you can look at like last week, you can look at last month, you can look all time year, and just kind of see how you’re growing, and maybe, like, you can use that to help predict where your databases might grow in the future, and like, what kind of storage demands you might have. Just by looking at the curve we’re currently, I guess, talking about that’s we use the same bar chart we did in the other ones. We’re currently also working on a line, line chart version of it, oh, which has it pull that one up and to kind of help, it helps visualize the data a little bit better than the the bar charts do, yeah.
Mitchell Glasscock 8:17 So this is just one of the sneak peeks that we’re doing, you’ll probably see this on the next release. Yeah. And we might add some more customization and use that chart elsewhere in in database, in database health, as it makes sense. But we also might have a slider radio button to help to choose between them, depending on because different people see like different charts better. It’s you can have a lot of say on how you see your data. Yep, let’s see here. So one of the other features that we added in this last release, one of the big overhauls we did in our previous release, was dark mode. So our focus for this last one was actually color blind mode, which should make a lot of the charts a little bit more friendly to individuals that do suffer from any type of color blindness. So we went through, did a little research on the best way to implement this, and this is currently what we’ve landed on. Anybody that has any recommendations or any slight color tweaks that can be made. We’re definitely open to listening to those in the future.
Shannon Lindsay 9:30 Yeah, and feel free to email me, [email protected] and I will happily pass along the info.
Mitchell Glasscock 9:40 Yeah, so just anything that makes our program a little bit more accessible to anybody.
Shannon Lindsay 9:46 Yeah, that’s awesome.
Mitchell Glasscock 9:49 And then one last little feature that we threw in there, so we’ve had the blocking and active queries charts, the real time charts. Is added to the server overviews, but we have now also added each of those to the real time overview by databases. So if you’re trying to find some more information about each database that you’re looking at, you can definitely look at the real time blocking and active queries for each of those databases. And
Shannon Lindsay 10:22 you had mentioned that there was a couple new things that you had started looking into working on
Mitchell Glasscock 10:29 so that first one, the the line charts we’re looking at, getting those rolled out for the next release, a little bit more, getting them more implemented in more areas, areas that make sense, like or George said. And then another page that we’re adding is the VLS page, which shows you virtual log files. It provides a visual visualization of the virtual log files that are used in the log files between backups, and then the ability to shrink and expand those files as needed.
Shannon Lindsay 11:09 Well forward to hearing how those advance and seeing the progress that comes with that.
Mitchell Glasscock 11:17 Yeah, so this is just a work in progress right now, but we should be seeing this rolled out by the time we do our next release.
Shannon Lindsay 11:24 That was short and sweet, but lot of information covered. Good work, gentlemen.
Mitchell Glasscock 11:34 we had two, two big pages to roll out, so that was our big focus for this last release of good Yeah, even if they were quick to go over, it took a bit to get them put up there, right. And they, they both have a lot of good information to look through, lot of lot of use cases for those, both of those pages, yeah, and, but both those pages we’ve even, we’ve already used with some of our clients to show differences of our own performance tuning like before and after, especially the performance ones for the page rights and reads we saw, we’re able to see and visualize the huge difference that we did with one of our clients, and then some of the other like The File time or file size over time. I’ve been using that one to look at some databases for another client to see kind of how they’re growing and what they might need to do in the future.
Shannon Lindsay 12:31 Usually at this point, we do, you know, kind of a, Q, a, and I haven’t seen anything come over, but I will. Usually there’s a little bit of a delay between the comment section and when we receive it. But like I said earlier, some places we can watch, usually we do the live stream and it’ll go to YouTube. @SteveStedman. And then another place you can watch is on our Stedman solutions web page. Stedmansolutions.com there is a tab that you can click. Scroll down. It says Stedman SQL podcast. And each week I go through and update that page and put in current episode at the top links to the other episodes. And we we keep that regularly updated, and it’ll also go to the Steve’s YouTube channel after I go through and edit and, you know, take out all the ums and such, and that’ll be probably a couple days, but it’s always available there later. Yeah, I haven’t seen any comments come through, but next week we will be covering we have a guest appearance from our first partner appearance from our partner program. Brandy from rock it will be joining us. So look forward to that email coming through, and that should be an exciting episode to have her join us. So thank you again for appearing with us. We appreciate hearing the updates and having you guys come join us and let us know what you’ve been working on,
Mitchell Glasscock 14:28 for sure. Thanks for having us. Thanks for having me.
Shannon Lindsay 14:32 All right. Well, have a good week, everybody, and we will see you next week.
Mitchell Glasscock 14:38 Thank you.
14:57 thanks for watching our video. I’m Steve. And I hope you’ve enjoyed this, please click the thumbs up if you liked it. And if you want more information, more videos like this, click the subscribe button and hit the bell icon so that you can get notified of future videos that we create.