Stedman SQL Podcast Ep. 1 - Database Health Monitor

September 18, 2024

Recoding Date: 9/18/2024
Topic: New features released in September for SQL Server Database Health Monitor
Hosts- Steve Stedman
George Stedman / Mitchell Glasscock

Steve Stedman announced the transition of their live stream to a weekly podcast called the Stedman Sequel Podcast. Shannon will assist with editing and streaming. The team is planning six months of content, including SQL Server training and new features for the Database Health Monitor (DHM). George and Mitchell, developers of DHM, discussed recent updates, such as a consolidated instance report panel, new server overview sections for blocking and active queries, and a new color for stability indicators. They also added checks for large differential backups, update locks, user-defined assemblies, and database snapshots. Future plans include a new color mode, resizable panels, and a file size over time report.

Watch / Listen Now

Podcast Transcript – New Features of Database Health Monitor for Sept. 2024

SQL Podcast Ep. 1

Steve Stedman 0:17
Hey everyone, and welcome to this week’s live stream. We’ve got some good information to cover today, but first, I have some news. We are planning to stop calling it a live stream and turn this into an official weekly podcast, so we’ll call it the Stedman sequel podcast. And what’s going to happen with this is we’re going to end up with a lot more weekly content, rather than just sporadically every once in a while. And with that, we’re going to have Shannon, who’s my assistant, who will be assisting us. We’ll just put her on for a moment here. Hey, Shannon. Shannon’s looks like she’s muted right now, but she’ll be helping with all the editing and the the streaming of this. Hello, everyone. And then with that, we’re working on building out our outline for the next six months worth of content. We’ve got a lot of things planned with this. We’ve got a lot of training content around various SQL Server topics, and then today’s topic is on database health monitor, and the latest release of that and some new features, and as we come out with more features, we’ll cover those in the future as well. And then we also plan to have a featured partner section, where Craig Buckner has been working on our partner relations side and signing up different partners to help with Stedman solutions on Well, it’s a two way street. We help them. They help us. They help us. And we’ll get to a point where we have some featured partners that we can share on information that they do. What’s different this week as well is that we’re using some new streaming software. We’re using a program called stream yard, which is a little bit more focused on how to build podcasts and this kind of content a little bit easier than some of the tools we were using previous previously. Specifically, it’s a lot easier with being able to bring on multiple people on the screen at the same time. We’re going to try and live stream all the episodes that we can going forward, just like we’re doing today, but depending on availability, if it’s a holiday week or someone’s out on vacation, we may record it ahead of time and release it that week. And right now, we’re going to consider this as Episode One, season one of our podcasts. So for this week’s guests, I want to introduce George and Mitchell. We’ll bring them in now. Welcome guys. Hey, yeah,

George Stedman 2:37
thanks for having us.

Steve Stedman 2:39
So George and Mitchell are the developers who work on our database health monitor application. Database health monitor is something that I started 13 years ago, and about the first 12 years I was about the only one working on it, and then these guys got involved, and they’ve been working on it for several months now, and our last update that we just came out with last week was the biggest feature update that we’ve had in database health monitor since probably, gosh, probably the last five or six years. So really appreciate you guys being part of this and being able to help us evolve and grow database health monitor as we move forward. So let’s talk about some of the new features we have this week. I will share your screen here, Mitch and take a look at first. George. Do you want to talk about what we did with the instance report panel? Oh,

George Stedman 3:36
yeah. So I guess it’s a little small on my side, but we, we, we ended up getting too many buttons. There’s too many requests from our team for new, new and more buttons. And unless you had a big monitor, it was impossible see them all. So we consolidated it into a drop down menu on the left, right under the instance Reports button. I guess screen sharings not

Mitchell Glasscock 4:01
okay. Let

me just change it to my full screen share, and that should stop sharing real quick and bring it up. That’s

Steve Stedman 4:14
where I want to say, thanks everyone for being patient with us as we try out this new streaming software we’re learning as we go.

Mitchell Glasscock 4:21
There we go. So it should be my full screen, and that way it’ll pop up with everything, and

Steve Stedman 4:27
there we go. There’s the drop

George Stedman 4:30
down list. You can also search with text if you know exactly what you’re looking for, but every time, it’ll save which pages which instance reports use as you go, and it will throw them in the most used section. There’s also a default install set list of the recommended instance reports for just basic health checkups. That are kind of a good start. Some of the other ones get a little more. In Depth to certain alleyways and such inside of different databases. But pretty much as you use you build your own little library of instance reports and they’ll be accessible.

