- Hosts: Steve Stedman / George Stedman
- Listen on Spotify
- Recording Date: September 22, 2026
- Topic: Instance report upgrades and visual changes in the Database Health Monitor Version 4
Database Health Monitor Version 4 Instance Reports Stedman SQL Podcast Sn 3 Ep 17
In Season 3, Episode 17 of the Stedman SQL Server Podcast, Steve Stedman and George Stedman explore the powerful Instance Reports in Database Health Monitor Version 4, demonstrating how SQL Server DBAs and IT professionals can quickly identify performance bottlenecks, troubleshoot blocking and deadlocks, monitor CPU and disk I/O, detect database corruption risks, and improve overall SQL Server health. Discover how the revamped Quick Scan, What Is Active, Open Transactions, Database Use, Deadlock Advisor, Disk Latency, and other reports provide actionable insights into your SQL Server environment. Steve and George also demonstrate new navigation shortcuts, historical performance tracking, security checks, backup monitoring, and tools for identifying unused databases and inefficient resource utilization. With more than 60 instance-level reports, Database Health Monitor Version 4 makes diagnosing SQL Server problems faster and easier, helping you resolve issues before they become critical and avoid unnecessary hardware upgrades. Download Database Health Monitor and start your free 30-day trial at https://DatabaseHealth.com
Podcast Transcript
Steve (00:16)
All right, welcome to the Stedman SQL Podcast. This is season three, episode seventeen, and I’m your host, Steve Stedman. Today I’m joined by one of the Stedman Solutions team, George Stedman. Hey George, welcome to another podcast.
George (00:31)
Hey, thanks for having me again.
Steve (00:33)
Yep. This is the seventeenth episode of season three, or our fifty-seventh episode since we started the podcast. Thanks everyone for joining. This week’s topic is going to be Database Health Monitor version 4 instance reports. Database Health Monitor version four introduced many new reports and many report rewrites. Some reports that have been there for 15 years have been completely rewritten and revamped. Most of the reports, though, are split into two categories. There’s those reports that apply to a whole SQL Server instance, and there’s those reports that apply to just a specific database. What we’re going to be talking about today is the instance level reports. Yeah, so Database Health Monitor, we just came out recently with the version four release. And what Database Health Monitor is, if you haven’t seen it, is it’s a powerful SQL Server monitoring tool by the team here at Stedman Solutions. And it’s for helping DBAs and network techs or whoever’s working on the SQL Server spot performance issues, bottlenecks, and health problems quickly. Our version four release was September of 2026, and we’ve had several updates since then. So, want to find out which database has the most blocking or which databases aren’t even being used at all. We’ll show you how to do both of those today. But to get started, let’s jump in and start talking about the instance level reports. All the reports we’re going to be looking at today are reports that apply at the instance level.
All right, all the reports we’re looking at today will be instance level reports that apply to an entire SQL Server instance. When you open up Database Health Monitor, you’ll see on the left side of the screen you have a list list of all of the SQL Server instances that you’re connected to. you’re not currently connected, you might need to connect to one to get started. But once you’re connected and you click on one of those instances, I’ll click on this one, Enterprise 2025, which is a development server. I’m not going to go click on that production server for this purpose. You can see that at the top level, there is a section in the grid here called instance reports. With that, there’s different sections. There’s performance. These are things you’re gonna want to look into if you’re having a performance issue right now. blocking tied into performance, things about storage, what’s going on with your disk subsystem, things like that. what’s your recovery state? Can do you have good backups? Do you have check DB? Things like that. what’s your security posture? What are you looking at for indexing, jobs and errors, and then over time, a bunch of reports to show you what’s happening. Changing over time as well as configuration and much more.
So the instance reports are located on the main server overview panel for each instance that we click on. part of what we’ve got here is it’s prominently located so that it’s easy to find because these are some of the things you’re gonna be looking into when you’re trying to figure out what’s happening with your whole SQL Server. Now, all of these instance reports, they run against live DMVs. Our historic monitoring database and catalog views. So this is not just a static snapshot, it’s things that are changing over time. So let’s jump into some of the health and performance reports. Let’s start with a quick scan report, because this is one of the first places I look. I’m going to give some hints along the way on how to better find things, but somewhere in this list we have quick scan. It’s right here. But rather than clicking it, I’m going to show you how to find it. You can use the search box here to just type quick scan. Type quick. There’s the quick scan report. It gets filtered. We click on it and it goes out. It takes 10 to 15 seconds to do a quick scan of your server and it’s going to come back with some findings. What are some of the things that we typically come across here, George, with a quick scan report?
George (05:09)
before we before I go into that, I will say it can take longer if your database is really big and bloated, because it will go through and look at all your indexes and stuff as well.
Steve (05:20)
Yeah, good point.
George (05:21)
Can take longer, but on a normal, like I don’t I guess normal isn’t really a good word for SQL, but
Steve (05:30)
Certain environments may take a little bit longer to load. So we have one client we work with that has well over a thousand databases on one of their SQL servers. Not your typical thing, but it’s there. And yes, this report takes a little bit longer to load in that case. So what kind of things do we typically find here when we’re looking at server storage?
George (05:52)
I mean right off the bat we on a surprising amount of clients we see that DBCC check DB has never been run. So that’s a
Steve (06:06)
I’ve set up our test database
George (06:04)
That’s pretty realistic.
Steve (06:07)
To simulate what we typically see. So one of the things we can do here is we can right click on this if we wanted to. There’s a lot of options, but we can go and go to the CheckDB, check DBC CheckDB report. And from there we could run CheckDB on these directly if we wanted to. Or we could go do other things to act on them. Other stuff that we see in the quick scan, George, that we’d typically deal with.
George (06:32)
It’ll tell you about your backups, any critical backup, like if you’re not doing backups or if your backups are too infrequent, or it’s got a few different backup level reports that will pop up real quick. and then it will it’ll go from high to low. It will slowly get more like it’ll go into the little itty bitty, like the gritty security stuff that like if you want to pass certain really strict audits, you should have turned on.
Steve (07:02)
So to start with, I’ll often click on the critical up here and this will filter the list down to just what are the most critical things. Well, the two things you said, no check DB and no recent backups. I’m gonna want to go and deal with those before I deal with the next ones. So then I might click and once that’s done, go deal with some of the high ones and then the medium and then some of the low ones after that. Depending on what it is that we want to do.
George (07:26)
And then you can also sort by category on the the right there. And so the two probably most important categories is corruption integrity and backup and recovery, ’cause and without those, if those are those are probably your urgent fixes, even if some of them aren’t critical.
Steve (07:45)
Yep. And then here’s some security ones we can go look at next, whatever order we want to put those in. So lots of different ways to look at what’s going on here. The other thing is when you click on one of the items, oftentimes there’s other actions listed down in the bottom right that you can go and do. You can go to the connection reports, you can read an article about this, you can copy the ticket, or you can ignore this check if you want to. There’s sometimes that we will get what we would consider a false positive, or it’s something that could be an issue, but we’ve looked at it and we know it’s not. So we just can go ignore it if we know it’s an issue. So quickscan is a great place to start as an instance level report. one of the things that going to do here is we’re going to use the back button. So when you’re navigating in database health monitor, everything is put into a history stack. So you can go back. When we hit the back butt button, it takes us back to the server overview where we started the report before that.
George (08:38)
And I believe backspace works as well, right?
Steve (08:42)
Yes, if we go, I don’t know, I’ll click on job schedule and the backspace button takes us right back just as well. The other thing is if you’re 10 levels deep on your navigation, you can also right click on the back button. So you can go and jump to any one of these reports that you’ve been to or you can clear the whole history. Yep. Okay, so the next one I wanted to take a look at was the what is active report, because this is an important one. And what this report does is it goes and looks at what queries are actively running right now on the server. Now, if we look at live, it’s showing. wow, this is a good example because we’ve actually got blocking going on. imagine that the test database, we’ve got a blocking script running. right. With this. It’s going to refresh once in a while, but it’s going to go out and give you a list of all the sessions that are currently active. Now, let me tell you what I mean by active. It doesn’t mean every connection that’s open. It means every connection that’s actually open and doing something. If you have a connection open that’s not doing anything, like you’ve just got SQL Server Management Studio open with a query window there, that’s not going to show up in here. What’s going to actually show up is queries that are actually active or doing something. And it’ll update. And this is a great way to look to go and be able to find out what’s going on with your server. If you’ve got an instance where things happen on your server and things get jammed up and everything gets backlogged, you can often go to this and find what’s going on. You can always go right click on it. And then right click has a lot of options. But one of the things you can do is you can kill a session if you need to from here. Kill session 53. Are you sure you want to do this? Yes. And then it’ll refresh. And if that’s your blocker, then that query has been cleaned out.
George (10:33)
Yeah, this one’s nice because it’s kind of like a task manager for SQL Server. So it you yeah, you can really kind of see like what’s going on. But the caveat is it has to be going on right now. It only grabs live stuff.
Steve (10:51)
Now that takes us to another one I want to peek peek at here real quick is the open transactions. Now it’s possible that you’ve got an open transaction that’s running, but it’s not actually doing any work. For instance, in management studio, you’ve opened a transaction, you’ve done a query, and you have not yet committed or rolled it back. Well, it may not show up in the what is active because it’s not doing anything right now, but it’ll show up here. And you can see here’s an example of an idle transaction. That’s been open for seven days and twenty one hours. And look at that. It’s even put a nice red button up there to kill it. hold on. 84, there’s a different one. It’s but we can go and click on seven days. That’s ridiculous. We can right click here and kill session 59. There it is. And it’s gone. So that’s one of those things you really want to do a little bit of checking. Don’t be quite as frivolous about terminating transactions. However, if it’s been open for seven days, most cases you’re probably gonna be okay to go and terminate it. You notice at the top of all the instance level reports, they have related links up here. These are some great ways to get around because if you’re looking at open transactions, it might mean that you’re diagnosing a problem and you want to go look at blocking queries. here’s an example of this query is blocking that one. And we can go and double-click it, click on it and see here’s the details. and this is a real handy one to be able to see the blocker, and oftentimes.
If you’ve got one query that’s jamming up like 50 other sessions, it’ll see one blocker here and then like 50 sessions below that are all blocked up by it. Well, oftentimes to get your system running again, you just have to kill that one blocker and everything else continues to run. All right. So let’s take a look. I don’t know where we’re at here. we’ve got blocking.
George (12:48)
I was gonna say off of the go back one more just talking about kind of like quick like in the moment what’s wrong. I like the connections report up at the top there on the right. So this one will show you all the live connections, even if they aren’t doing anything. So if maybe you saw some blocking a little bit ago and you’re like, huh, I wonder who’s logged on, you might be able to see that so and so still logged has a session open on the database even though it’s not running.
Steve (13:26)
Yep. So this is kind of like what you might see out of the SP HU2 procedure, but it’s got better detail. things like we can go and double-click on one of these and it’ll bring up the full details of the query that’s going on there. can also look up top. We’ve got this chart that shows on the left the client addresses and on the right the login in the middle of the program. So we can see that there’s kind of the yellow text here, two of them that are coming in from the Dot 11 and dot 33 IP addresses are both connecting to database health monitor and using the SA connection. Well, yeah, it’s a test server. I’m overusing SA. Probably shouldn’t be doing that for a proper secure environment, but it’s one of the checks we have in our security. But we can also see that the local machine has connections open to SQL agent, SQL Agent JobSTAP, log reader, because we’ve got replication running here, and the SQL Server CEIP or the Customer Experience Improvement Program. So that’s one of those that we like to turn off if it’s running. But just a good overview of just what’s going on here. We can also, outside of the flow from the connections report, look at clients, get detail about what’s going on with different clients. security. This is an interesting one because we can go and see okay, database health connect monitor is connected from two different servers. Those are my desktops, and it’s not encrypted and it’s which means I should have clicked the checkbox to turn on encryption when I connected, but I didn’t. And then under auth, it’s using SQL authentication as opposed to Windows credentials. It’s using a TCP connection. And many of them are using shared memory on the local server. then the version of the driver that’s being used and the number of connections that are open. So one of those things that people often ask is how can we turn on forced encryption for everyone? Well, if one of the things you want to start doing is figuring out, well, who’s not using encryption and use this to go find them and clean them up.
George (15:23)
Which is back to our first one, is one of the quick scan checks is if you have forced encryption turned on. And that’s often a pain point because you don’t know what most people don’t know if like their third party app is using encryption or not. And surprisingly, it’s or I guess maybe not surprising to a lot of people. third party apps usually don’t try to be as secure as possible.
Steve (15:49)
So prior to the version four release, I was working with a client where they turned on forced encryption and a bunch of stuff failed. And they didn’t have a good way to come back and show, okay, well, who’s connecting that’s not encrypted? So this is a good report to use to figure that out. So can also do things like click on the bottom and export to CSV or HTML or find in the grid, things like that. So
George (16:13)
You can also kill a session from here as well. which is nice if you wanna know like if you kill a session, will it auto-reconnect? If it’s if it’s is a third party app or using like a a Java driver or something to get into SQL Server.
Steve (16:28)
Yeah, so let’s say we click up here on this database health monitor on our OG laptop. That’s the computer I’m running on right now. if we decided we wanted to kill connections, how would it behave? Well, I’m not gonna do that because it’s in the middle of the demo and I don’t want to mess something up. But I could go to this one on my other computer and just go and click here and say, okay, kill this session. Yep. And then I forget, do we put in multi-select on kill? No, it’s only gonna kill one of them. It’s not gonna kill all of them. There, there’s not a kill option up there. So But this is way more functionality than we had in the previous version. There’s always been a connections list, but it’s never been as useful as this one is now. So I’m going to hit the back button a couple times. And we’re going to go back to the top level server overview and talk about a couple other ways to find reports. So let’s say you want to find something related. Well, let’s just say you’re trying this out and you know that there’s a logins or sorry, a connections report because you just watched this, but you don’t know where it is. You can just hit the control K, which is also it’s hinted up in this little search box up in the top left here. But Control K brings up the search dialog, and I can just type connections here, and it’s going to show me connections on the current SQL server. but I’ll spell out the whole word connections. But it’s also gonna show me the connections report on all these other servers that I’m connected to as well. So I could jump around to the connections reports on different servers if I wanted to at this point.
There we are back to the connections report. the other thing I want to look at, let’s just jump into alphabetically the first report we have in the instance reports, which is it used to be. It’s now availability groups. So the server we’re on isn’t part of a availability group. If it was, we’d be able to see a bunch of availability group information here. But what we can do to navigate amongst the reports, George mentioned earlier the backspace key, but we also have control plus arrows to navigate. So if we go control right, it takes us to the next report alphabetically in the list, backup status. And then we hit control right again, it takes us to the blocking by hour, control right again, blocking queries. And then of the obvious one, if we’re doing control right, it takes you alphabetically to the next one. Control left takes you reverse alphabetically to the, I guess, to the previous one at that point. is really handy. I built this feature initially because clients I was working at, I needed to flip through all these reports, and I just got tired of clicking around to be able to get to all the reports. So we put in this. That I’d go and check out a server, control right. I’d page through and say, okay, everything looks normal there. here’s our blocking tree. This is another view on blocking. It’s showing like the red here is what the blocking cause is. The next column over are two that are blocked by that. I’m gonna hit F5 for refresh here and see if we get any different information on this one. sometimes if there’s more blocking getting on going on, we’ll get a third or a fourth column over here. We hit F5 for refresh a few times. And now I’m going to do control right arrow to jump to the next one. this is this is a fun one. This is a new one that was added in our, I think, our second release after the version four release. And this report is showing what’s going on in different problem areas. Because oftentimes, if you’ve got a problem with your SQL Server, you’re either going to be related to slow storage in some way or not enough I.O.
Slow processor or not enough CPU or contention, which would be blocking and locking and things like that. So, what this report is showing on this specific SQL server, shown by color and size, the biggest issue we have is around blocking. Well, imagine that it’s in the blocking test database. If I click on that, it jumps down here and it can give additional details on what’s actually happening with their blocking held up on I/O. Or we can go click up here. If we look on processor, we can see here processor is not as big of an issue, but we’re able to really just kind of click around and get a different perspective on different queries and what part of the system that they’re going on. So, like if someone’s has this kind of a chart right now and everything is showing up under contention, should we tell them that they need to get faster disk charge?
George (21:06)
No, we shouldn’t.
Steve (21:08)
No, because it’s blocking. And no matter how fast your disks are, it’s still going to have blocking for doing things like updates. do they need more processor? No, they don’t need more processor because the main problem they have is blocking. Now, if all of the slow queries were showing up over in the storage corner of the triangle, well, that might be where we need to go and look at faster storage for them, faster disk, faster I/O. So, but one of the mistakes that people so often make when they don’t have someone who can analyze this is they just say, my server’s slow.
Let’s just throw more money at it and let’s just get more CPU and faster disks and more memory and all that. Well, that’s not not always going to help you. And this chart is a really quick way to figure out where is your pain point, storage, processor, or contention.
George (21:52)
Money’s a quick fix for the first couple times, but after three or four times it gets messy.
Steve (21:58)
Yeah, if you’ve got a two thousand dollar server and you double it to a four, well that’s not that bad, or you double it to an eight thousand dollar server and you double it about four or five more times and there’s no way you can afford that server anymore configuration values. This is an old report. Again, control right to get there. that has been revamped again with the latest version where we’ve got different filters and verdicts and more information on here on what’s going on. Connections, we already looked at this one when we’re clicking around earlier, so I’m just going to flip past that. CPU by database. Well, this is a test server. There’s not a lot going on here. So database health history is always the top one when we’re on a test server. But on production, you’re gonna be able to go and see which database has the most CPU. And then you can do things like click, double-click to drill down into that database to see what queries are taking the most CPU. Now, we said this whole podcast is all about instance level reports, and we just jumped out of instance level into database level by double-clicking that. I don’t want to go there, I’m gonna go back and we’re gonna continue on on the instance level reports. CPU by hour, good way to see how you’re doing overall with CPU. On most of these by hour reports, you’ve got show more buttons that you can expand it out and kind of see more, more weeks of data. CPU schedulers. this is one of those that if you ever have contention on your CPUs being overloaded, you’ll be able to see here on this server I have a total of 10 CPUs and they’re not really doing much right now. if there was a backlog or things waiting, that would mean that you have CPU contention.
Ooh, this is a cool one. George, over to you.
George (23:46)
Database use. So this one. This one is it used to only be a a database level report, but then we decided why not make it an instance level report? Because it’s way more useful as an instance level level report. this pretty much.
Steve (24:01)
But we also had a client that said, How can we tell which databases don’t have anything happening with them right now too, right?
George (24:07)
Yeah, well that’s why it’s more useful as an instance level report because I went through and did database level report for each and every database. And I said, This is dumb. Why don’t we just have one button for it? but this this one’s really good. It will tell you if any of your databases are not being used. and this only tracks since the last restart, but for a lot of stuff like a lot of people will have their se SQL server up for months at a time. So if you’re a single database hasn’t been used since for an entire month. So this may might be something that you want to check each time you do updates or each time you restart your servers. and you could be like, Well, wow, this database uses a whole ton of storage, but man, it has not been used since it’s been rebooted and it’s been a month. So unless it’s like an archive database, it’s probably a good sign that maybe you could clear it up or at least get it off of the SQL server and just save the backup somewhere.
Steve (25:08)
So this is a great time to introduce a new hotkey. We’ve done control left and control right on the arrow keys to switch between the reports alphabetically. Now I’m gonna do control up and control down. Control down jumps to the next same report, but the next instance.
George (25:25)
Next instance on your list, which is really nice for those those really big environments where you have ten or fifteen SQL servers and they say, Okay, how which which databases are being used? How can we clean this up? Because we’re running out of we’re running out of storage or we need to consolidate some of these.
Steve (25:50)
Yep. And we just jumped to older servers here. This this database report is not available anything older than 2012. So as we flip through here, we get eventually back to 2014. there’s 2012. Yep. And then 2014 and it’s back to working again. So what I’m gonna do is just jump back up to Enterprise 2025 where we were do the control K just as a reminder. Database use is remembered in there as the last one. but we want to go to it on Modern Use Enterprise 2025. And there we are, back to it. So that’s a pretty handy one that George and Mitchell added just a couple weeks ago.
George (26:39)
Just kind of a reminder too is all of almost I would say actually all of these reports were made for a reason. and that reason might not align or might not be useful to everyone, but there’s a report for everyone and if there’s anything.
Steve (26:57)
Yeah, that’s that’s a really good point.
George (27:01)
If there’s any report that you think we should have that we don’t, let us know.
Steve (27:06)
Absolutely. Yeah, let us know. We don’t just sit around thinking, gee, what’s a report that might cause a or help a problem? What we do is we think about what’s a problem we have that we’re dealing with. How can we have a report that’ll fix it? All right, databases by size. a lot of environments that we work in. You’ve got usually like one database that takes up like 80% of the entire SQL Server. There’s a lot you can learn from that. here we’ve got some things like the big stuff database has a really big log file. data’s 14 gigs, logs 21 gigs. Well, it’s that big because I had some really big import tests running on it. But let’s say we want to go and fix that log because 21 gigs is too big. I could just right-click here. Go to the VLF report for big stuff. Again, jumping out of instance level, jumping to database level, I can see, this could easily be shrunk down. This is showing 356 virtual log files in that log. We could easily shrink it down to about two thirds of the size that it is right now. I hit shrink file. Let’s see if it shrinks. There we go. We’re smaller. We’re hit the back button. Go back to where we were. And now the big stuff doesn’t have quite as big of a log file.
It’s still a pretty good size, but it’s not as big as it was. So continuing along on our tour, control right, we’re going alphabetical, deadlock history. we’ve got a great podcast episode on deadlocks and blocking and the differences between those. So we’re not going to take the time today to explain that. But with this, we can see when deadlocking is occurring, what deadlocked. And check this out, we can double click on the deadlock.
From our deadlock advisor, we can go and see who’s stuck, blocked, waiting on what, what sessions it was, what tables it’s locking on, and specifically even what index on that table it is. Well, it’s the primary key, which in this case is the clustered one. But the order of getting stuck between table A and table B is what’s causing the deadlock here. we can look at information about the sessions, what locks were going on. Here’s evidence about it. Session 121 holds a key lock on table B. 121 is waiting on a key lock on table A. And here’s the opposite. The other session is holding key locks on the other tables in the are the same tables in the opposite order. Therefore, deadlock. Bam, one of then terminated.
George (29:29)
This is another one of those reports where I there’s ways to see it inside of SQL Server Management Studio. They’re not pretty.
Steve (29:40)
No, one of the ways is you can just turn it on to dump it to your SQL log and you get a bunch of information in there. It’s not as useful as this. this is way more useful. And you can go save the graph and copy it off and save an image and send it somewhere if you need to send it to a developer and say this is the problem. We need to fix these queries.
George (29:57)
Screenshot it, teams the the person you think it is. Let them deal with it.
Steve (30:02)
All right. more information on deadlocks, control right arrow. We can see here most of the deadlocking is happening between table A and table B. Well, that’s because it’s a test server, and I specifically set up a deadlocking test with table A and table B. But this would be able to show you very quickly if you have deadlocking going on in your environment, you’ve got 10,000 tables out there, what tables are having the deadlocking issues around. Here’s gonna deadlocks by database. Again, control right arrow to get there. gonna show me that the big stuff is the database that’s having the most deadlocking. And I can double click here to drill down into big stuff and see just the deadlocks for that table. Or sorry, for that database.
George (30:46)
Which was the report two two reports ago.
Steve (30:50)
The difference was this one is filtered for just the database big stuff, where the previous one was showing it for all databases on the system. Because big stuff is the problem, they probably look pretty much the same. So continuing on, we can see deadlocking by hour. All of our deadlocking happens between 8 a.m. and 3 p.m. typically.
George (31:13)
really like these overtime reports, especially the deadlock one, because you could say, well, maybe I shouldn’t schedule these two jobs that always deadlock each other at the same time, whereas free.
Steve (31:25)
Yep. Good point. Good point. Continuing on, deprecated features. this is a pretty cool report because SQL Server has deprecated a lot of features over the years. And what that means is they’ve come up with some feature that they’ve said this may be going away at some future version of SQL Server. And you don’t know when it is. But you know, if you’re going from SQL Server 2022 to 2025, you can go test it and see if it’s still there. But Microsoft always tells you that with Azure, well, deprecated features, you don’t really know when they’re going away. They could pop disappear at any point on Azure SQL Server. So here, this is going out and querying what’s in the database to see in all the databases we have, where are we seeing these things that have been announced as being deprecated? we can go look at the catalog and see, well, what like data types text and text or image. I mean that’s been deprecated I think since SQL Server 2000 or 2005.
George (32:27)
Yeah, it says there announced the announced column is announced two thousand five.
Steve (32:32)
Good point. stored code. Yeah, we can also go do a live capture and watch live start the capture. And what this is going to do is it’s going to monitor. I’m not going to do it because it takes a little bit to set it up here. it’s going to monitor everything that’s currently running on your SQL Server. And even if you’ve got some co code out there running in.NET or Cold Fusion or whatever that’s calling some deprecated feature.
It’s going to catch it on the fly while it’s being run with that watch live feature. So that’s the kind of thing that if you’re thinking about going to a version of SQL Server where one of these deprecated features doesn’t exist anymore, well, you could turn on Watch Live, keep an eye for on it over time and see if it shows up on your server at all. Control right, disk latency. George, this is one of your reports, isn’t it?
George (33:21)
Yeah, this is this is a fun one. This one will actually tell you if maybe your hard drives are the issue or if there’s maybe something or maybe if you need to adjust some of your queries to be less disk read write intensive, which we’ve seen both.
Steve (33:44)
So on this server, this is my R drive and it is read intense or this is the reads. Do I have read issues on this server, George? On the R drive?
George (33:55)
Sometimes. I mean if it’s an SSD, I mean eighteen milliseconds is a lot, but it’s not horrible. We’ve seen it where it’s whole seconds of latency and this this is another one that is really nice for planning if you don’t have backups running. it’s a really one good one for planning your backups.
Steve (34:15)
So if we’re going to run backups, we probably wouldn’t want to run them at 11 or noon during 11 a.m. or noon. We’d want to run them in the middle of the night when things are all green. And then you can go choose the drive letter here. Go look at the Q drive. It’s about the same. We can change it from reads to writes. look at that. Writes. We might have some issues with writes we want to look at. one of my favorite ways to fix writes is to reduce the amount of work you’re doing. one of my favorite ways to do that is, I’ve done this at a few client sites, is an update statement to update something, set the value equal to something, and you put in the where clause where the value is not already equal to that something. it’s quite amazing how much I.O. you can reduce by not setting something to something it already is.
George (34:59)
Yeah, it’s been a while. Does this one have a double click on the time slot to see queries?
Steve (35:05)
This one does not. That’s in the queue. we can copy the chart. Yeah, the CPU one does control right arrow again. That takes us to disk space. I’m just looking at our time here. how much we’re gonna get through. We’re alphabetically on disk space. we’ll flip through a few of these. disk space forecast. this is trying to figure out how long it’s gonna be until things run out of space. email alerts. I’m not gonna tell you about this one. I’m gonna tell you to go watch our email alerts podcast where we talk about everything there is to know about our email alerting system.
Control right arrow again is going to jump us to the error log. Now, how many times have you right-clicked on the SQL Server error log error log and come up with like thousands and thousands of entries? In this case, there’s 6,000 entries. And then not really been able to figure out what was going on because there’s so much stuff in there. Here we can go and pick which log we actually want, the current one, previous ones. We can switch it between the SQL Server or the SQL Server agent log. Agent log is empty on my server can go look, flipping it back to the SQL log, we can go look at just 24 hours or seven days or everything. And we can filter different things based off of time sources, things like that. But we also have the ability to go in.
George (36:27)
The text filter is really nice. Like if you wanted to look at…
Steve (36:32)
Killed. Who was killed? Look at that. There were four sessions killed, and that was all done during this demo. Yeah, there’s a lot of things you can do with that search. then you can also do things like cycle the log, start out fresh. That’s one of those things that whenever we’re trying to fix a problem and it’s filling up the log, I like to go and cycle the log and start out fresh and then see how fast things grow back grow back.
Continuing on, failed jobs, always good to know what’s failing on your job system. Quick way to find those. Failover compatibility. Okay, this is not an availability group, but if this was an availability group, it would tell you right now, are you safe to fail over? And what it means is not just is your data synced, but do you have the same users on both systems? Do you have the same jobs on both systems? Do you have the same link servers on both systems? All those things that if you’ve ever worked with an availability group and they fail over from the primary to the secondary, that will cause problems for you.
George (37:34)
Another one built from personal experience.
Steve (37:38)
Yeah, recent personal experience. here’s looking at file utilization. We can go through this. This is one of my favorites. That I mean, normally we don’t like to shrink files too much, but if we’ve got a server that’s running out of disk space, we can go and look and say, okay, if the e drive’s running slow on space, we can go and see that we can probably shrink some. if we look at logs, northwind log, not much. Well, we could probably shrink that a little bit. This one, 14 gigs down to seven gigs. Maybe we can do some shrinking there. it’s one of those things that if you do have to shrink or you do want to understand what’s going on with your space and your utilization, it’s good to be able to go and look at how big the data files are, but also how much of those data files are actually being used. I.O. by database. yeah, this is one of those that when we’re looking at performance tuning, if we were I.O. bound, which we found out we were not on that triangle chart earlier, but we’d want to go here and first see what database has the most I IO, and then from there track down the queries that are having the most I.O. as well.
George (38:44)
That’s been one where you you use the d IO by drive and IO by database and a lot of the times if temp DB is your big IO hog, you could maybe move it to a different drive.
Steve (38:55)
Wow, TempDB is our big IO I I.O. hog right now, George. If you look at this, like 97.6% of all of our IO is TempDB on this server.
George (39:06)
But this one shows that it’s on its own drives, so hopefully it’s not affecting your other databases.
Steve (39:11)
Are in the Q drive, exactly. So it may be affecting the performance of queries that are running through TempDB, but the TempDB IO is not impacting all these other databases that are tiny and sit on the E drive.
Another one of my favorite new charts.
George (39:26)
I like the new charts.
Steve (39:28)
Okay, moving on. Index lob columns. Now, putting big objects into indexes is not very smart sometimes. So, what we did is we set up some really wide columns and we created some really stupid indexes on them because that happens a lot in the real world. And in there, it’s intended to go and catch things that have big stuff. Well, it’s the big stuff database, which is kind of why we named it that. But big things in the index that may need some attention.
George (39:59)
Var Char Max Data.
Steve (40:01)
Don’t index on bar chart max if you can avoid it. And yeah, there’s a couple of examples of the problem areas. So inventory, this was something that’s been around for a long time. It’s just a way to export some of this data. it’s a format that was used by some other systems that you can copy and paste it out of here and paste it in some other system to use it. but it’s also handy to see for the SQL Server. the red is showing that the SQL Server is not up to date to the current level of patches from Microsoft.
Job history, you want to cover this one, George?
George (40:40)
This is another one where it’s a kind of overtime report to help you figure out if you need to schedule a new job, where might be a good time to do it where it’s not like where the system’s not busy. Which is another way to optimize your overall performance is just run your jobs when the system’s slow.
Steve (41:02)
And to play off of that, we can go right to the job schedules report to see when things are actually scheduled. in management studio, you can see all the jobs, but it’s not so easy to go and look at what the entire frequency or schedule is for each of those jobs. Large tables, this is one of those that yeah, we’ve had some stories over time on this one. We used to have a different report that looked really horrible on this, but this one looks much better. But when you’re working with a client and you find out, gee, their server has 14 terabytes of database space used up, and two tables are taking up half of that. Well, that tells you you can free up a whole lot of space just by dealing with what those two tables are. And not worrying about the rest of it initially.
George (41:50)
Think a common one for that is if you have like login history is something that you don’t need to keep permanently and then maybe email history, I think is the other one. Yeah, but those are the kinds of tables that grow indefinitely and they add up over time, especially if you’re querying those.
Steve (42:10)
Then we’ve got the last DBCC CheckDB known good. Now, if you don’t have a recent check DB, you could have corruption. So in here it’s showing you those databases that have never been checked. Let’s go look at this demo database. We’ll double-click on that. We’ll say start CheckDB. it completed that quick with no errors because it is tiny. can go look at a bigger one and we’ll start CheckDB. And it actually will go through and show you how far along through the process it is while it’s doing the whole check TB. And if you’re impatient like I am, you can have that one open and then go open another one. And then what happens when they complete is they pop to the front again. So we’re actually going to leave that other one going in the background and it’ll pop to the front when it’s done. And move on to link servers. Here’s one of those reports that again came from troubleshooting and
Problems with link servers, why things weren’t working right. Well, sometimes you can have a link. look at that, the big stuff database, check TB, finally finished, it popped to the front. So it’s all green, it’s all good. So we’ll go ahead close that. but the link server shows you like what actually is enabled and different things here could cause problems. a link server with database access disabled is kind of hard to use, but there’s a bunch of different things here that it’ll show you about your link servers.
Log shipping. this is kind of cool. Here we’ve got this log shipping where we’ve got log shipping going here from north wind to northwind. on different servers. We can go and do things like right click on it and change log shipping settings. while log shipping is running, we can go and say, Okay, well, we need to move the the backup folder somewhere else and we’ll go change it in all the log shipping jobs. Change the timing, or we want to change it from Whether we’re using backup compression or not, or any of the settings like that, it’s pretty, pretty handy as a shortcut to tune up your log shipping without having to go through and reinitialize it.
Gosh, this logins one was really cool when I built it, but it almost seems boring now after looking at all the other ones. But one thing that is cool about this, this shows you all of the logins that are active on this server. I’m sorry, not active, but logins that could log into the server. These are users that could log in. They’re not necessarily logged in right now. One of the handy things you can do when you’re migrating from this SQL Server to a newer version of SQL Server is we have an option here to right-click, create a script. Script to create users for all users and it creates user creation scripts that you can use to migrate to the new SQL Server and it has a hashed password value in there. So even for SQL logins where you don’t know the password, you can go and create them on the new server with the same password.
Super handy little report there.
George (45:01)
I will say that that doesn’t work in all environments. I think like Amazon RDS makes it so you can’t copy the passwords out, but other it works.
Steve (45:09)
But it works in others.
George (45:13)
A lot of the time.
Steve (45:14)
Yep. All right. So we are jamming through this here. There’s a lot left. Long running queries. You if you want to find out what’s the longest running problem queries, look for the biggest number here and double click on it and it’ll drill down and show you. Look at that. CDC change data change data capture scan. Huh. That’s slow. And you can go and find out what’s going on there. So the again, the long running over time, that’s a handy one. Scrolling through here, long running history.
George (45:43)
Same stuff, different view.
Steve (45:46)
Memory. What’s taking up the memory on your SQL Server? I mean, it just on and on and on here. and I think we are almost out of time. So what I’m gonna do.
George (46:00)
And I think when I first started working on database health monitor, there was maybe ten or fifteen instance reports.
Steve (46:12)
Yeah. And now there’s what’s the count? Hold on, let me let this one it shows 64 instance reports on this server. And there are even more database level reports. So I think at this point, that really I mean, we could probably go on for another hour showing these reports and talking about the detail and things on them. but for now, we don’t have the time to do all of that. So point we’re trying to make with this is there is a whole lot of information in there with database health monitor version four. And it is a massive improvement. If you’ve used database health monitor three or you happen to be one of those people out there that’s falling behind and still using version two, upgrade. There’s no cost to upgrade. if you’re a paying user, it’s the same for whatever version you’re using.
And new version is just massively better. So if you’re not using Database Health Monitor and you want to try it out, you can go to databasehealth.com and download, get the latest version, check it out. if you’ve never used it before, you can get a free 30-day trial. if you have used it before, everyone’s trial who everyone who had a previous trial has had that trial extended through October 2026. Anyone’s used it before after that and you really want to try it again, let me know and we can extend your trial a bit longer. So what I want to ask is, George, what’s your favorite report in the instance reports and Database Health Monitor?
George (47:43)
Ooh, favorite report is probably one of the ones that’s gonna save me the most time. man, I feel like I need to have them in front of me.
Steve (47:54)
That’s okay because I’m going to tell you then to do what I’m asking the listeners to do is to post a comment and tell us what your favorite report in the new instance reports is in Database Health Monitoring.
George (48:05)
Yeah, I’d probably have to say the the what what do we call the one where you see last used. Yeah.
Steve (48:11)
Last used. Yep. That’s that’s a new favorite one for me too. But I think that triangle one that we were looking at there was one of my favorites as well.
George (48:19)
Yeah, that one’s pretty cool when they when someone says, Hey, what’s what do we need to do? What do we need to upgrade? What do we need like why is it slow?
Steve (48:27)
Yep. Absolutely. So George, tell us tell us about our next episode.
George (48:34)
Next episode is gonna be what we typically would do in the first 30 minutes on a soup SQL Server. Like what how we would diagnose, how would we how where would we s where would we start? And we probably break it down into five-minute intervals and go over like triage, 30 minutes. How do you fix a SQL Server? How do you even begin to start looking at something you’ve never, someone’s SQL Server you’ve never seen before?
Steve (49:04)
And we get thrown into that a lot. So hopefully we can share some of our experience there to help out. Okay. So I’d like to thank everyone for listening. This is where I want to say, please subscribe, like the if you like the podcast, please click the like button and keep your databases out there healthy. And have a great day.