- Host: Steve Stedman
- Guests: George Stedman and Mitchel Glasscock
- Recording Date: December 11, 2024
- Topic: New features released for Database Health Monitor
Stedman SQL Podcast Episode 11 – New Features
Steve Stedman hosts the Stedman SQL podcast, featuring George and Mitchell, developers of the Database Health Monitor. They discuss new features in DHM, including the VLF visualization report, schema search, SQL CPU schedulers, deprecated features, historic file size over time, and average page life expectancy charts. Mitchell highlights the flexibility of the new instance page layout and the upcoming disk latency report. George mentions the rewritten schema drift tool for comparing database schemas. Steve encourages feedback for new features and previews the next episode on SQL join types. The podcast is available on Spotify and YouTube.
Podcast Transcript
Steve Stedman 00:00 All right, welcome everyone to this week’s podcast. I’m Steve Stedman, and I’m the host of the Stedman SQL podcast. This is episode number 11 this week. I’m joined by George and Mitchell, two of our developers on the Database Health Monitor project. We’ll get to that in just a second.
Right now, the podcast is available on both Spotify and YouTube, and we’re about to finish we have this episode and one more episode to finish out the year 2024 or season one, and then 2025 will be going into Season Two of our podcast. At that point, some news from Stedman Solutions, Database Health Monitor, released last week with some great new features. And part of today’s podcast is we’re going to talk about some of those cool new features, including the VLF visualization report, schema, search, several other new reports as well.
Also, Mitchell has joined the company as full time as a full time employee. He’s been working part time while he finished school, and he’s done with school, so he’s now a full time employee, with his main focus being Database Health Monitor development and then working with clients also.
Do you want to be a guest on our podcast? Do you have some SQL Server topic that you want to share with our listeners? Just visit our podcast page at stedman.us/podcast, and click on the podcast guest page. There’s links there that to schedule for one of our standard recording times with always looking for guests to have on the podcast.
All right. That brings us to this week’s topic, which is new Database Health Monitor features. Welcome George and Mitchell also. Oh, hey guys. Also, should point out that we’re having a technical difficulty with Mitch. Is video right now, so we don’t get to see Mitch face today.
Mitchell Glasscock 02:12 Apologies for that, but we’ll make it through.
Steve Stedman 02:16 All right. So some exciting new features that just released with Database Health Monitor, if you want to tell us about those.
Mitchell Glasscock 02:22 So we rolled out, what is it? We’re on version 1082, now. Whole bunch of updates. We included a whole bunch of new pages in this that we’re hoping that people find very useful from here on forward.
Steve Stedman 02:37 All right, I’m going to jump in. I’m sorry I asked you about the features, but I realized I totally forgot to tell people what Database Health Monitor is, and I should probably do that before we start talking about new features. So Database Health Monitor is our SQL server monitoring solution. It’s a SQL server monitoring tool that been working on for, gosh, 1314, years, something like that now, and it’s part of the core offering that we have with Stedman solutions, and it’s the tool that we make available for all of our clients in order to be able to monitor and track and follow what’s going on with their SQL servers. So back to you, then Mitch on to the features.
Mitchell Glasscock 03:17 We’ve got some big ones this go around. One of our big ones is the visual representation of log files that’s going to be a server tree page called VLFs.
Steve Stedman 03:27 So first off, VLFs are virtual log files. And all of your SQL server log files, not your log backups, but your actual LDF files, are made up of smaller internal chunks called virtual log files, or VLFs. So what this tool does, it gives the option to be able to go in and see what the structure of those is, and see what you’re in use and which are available, and then to give you some cool abilities to shrink or expand the file. So here’s an example. Go ahead and you want to talk about what’s going on with this one. Mitchell, right?
Mitchell Glasscock 03:59 So this is just a really small database. Just have a whole lot of backups on there. There’s not a whole lot of use getting on so as you saw at the beginning, there’s there were only two virtual log files, both really small. So what SQL Server does is, when you’re doing your log backups, it creates these virtual log files where it stores the information, and it sequentially rolls through them, puts them in use, and then once it’s gone through every single one of them, it will go back to the beginning and start using them again. So when you’re under a heavy transaction load in your SQL Server, sometimes that the log file can get really bloated with these VLFs. And so what this does is it allows you to easily go in and visualize how many VLS you have, like your log files gotten bloated over time, or you had a large transaction, and the log file grew unexpectedly. You can go in here and see if you can try. Off some of that so that slides, yep.
Steve Stedman 05:03 So some of the things that would cause that log file to bloat or grow out over time could be if you’re in a full recovery model database, all of your transactions are stored in this LDF file until your transaction log backups run, and if something happens and your transaction log backups aren’t running for a little while, the file can grow quite a bit, and then you need to come in and clean it up once in a while. The other thing is, if there’s something that’s holding a transaction open for a really long time, really big transactions can cause it to float. And hopefully that’s the kind of thing that if you see that happening, you can figure out what those big transactions are and clean it up a little bit. Do you have an example of one we can click on there. Mitchell, that has more VLFs showing, yeah,
Mitchell Glasscock 05:46 Let me look in here. I don’t have any personally.
Steve Stedman 05:52 I think on MySQL, SQL 2022, there is. There’s a bunch of databases there, but I think bad VLFs is one of the test databases. There we go. And actually, yeah, there we go.
Mitchell Glasscock 06:08 So we can see that this has a bunch of VLFs, and this is set up to grow percentage wise, which is a configuration that you probably want to avoid. You can see that they’re growing sequentially bigger and bigger. But we created this tool so that you can actually shrink your VLFs in database health monitor. So you don’t have to take a script and run it in SQL Server itself or in Management Studio itself. You can simply just choose the size that you want to shrink it down to, and this will auto select the smallest size possible based on what is last in use. And you can simply just hit shrink file. It’ll execute and it’ll cut the log file down to size.
Steve Stedman 06:52 One of the things generally it’s a bad idea to be regularly shrinking your log files, because then they have to grow out throughout the day or the week and over time. So one of the things that we’ll do, if we see that there’s a log file with a lot of VLFs in it, and usually our threshold is around 250, to 300 or so, if there’s 1000s of them, 1000s of tiny VLFs, one of the things we’d like to do is to shrink the log file down to the smallest that we possibly can, and then expand it back out to a larger to the larger size. It’s big enough to handle the typical log file needs, but we end up then with a lot of large chunks or large VLFs in the file, instead of just a bunch of tiny ones. So if you expand that file, so what we’ll do is oftentimes shrink it down as small as we can. And if the last file is in use, you might have to wait till tomorrow or until your next backups run, or wait till a later time in order to be able to shrink to a smaller part of the file. And then once we shrink it, we’ll expand it back out to the size that it regularly needs to run. And there we go. So when you added 250 it added what looks like six or eight log files there that were all similar sized. And instead of a bunch of little tiny log files like we had there previously.
Mitchell Glasscock 08:06 So that was the overview of VLS. Actually, one other thing is that if you don’t want to use the shrink or expand inside database health, we also have the option that you can script based on your shrink and expand, and you can take this into SQL Server Management Studio and run it yourself.
Steve Stedman 08:21 Yep. And you know, what’s interesting on this is in the three or four week period from when you’d completed the code on this report until we actually had it deployed, I was probably on three or four different client calls where I wish I would have had that feature to work with them, where we had to go through and manually do it the old way, create scripts and manually go through and try and figure out what that log file looks like, right?
Mitchell Glasscock 08:44 We’re hoping to get a whole lot of use out of this one going forward. All right,
Steve Stedman 08:48 So what’s the next one?
Mitchell Glasscock 08:52 All right. Next up, we have schema search. This is a pretty cool one. So the overview, it’ll allow you to cross database, examine your schema comparisons, you can pretty easily locate schema just by a simple search function, and this should help streamline and troubleshoot the understanding of the schema structure.
Steve Stedman 09:12 So when you say schema search, what kind of things are we able to search through?
Mitchell Glasscock 09:18 So the database has tons of different schema within it. Let’s see table names that’s that might not be correct.
Steve Stedman 09:26 So we can, well, I know we can search the entire structure of a table, all of the client names. We can search indexes. We can search views, procedures, functions, all those kind of things, right? Yep, okay, look at how that works.
Mitchell Glasscock 09:45 We’ll just go into mine real quick. And so schema search is a instance report. So whenever you click into an instance, you’ll be able to find it right there. And then anyone have a schema that I should search?
Steve Stedman 10:00 Try searching on text.
Mitchell Glasscock 10:03 Text, all right, and we’ll pull up 100 results real quick.
Steve Stedman 10:07 So as that bar is going across there, it’s doing different searches. It’s searching all the different object types, triggers, views, tables, procedures, all those kind of things. And who knows, we may add more over time, but does a pretty exhaustive search. George, while we’re here, do you want to share a story of a client we were working with that we had to find some deprecated features of everywhere they were using the text data type, which was deprecated in SQL Server 2005.
George Stedman 10:32 Well, that’s going to lead into one of our next database health features, but as of we used it to find approximately, like, 800 uses of the deprecated text data type inside of their multiple databases, and just being able to find that quickly, or, like, find where more where to look using schema search saved a lot of time.
Steve Stedman 11:00 Yep. So this is one of those things that, let’s say you’ve got a column you’re going to change, and let’s say you that you’ve got some column named, I don’t know, job description, or something like that, and you want to go through and find all the places that that column is referenced from in your store, procedures and functions and things. Let’s say you’re going to try and go through and find all those things that match a certain criteria. It’s a great way to be able to search all of those database schema objects and find where that’s being referenced, right?
Mitchell Glasscock 11:27 And then within schema search, you can pull up, if you find one that you need to look at, you can pull it up and use the Query advisor to look at this and then take it into management studio.
Steve Stedman 11:41 Yep, yep. This is one of those things that I’ve had a T SQL script that I’ve kind of been using myself for the last 15 years to search the schema, and it’s kind of flaky. And I was really glad that we were able to build this in as a full featured part of database auth monitor our next one.
Mitchell Glasscock 11:56 We have another new instance report the SQL CPU schedulers. So this will help you monitor CPU usage across your instance, and should help provide some more detailed insight into the schedule or utilization, yep. So this is a quick one that should just pull up your thread usage within the Management Studio and show you information about that.
Steve Stedman 12:23 So if these are showing up with runnable tasks and the current workers as being low numbers, that’s a good thing. But if we start, if it’s showing that we have a runnable task that’s numbers greater than zero or greater than one, then that can be an indication there might be some squeeze on your CPU, on this server. So it’s one of those that the runnable task count. If we see that going too high, if you see it like above four or five or 10, that’s an indication that your server may be way overloaded on CPU load. So it’s a great way of somebody saying, gee, it feels like the server is overloaded on CPU to go and see is it. And this one doesn’t really look like it is. But then again, this is your desktop computer, and it’s not, it’s not doing a lot compared to most SQL servers.
Mitchell Glasscock 13:09 So it’s not got a lot on the background. But if you do have that, if you’re thinking that your CPU might be bottlenecking your system, this is a great way to look into that, especially when it’s under load, or to prove or disprove whether it’s under load, right?
Steve Stedman 13:22 Whether that’s the bottleneck.
Mitchell Glasscock 13:31 Another new instance report, deprecated features, George.
George Stedman 13:36 That’s the one we hinted at earlier. Yeah. Deprecated features is is a really fun report, because it just shows you everything that Microsoft has put on a list of they may or may not support it in the near future.
Steve Stedman 13:52 That’s an interesting way of saying it, because, I mean, if you just heard deprecated features, you really didn’t know what that meant. You might think, well, these are features that don’t work in SQL Server the current version, but the fact is, they work. It’s just that Microsoft has announced that they’re going away, and some of them might not work as optimally as the alternative.
George Stedman 14:14 Some of them are deprecated, and they don’t necessarily have a replacement for it, or they don’t intend on having a replacement for it. So why don’t a lot of things that you may need to do if you have deprecated features in your database?
Steve Stedman 14:29 Yep. Now in kind of the old school world where you would have a SQL Server running, and you might be running the same version of SQL Server for 15 years and never doing updates, it’s not suggested, but a lot of people end up running that way. Deprecated features might not be as big of a deal because you’re running that same version of SQL Server, but if you’re always going to the latest version of SQL Server, or you’re running a cloud based SQL server like Azure or RDS or something like that, then those deprecated features might sneak up on you and disappear on that next version. And you know, not knowing.
George Stedman 15:00 Another thing is, a lot of even with a brand new like 2022, SQL version, a fresh install will have some deprecated features used right out of the box.
Steve Stedman 15:12 This is catching when they’re being run. It doesn’t necessarily mean it’s being run by your code. It might be being run by SQL Server itself internally.
George Stedman 15:19 So yeah. This this report came with a new chart type as well, instead of Database Health Monitor that you may be seeing to help visualize like how big or how much something’s being used over the other ones. So you can see here that sis users, the deprecated feature is being used more, and although you have the usage numbers down below there, the visualization can really help, kind of see, like, what’s really spiraling out of control. What are you using too much of when you really shouldn’t. And typically, the ones that are really spiraling out of control are the ones the user is using, not the one the actual not the features that SQL Server itself is using it’s so we’ve seen, we’ve already used this on a couple of clients, and seen some of the deprecated features that have really just, it’s almost as if their data, their databases, were built on it. As they upgrade SQL Server, they didn’t replace them, or didn’t, may not be able to replace them even.
Steve Stedman 16:21 Well, I do want to say that over time, we’ve really overused the pie chart, or the half pie chart, as we have in database health monitor. And I want to thank you on this one, George, because I’m not looking at another pie chart. This is a better way to represent some of the data. So hopefully we’ll see some more of our other pie charts being replaced with vendor formatted charts in the near future, too. Alrighty.
Mitchell Glasscock 16:47 Another one, the historic report file size over time. This one actually just got a big update in this last release. George, right, yeah.
George Stedman 16:55 This is another one where we added a new chart type. We were sick of seeing all the bar charts all over database health monitors, so we upgraded it to a line graph to better kind of show growth and such in certain reports. Yeah.
Steve Stedman 17:12 While you’re bringing up that file size report, let me throw out one thing here, prior to, prior to about a year ago, I was the only developer really working on database health monitor, and it’s gone to show how adding George and Mitchell to be regular developers on the Database Health Monitor project has made such a big impact, because there’s so many more things getting done now. It’s really, really exciting to see.
George Stedman 17:37 Yeah, and so last update, I believe, we added the historic file size over time report. And one of the big changes was we added it was kind of weird to see it in a bunch of little tiny bars, especially when you were doing lots and lots of data. And so the lines kind of help, kind of connect and show your growth habits of the SQL Server better than the bars do look very nice.
Steve Stedman 18:02 That looks so much better than the previous rendition of it.
George Stedman 18:07 Yeah. And the other thing is the line chart, if there’s missing data, so say, like, for whatever reason, the reporting queries weren’t being run and there’s a giant hole in the middle of your data, your data set, the bar charts would just drop off to nothing in that area. And I believe the lines actually just connect the last point to the next point. So it’s not like he just didn’t have any growth there. And you aren’t unable to visualize it. It’s it’ll kind of fill in the gap, make it up, normalize, yeah. All right. Another good improvement.
Mitchell Glasscock 18:39 And then this one. So we were we released performance history as an instance report in our last major update, but we just added in a new chart, the average page life expectancy chart. So this should just allow you to have more to look at that performance history, more performance trends, and provide you a little bit more insight into what’s happening in your instance.
Steve Stedman 19:05 And I’ll pull that one up real quick.
Mitchell Glasscock 19:12 So we showed off performance history last time. This is just the new chart that we’ve added in here the average page life and expectancy.
Steve Stedman 19:21 So normally, so page life expectancy refers to when a data, page or data in a table that’s represented as a page on disk and loaded into memory is loaded into memory. How long that’s going to stay in memory before it gets pushed out? Now keep in mind, we’re looking at Mitchell’s SQL server here, I think, can we go look at the SQL 2022, one, and compare that. So on a on a well behaving SQL Server, we should see the page life expectancy going up over time, if it has plenty of memory, if it’s squeezed on memory, yeah. So like right here. We’re seeing average page life expectancy just goes up and up and up. That tells me that there’s far there’s more than enough memory on that SQL server so that those pages aren’t getting pushed out of memory. Now, then we hit a point where it drops down to zero and starts growing up and up and up again. What that means is that that was the time that the SQL Server was last restarted and it dropped back down to zero. So if you see the page, life expectancy always growing until your next reboot or restart, that means you’ve got plenty of memory. If you see it always growing until, like the middle of the night, when your indexes or statistics are being rebuilt, that probably says you’ve got plenty of memory to run on a daily basis until you hit those indexes rebuilding, and then that, that kind of squeezes it at that point and forces some of those pages out of memory so other pages can come in cool addition there. Thank you.
Mitchell Glasscock 20:52 Alrighty. And then the Ignore weights advisor enhancements.
Steve Stedman 20:57 Click on the top level instance, and then go click on historic weights up top. That’s right, and you’ve got the others button there, right. Take it from here.
Mitchell Glasscock 21:07 I had worded it differently when I had reported up on that one, all right, so the Ignore weights previously was just typing it in and adding a new weight type that you wanted to to ignore in this chart. Now we have a nice drop down list, and you can see all the weight types, and you can select from there. You can also type in, let’s say I want to go through here and find anything on async.
Steve Stedman 21:39 I want a specific one.
Mitchell Glasscock 21:41 So I’ll do async disk lock, and then I can simply just add that, you know? So it’s a bit of a back end feature, but it’s, it should make everyone’s lives a little bit easier.
Steve Stedman 21:51 Yep, and, you know, I, I know a lot about weights, and I, I know what a lot of those weights are, but I still find myself the old way of doing it, having a lot of typos, and how I tried to enter them in there. So this is one of those that even if you know what the weight type is, this will make it quicker to add them in, to kind of filter them out from your list, if that’s what you’re trying to do.
Mitchell Glasscock 22:13 So right, right? And especially because there’s so many weights too. So instead of trying to figure out which one you really need to add, you can go searching for it a lot easier now.
Steve Stedman 22:22 Yep and every version of SQL Server seems to add a few more. So, all right, that’s just a small enhancement that makes life a little bit easier. One of the things I want to ask you guys about, are there any upcoming features, changes, or things that we’re working on now that you want to talk about?
Mitchell Glasscock 22:43 One of the big things that I’ve been kind of working on in the back end, you’ve, you’ve, you might have seen it in the performance history. You’re able to move those charts around and collapse them. One of the things that we’re starting to work on the back end is the instance page and all the other pages that have kind of a lot of charts that are just built in, they’re the same. They look the same every single time you open it up. The charts themselves may be slightly different, depending on your data, but the the layout is the big thing that we’re working on, being a little bit more flexible one, so that when users open up, if they have a preference for a certain chart and they want to see that one up at the top, that is what we’re working on, so that you’re able to move these charts around and set your preferences or your layouts.
Steve Stedman 23:38 Yep. Awesome.
George Stedman 23:40 Another thing that we have kind of in the hopper. Let’s go with that is during the last update, we added some background tasks for future reporting on disk latency over time. So for all you folks that have spinning disks or even just slow SSDs, disk, the disk latency over time can help you schedule once we get that report working, it should help you figure out when to schedule jobs to like you’ll know when it’s when your display when your discs are being maxed out. And so you can schedule jobs around that, and so you don’t have a bunch of jobs hitting your disc all at once.
Steve Stedman 24:26 Awesome. That’s a good one, because people often ask, well, when should I schedule this big maintenance job that’s going to do indexes and things like that? And the right answer would be, probably, when you’ve got the least IO and the most CPU available, the least IO being used in the most CPU available, and you’ll be able to get all that with that new report. So all right, anything else you guys want to add?
Mitchell Glasscock 24:50 Do we want to speak on schema drift and the work that we’ve been doing on that at all recently?
Steve Stedman 24:55 Oh, yeah, we could do that. So background on schema. Drift. About eight years ago, I built this program called schema drift, which would go out and do basically diffs or comparisons between databases. And then, when I originally built it, it was using the SMO or the Microsoft Management objects that are kind of part of management studio, and they seem to be really flaky or really incompatible between different versions of SQL Server. So it finally got to the point that I stopped shipping schema drift, because it was kind of unstable, because of all these objects I was using to do the diffs and things. So what I ended up doing, with Mitchell’s help, was rewriting, replacing all of those smo objects that were being used with actual T SQL code, where we can go out and do the diffs between tables, between indexes, between procedures and things like that. So it also allows us to do a diff and compare it to a file system and to save those changes out to a file system. So let’s say you’ve got a database, and you want to save it off into source control. Well, you can use schema drift to copy all of your schema onto disk and then check it in with something like Git for source control. It’s a pretty cool thing. We’re still testing it, but we’ll probably be getting the release of that out a schema drift sometime. I don’t know, around the first year, I would think so, right? I’m internally testing it and using it at several different sites that we work on, and seems to be working really well so far. Thanks for bringing that one up.
Mitchell Glasscock 26:34 I just thought about it because that’s we worked on that a lot in conjunction with our schema search. So we’ve been working on that in the back end, and yeah, like Steve said, hoping to see a release in around q1 of next year.
Steve Stedman 26:48 Yep. And what’s really cool with that is that the schema search uses the same underlying core objects that we’ve built to be able to do the comparison, to be able to go get the source of each table, be able to go get the source of each procedure and things like that. So we’re able to be using that same functionality for two different tasks, one for our search, the other to be able to compare schemas so good, reusable code, which will hopefully lead to more, longer term stability of that schema drift project as well. So that’ll be coming. The other thing I wanted to throw out, if anybody’s listening and you’re thinking, Gee, I use database health monitor, but it doesn’t have some feature I like, or it’d be great if it had this. We’ve got time to add features. We’ve got people available with Mitchell and George to be able to go and add those new features into database health monitor. So if you have something that it’s not working right, or something you’d like to see that it does better or differently. Let us know. Just reach out, send an email. You can always email Shannon, my assistant, email Shannon at Stedman solutions.com and she can get the information over to George Mitchell and myself, or you can always email me as well Steve at Stedman solutions.com and if there’s some feature you need we’ll see what we can do to figure out how to get it added into database health monitor. You can see there’s a lot happening now, and it’s really awesome having a couple developers working on it. Okay, anything else on Database Health Monitor before we wrap it up?
Mitchell Glasscock 28:13 I don’t think so. I think that covers a lot of our big topics for this podcast.
Steve Stedman 28:18 Well, George, thanks for joining us. Mitchell, thanks for joining us. Thanks for getting all these features into Database Health Monitor.
Mitchell Glasscock 28:26 Thanks so much.
Steve Stedman 28:29 So join us next week as we talk about SQL Server, join types. Oh, and this is one of my favorite topics. I’ve been presenting on it for around 15 years. I’ve presented it several SQL Saturdays. I presented at a past summit one year on SQL Join types. And what we’re going to do is dive into kind of the overall basics, as well as some in depth around inner join, left, join, right, join, anti join, things like that, and sort of get a good overview on how to do different joins on SQL Server. So tune in next week for that episode, and that will, I believe, be our last episode of the year.
And remember, you can watch this and other episodes on YouTube and on Spotify, and the links are available at stedman.us/podcast and get information on all the episodes there, as well as link to YouTube and Spotify. So thanks for watching. That wraps up this episode. Have a great day. You. Steve, 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. Future videos that we create.