Mitchell Glasscock 5:13
Yeah. So I’ve been using database health a whole bunch recently, so a lot of the recommended stuff I’ve clicked on it, and this list is a lot bigger when we first start up the program, so once you click on it, it’ll move it over to most used and sort that by how much you use them. But my list is a little short right now, just because I’ve been doing a whole bunch of stuff.

Steve Stedman 5:35
And one of the really cool things that this does is this makes it so that we can add more. We can add four or five or 10 more instance level reports without being constrained by how many of those silly gray buttons will we fit into that space? So there’s more coming there. And in fact, I think we’ve even got one. We’ll talk about in a minute on that. But for now, on the over server overview page, there’s a couple new sections that you added, Mitch. Do you want to talk about those? Yeah,

Mitchell Glasscock 6:03
so I added the blocking queries and the active queries panel. We found that while the reports do tell you what’s going on, you’d have to go and look in the specific instance reports. So we added this new server overview panel so that you can get a constant update on what’s going on if you have any active queries or blocking queries. So I’ll just quick example. Started a whole bunch of blocking in the background, and all of a sudden it’s telling me, hey, you’ve got a bunch of blocking. Click on it, and it’ll drill down into what’s going on in the background and give us live updates.

Steve Stedman 6:46
So one other change that happened around those panels as well. You want to talk a little bit about the color change with the with the addition of blue, yeah.

Mitchell Glasscock 6:55
So I found that when I was using it, the color changes were either red or green, which is great. It tells you if it goes up, goes down. But when it’s red all the way across, you can’t really tell if it stayed the same. So I added in a new extra color, blue. It just represents that nothing has gone on in the background. It’s stayed the same since the last update for that overview panel.

Steve Stedman 7:22
So the blocking queries, from my experience, is probably one of the most detrimental things you can have on your SQL Server. So including this here, so we have much higher level exposure, so people will see them quicker, we’ll hopefully help people find and fix some of those database issues a lot quicker. Okay, then so George, next is the indexes with lob columns report, yes, that’s

George Stedman 7:46
the large object columns. So that’s a new feature requested by someone else on our team. So that added this report, which you don’t have any for see if there’s any, I guess, yeah, we didn’t prep. Oh,

Steve Stedman 8:04
let’s just talk through what it is. Then, since we can’t actually see the results on this, on this test server

George Stedman 8:09
anyway, so it’s looking for indexes that have, like, large, large objects, like varchar Macs and VARCHARs that kind of stuff. And it’s just kind of helps clean up your indexes to make sure that you got nothing like kind of getting out of control.

Steve Stedman 8:29
And the idea behind that is a lot of those bigger varchar columns, people will end up adding them to an index, not knowing how detrimental they may be to the overall performance of that index. And I think that this report goes out and it scans all the databases on the SQL Server. Or you can run it on a database level, and just run it database by database and see which databases or which indexes have those bar charmax and other large objects in them. So that

George Stedman 8:56
one is it’s in the instance reports. But also, if you drill down into the server tree on the left. Mitch, if you want to go down to real time, I think where am I? Click

Steve Stedman 9:09
on any one of the databases. Yeah, there we go. And then click real time and

George Stedman 9:14
plus, and then go down to indexing. It’s the only other one with a plus and that indexing, there we go. And then hit the plus on that one, and so it’ll show up down there in the lob columns. If they are there, five down lb, there we go, yeah.

Steve Stedman 9:33
So there are no lob and lob is stands for large objects, so there’s none of those on this database. But if they were, they would be listed here, and those would be indexes that you would want to go work on or go deal with before they become an issue. Or maybe they’re already an issue,

George Stedman 9:47
or maybe I wanted you wanted it that way, and it needs to stay, but it’s just another one of those things that could potentially be an issue. Yep,

Steve Stedman 9:57
and that’s a really good point, George, because a lot of the time. Sometimes things we’re catching or reporting on here are things that may generally be an issue, but in a given specific environment, they may not always be a problem. It might be what you need for your system to work correctly, but we’re at least bringing up the insight into it so we can go look into it and learn more. One of the other features that Mitch worked on was dark mode, which is one of those things I added a while ago, but it what I didn’t do a very good job, apparently when I added it. So I mentioned when it made it better.

Mitchell Glasscock 10:27
Yeah. So we went and added the new, updated dark mode. So we we reworked all the colors. Everything is a lot more visible now, for those of you who are maybe doing a late night session looking into your servers or whatnot. So everything should be a little bit easier on the eyes. The dark mode also has a persistence now. So one of the issues we had in the past was when you closed out a database health, if you were in dark mode and you reopened it, it would not reopen back up into dark mode. So we added that as far as the colors go. I mean, there’s not a whole lot of saying what the colors are, but, but

Steve Stedman 11:07
when we drill down on some of the reports before, they were not always implemented so well with the dark mode support. But now, when you do drill down on some of the different reports there are, they have a better dark mode representation at this point.

Mitchell Glasscock 11:22
Yeah, so mine’s a little slow right now, but dark mode is working in most reports I’ve been going through and updating a whole bunch of them, just to be a lot better visually, to work in both instances dark mode or light mode. So

Steve Stedman 11:38
next Do you want to bring up the quick scan report Mitch, and we can talk about some of the new additions there. One of the items we added was a check for large differential backups in the Quick Scan. And overall, what the Quick Scan does, it just goes through and takes a quick overview of over 200 items that we regularly check, that we know about that have been known to cause problems on people system. It doesn’t mean everything’s broken on those it just means that there’s something that requires attention. We may want to go take a look at and on any of the quick scan items, you can double click on it in the list, and it’ll take you through to a help page for that. But as far as the large differential backups, who added that one?

George Stedman 12:20
Think that was me. I know we added so many. We’ve added a whole bunch, but, yeah, I think that was indeed me.

Steve Stedman 12:31
And why did we add that one? George man,

George Stedman 12:34
because you I was asked to, no, you

Steve Stedman 12:39
want me to give the background on that one Sure.

George Stedman 12:41
Sorry, that was a while ago. Yeah.

Steve Stedman 12:45
So in a lot of environments, we’ll set up backups so that you’re doing a full weekly backup, and then daily differential backups, and then hourly or every minute or every five minute type log backups. And for some of the systems that have larger databases where they just can’t do a full backup every single day. And what we run into is, in some environments when a database has too much going on, those differential backups keep track of everything that’s changed since the last full. And in some cases, you’re able to go a whole week, or you maybe may be able to go even a whole month without doing another full, without the differential backup growing to be too large. But what happens is if a database has a lot of churn, and that differential backup grows to be almost as big as the full backup, well it seems pointless to be using differential backups at that point. So we use that to detect when we have these bloated differential backups that we could maybe modify the backup schedule and do a full backup that might be more efficient instead. Okay, so next we added the update lock, or UPD lock, causing excessive wait times with check number 222, and 223, who worked on that one? Do you guys remember

Mitchell Glasscock 13:49
that was a combination of us both?

Steve Stedman 13:51
Yeah, okay, each

George Stedman 13:53
did one of those. So one was for blocking, one was for excessive weights. But that keyword update, lock can, well, it locks up whatever you’re doing so that no one else can, or no other queries can use it, at least the tables that you’re in. So it can cause a lot of blocking and weights, as you know, being a resource hog as that that is,

Steve Stedman 14:20
and where this one came from. Working with one of our managed service clients on performance monitoring, we found that they had some SELECT statements that were using update lock. And normally you don’t need to do an update lock on a select and it turned out that just a select statement was doing as much blocking as an update would have because of this, and by removing that, we were able to get really good performance improvements out of it. And it just is one of those things that we realized. It’s not always obvious that it’s being used, but it’s a great way to catch it, if we can, and get a note of it. Okay, so check 224 was added, which looks at user defined assemblies in the databases. So.

Mitchell Glasscock 15:00
That was me. So UDAs are one of those things. They’re often implemented for cross compatibility the software. UDAs are the user defined assemblies are something that we found that can open yourself up to potential security risks, especially when running unchecked. So we wanted to be able to see if any user defined assemblies were being ran in the background, and kind of keep tabs on those, whether or not they’re necessary, if they’re necessary, then we know what they’re doing. If they’re not necessary, then we can eliminate them and decrease security risks.

Steve Stedman 15:41
So an example of that a user defined assembly can be a object that’s compiled as part of like C sharp or.net code. And there’s some clients we work with that are using a pretty common medical EPCR piece of software that tracks patients and they have in their system provided by the manufacturer, these user defined assemblies that are used to verify that they’re licensing, that they’ve actually paid for the software. And if you take them away, it makes it so their software won’t won’t run because it looks like they’re not licensed. Now, in that environment, that’s one of those where you want to be aware of it, because you need to know about it if you’re migrating to a new system, but it’s but you certainly wouldn’t want to take it away in other cases if, let’s say, somebody wanted to put in some malicious code in your system so they could come back and mess with you later, like ransomware or something like that. User Defined assembly would be a great way to inject that kind of thing into your database with kind of get around some of the security checks that you would normally look for. So it’s one of those things that we just have to be cautious with it and really look for them and make sure if the user defined assemblies are in use, that we know why they’re in use and how they’re being used. Next, we’ve got checks 225, 226, and 227 which relate to database snapshots. First ones around how many database snapshots there may be, and then there’s database snapshots that are older than 60 days, and database snapshots that are greater than 250 megabytes and greater than 50% of the original database. Now, what a database snapshot is in SQL Server is when you right, click on a database and say, take a snapshot of it, and it sort of makes a copy of that database that you can you can work with, but it’s not a full copy of it. It only tracks things that are changed, so you can look at that database at a specific point in time. And if you have a database that’s changing a lot, those snapshots will grow to be quite large over time. And these checks, do you want to talk about? Specifically about what those three checks do, guys? Yeah,

Mitchell Glasscock 17:38
so I did all of those for the snapshots. So as Steve said, 225 is the one that’s going to be looking for any database snapshots. If you have taken any snapshots, let’s be aware of how long ago those were. So that’s where 226 comes in, and it’ll update you on how old they are, and then, like Steve said, it checks for what changes in your database. So large databases, if you take a snapshot of a large database, and then you do a whole bunch of changes, those snapshots can get really large fairly quickly. So we want to keep tabs on them as to how fast they’re changing in the size that they may be taking up in the background. All

Steve Stedman 18:25
right, awesome. The next check we added was, and I’ll just cover this one. It’s check 229 which was a specific check that if you’re running SQL Server 2019 cumulative update 27 there were some extra error logging messages that Microsoft, I think, accidentally left in there on that cumulative update, they went away in the next cumulative update. But what happened was, in certain environments, you end up flooding your SQL log with 1000s and 1000s, or even hundreds of 1000s of extra error messages that are not exactly meaningful. So that was there in SQL 2019 cumulative update 27 and it was fixed in the next update after that. So we threw that in as a check, as a check, just to make sure that nobody’s running that version, because you might be spending all your IO writing to error logs rather than actually serving up database files if you’re running that version. Check 230 checks to see if a remote query timeout is below the default value, Mitch, you want to talk about that one? Yeah, so

Mitchell Glasscock 19:26
it’s just one of the remote query timeouts, just one of those settings that someone might have changed on accident. And if you’re running a query, and if you know it’s going to be taking a whole bunch of time if someone set it below the default value, chances are that query might time out before it even gets a chance to fully execute. So this check just makes sure that that hasn’t been edited on accident by anybody working in that instance.

Steve Stedman 19:55
The next check was a check to see if there are Sa i. Are connections that are active and running. And one of the what we refer to as worst practices is that you have people log in and use the SA account for almost everything. A better practice is to not use it for everything, but to use it for some things. And the best practice is that nobody ever uses the SA account, and that you actually replace that with a different elevated privilege account. But so what we did is we added a check to go and show how many active connections there are with the SA user as an indicator that maybe we need to look at this and see that somebody is improperly using that SA account. I remember I worked for a company, gosh, 15 years ago, where, when I started working there, they had one database login on their entire SQL Server, and it was the SA user, and everybody used that, from the product support team to the developers, to the web service or the web code, and it was just one password. And it was at a point, it was too hard to change the password, because everybody was using it every system, and it was so insecure because of this, so that’s the kind of thing we’re trying to catch or prevent. And before I left that company, we had multiple essay accounts. It took a while, but we got rid of most of them, or got rid of the need for having that one. Okay, the next was check 232. Which is checking to see if transparent data encryption or TDE has been enabled, and if it has to check to make sure that no other databases were missed, who worked on that one? That

Mitchell Glasscock 21:28
was me. So we do do you want to explain TDE? Encryption kind of encompasses

Steve Stedman 21:34
Sure. So what TDE covers is the term transparent data encryption means that your database files are encrypted on disk, and then when your data gets loaded into memory, it gets decrypted. So it’s transparent to the application or the user or the person running a query, because everything they run against the database looks just like you would normally expect. But if someone was to get a hold or steal those database files or your backup and files, or your backup files, you would need that encryption key in order to be able to read them. So what we have a lot of clients that we work with where we’ve been enabling Transparent Data Encryption just to better protect their environments, and this was a check to make sure that if we’re turning on transparent data encryption for a client, that we make sure it’s turned on for all of their databases so that nothing slips through the cracks. And Mitch, you added that check. And I think, I think I went over the top on explaining it, but I think that for that one, pretty much what the check does,

Mitchell Glasscock 22:29
yeah, we, we regularly do TD encryption, and so this checks there, just in case one of the databases might have been missed, we get alerted to it, and we can go back and make sure that it’s encrypted, or if a client has already had TDE encryption enabled, we’ll see if they’ve missed anything prior to that.

Steve Stedman 22:48
And where this came from was one of our managed services clients, we had TDE encrypted all of their databases, and then a new database got added that we didn’t realize had been put in there, and it was not encrypted, and it got missed for a couple of weeks until we realized it. So this check was put in so that we never missed that kind of thing again. And that’s where a lot of the checks in this list have come from something that we found that was being done that was maybe not the best practice, and a way to catch it so we know about it right away, and we can deal with it quickly, or that anyone using database health monitor can know about that right away. So I know you guys are actively working on database health monitor. We just got the September release out last week, and there was a lot of good stuff that went into it. There’s a few other bug fixes that were done and a few little things that maybe we didn’t mention here, but every day, we’re making improvements on it and making it into a more robust and better product. What’s coming next? You guys have any plans there?

Mitchell Glasscock 23:50
I’ll go. We’re looking at implementing another color version to database health, something kind of in between the light mode and the dark mode. So if we have any suggestions what somebody might like to see, I’m open to that. So

Steve Stedman 24:06
like me, someone who I’m a little bit overwhelmed by the light mode, but the dark mode is just too dark for me. Yeah, with my old man eyes, that maybe we need that intermediary one? Yeah, I think I’ll, I’ll be the first user on that one. I think sweet.

Mitchell Glasscock 24:22
And then we’re also looking at a method to resizing and being able to move panels. If you have a certain report that you prefer to be at the top to always be able to monitor it, we’re going to be able to we’re looking at ways to reshape database health to be more user definable.

Steve Stedman 24:45
It’s like this whole layout screen that’s being shown here, we’d be able to hide, rearrange or move any of the different panels that show up there to yep, yep. That’s to me, that’s one I’ve wanted to do for a long time, and I just haven’t had the time to do it. So I’m glad you guys have time to work on that now. That’s. Awesome. Okay, any others

George Stedman 25:04
we’re looking at also adding a file size over time report to kind of figure out, like just another way to look at your database, IO slash growth rate, because there’s database by size that we already have, but the actual file size report would be nice to just kind of be able to see that over time, rather than just a snapshot of what it is now. Yep, I was gonna

Steve Stedman 25:27
say one of those questions that I always get around that when, when we come across someone who has who ends up with a database file or log file that’s a lot bigger than they were expecting, and then they asked the question of, well, how did that happen? When did that happen? And we can look at like disk space reports and see when the disk space changed, but we don’t. We haven’t had a real good way in the past to go through and show when a when a file, all the database files sizes changed. And several months ago, I secretly added the tracking for that, so we’re actually logging all of that in the database for DB health, history database now, so at this point, we just need to go and build the reports to be able to display it so that we can see how fast those database files have been growing over time. One of the kind of, I don’t know, work around ways that I’ve done in the past to see how they’re growing is to go look at the backup files, because the backup files are generally related to the size of the databases, unless you’re using compression, of course, but you can kind of get an idea of how things are growing that way with this new report, we’ll give you the ability to go and see that. Yeah, every week at 3am on Sunday, for instance, that that’s when your database file grows. Things like that, all right. So the other thing is, we’re, I mean, we’ve got the team at Stedman solutions. We’ve got 10 team members now, and a lot of the features that we’re working on are coming from internal team members, things that I want, things that team finds, things that we find working with our clients or the clients want. But what I want to do is make sure that anyone out there using database health monitor, if you’ve got a feature or an idea or something where you say, gee, I’d really like to be able to do this with database health monitor, you can go to the website, database health.com which is the normal download site, but if you go to database health.com/ask as k, that will take you to our support page for database health monitor, where you could submit a support ticket telling us what it is that you would like to see in database health monitor, what you’d like to see changed or added, or maybe to answer Mitch question about, well, what color would be the good color to fit in between the light mode and the dark mode? Anyway, we’re really we’re at a point where now that we have three of us working on it, mostly these guys, but me, once in a while, that we’ve got more bandwidth in order to be able to add more features, database, health, monitor, and we’re doing that. So there’s things you want go and do it at database health.com/ask, and put in your input. And we’d love to hear from you. So also, thanks, guys. We appreciate you being on the show. One of the things I want to talk about is next week will be episode two of our official renaming this to be our podcast, and Craig Buckner, our VP of sales, marketing and partner relations, will be on the show with me, and we’ll be discussing our SQL Server managed services, or the concept of database administrator as a service, which one is one of our core offerings at Stedman solutions, and it’s one of the key things that We use database health monitor for in tracking. So thanks for watching everyone.

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.

Visit our main podcast page for other episodes