- Host: Steve Stedman / Mitchell Glasscock
- Recording Date: February 26, 2025
- Topic: New Features for Database Health Monitor
- Download Database Health Monitor: Databasehealth.com
- Listen on Spotify
- Check out our new Schema Drift tool: Schemadrift.com
Stedman SQL Podcast Sn 2 Ep 9 New Features for Database Health Monitor
Steve Stedman and Mitchell Glasscock discuss new features in the Database Health Monitor, including six new deadlock reports at both instance and database levels. They explain deadlock scenarios, the concept of deadlock victims and winners, and how the tool helps identify and track deadlocks. They also introduce a new email alerting tool for notifying users of critical issues, even when the tool is not open. Additionally, they highlight the inclusion of Schema Drift comparison, SQL Server health reports, and replication status reports. The episode concludes with a sneak peek at upcoming features and an announcement for the 10-year anniversary of Stedman Solutions.
Podcast Transcript
Steve Stedman
Welcome everyone to this week’s episode of The Stedman SQLPodcast. We are on season two, episode number nine, and I’m your host, Steve Stedman, and today, Mitchell Glascock joins us to discuss on the new features and Database Health Monitor. Welcome Mitch. If you missed last week’s episode, please check out season two episode eight, where Derrick Bovenkamp and I discussed the 30 minute analysis of a SQL Server. And it’s a cool episode to find out. Kind of how you get in and quickly understand what’s going on with a new SQL server available at here’s the shortcut. URL is stedman.us/podcast YouTube. Welcome everyone. This week’s topic is new features in Database Health Monitor. All right? Mitch, what do we have to show today?
Mitchell Glasscock
Sweet, so we have a lot. In the past month, we have added a lot of features to database health, a lot of exciting new features, a lot of exciting new reports. So let’s jump straight into it. So one of the big things that we added was new deadlock tracking. And Steve, do you want to go over kind of how we’re doing that deadlock tracking that should start every instance.
Steve Stedman
Let me start with why we’re doing it, and then we’ll go on to the how. So with Database Health Monitor, we’re really trying to be the entire monitoring tool that we need for everything that our clients and Database Health Monitor users would ever come across with SQL Server, and a couple weeks ago, I was on a mentoring call with one of our clients, a relatively new client, and we had some deadlock issues, and we didn’t have the right reporting and Database Health Monitor to just completely pinpoint and show them what was going on. So then we came back for our next mentoring session, and they said, Oh, we downloaded this competitors product and tried it out, and it was perfect. It showed us everything we needed to know about deadlocks. It showed us where the problem was. Well, at that point, I realized that we were missing a significant piece there. And what really got me going was, well, they tried a competitor’s product, which is always kind of tricky, and we want to make sure that we don’t, you don’t need a competitor’s product. So rather than going and looking at that competitor’s product and just doing a copy or whatever, because that’s kind of tacky, what I did was I went and investigated, looked into what are all the things we can do with deadlock reporting. When you get a deadlock on your SQL Server. What are all the best ways that we can report on that? And we came up with, I think, five or six different deadlock reports that show different perspectives on this. Yeah.
Mitchell Glasscock
So we have six new deadlock reports, and they’re spread across both the instance and database level.
Steve Stedman
Why don’t we start at the instance level and take a look at one of those deadlock reports?
Let’s just start with deadlocks.
Steve Stedman
So this one is just listing all the deadlocks that have occurred on the SQL Server. And here, this is a test server, and I created some deadlocks on purpose just to be able to track this, and this is just reverse chronological order from the top down of the most recent deadlocks going back in time. And if we double click on one of those, we can get more details about specifically what was happening. Now, one of the things we should explain before we go too far here is what is a deadlock now we have locking and blocking that we’ve reported on for a long time in Database Health Monitor. And what that is it’s one query is jamming up another query for some amount of time, and then they eventually clearly go through. But what a deadlock is, is a deadlock is a situation where one query is waiting on another query and that other query is waiting on the original query, and whatever happens, there’s no way that they’re going to clear it, meaning it’s sort of like two people have gotten trying to get into the same parking spot, and neither one’s willing to give up. So with this, you can see in this example here, one transaction is updating table A and setting some things and then waiting for delay, and the other one and then updating Table B, and the other transaction is doing the opposite order of updating Table B and then updating Table A. So that gets set in a situation where the transaction on the left is waiting for table B to be available, and the transaction on the right is waiting for table A to be available, and that’s never going to happen because they’re stuck waiting on each other. So what SQL Server does? It says, Okay, we’ve detected there’s a deadlock here that will never resolve itself. So we’re going to cancel one of them and terminate one of them. And the terminology around that is a deadlock victim. And if you’ve ever run a query, SQL server might throw. An error message that when a deadlock situation that says You’ve been chosen as the deadlock victim. Now they way they phrase that chosen sounds like kind of an exciting thing, but it’s not good. It basically says whatever was happening with your transaction was completely imported and rolled back so the winner transaction can go through when the deadlock SQL Server decides, well, it’s better to have one transaction succeed than to fail both of them. There’s a lot of different strategy around preventing and reducing deadlocks. We’re not going to go on that today. Today, we’re just going to show how do we find them. So in this case, from this dialog the deadlock advisor, we can see the problem here is between table A and Table B on this database. It’s called deadlock TestDB. I think there was a database called Big Stuff that was just a test database that had similar tables I was testing against. And we’ll see some other dead blocks on that, but we can see down at the bottom what host name the blocking was coming from. We can see what application, in this case, it was coming from, Management Studio. They’re both in READ COMMITTED mode, and you can see the session ID and the transaction counts there as well. So pretty much everything you need to do to figure out what’s going on with this deadlock, how to track it down. And next is you have to figure out how to fix it, which is, could be a whole other hour long podcast, right?
Mitchell Glasscock
You also mentioned we also classify the victim and the winner in the advisor so you can keep an eye out on which one actually went through and which one canceled if you didn’t catch that Management Studio.
Steve Stedman
Yep. And with that, the thing is that this is the winner and the victim this time, but if this deadlocks again in 10 minutes, you might get a completely different order, depending on who got there first as to which one is the winner. We’re not always going to see the same one as the winner, right? So this one’s just a big list that shows all of them. We’re not all a certain amount of them going back in time. How about if we go to the deadlocks by database or deadlocks by hour, and take a look at those. I Okay, so what we’ve got here is the deadlocks by database, and with this, it’s showing that big stuff, database, which just happens to be a test database on our server, has had 161 deadlocks. The deadlock test DB has had six, and the query training February has had five. I think those five for you this morning mentioned, weren’t they? They were they were doing some doing some work there. But with this, what’s cool about it is you can double click on the database name down below and jump directly to that database. So when we jump to that database, this gives us the deadlock history for just this one database. It’s very similar to the overall server one, but it’s giving it you just for that one database, so that you can go and track it down. And there’s similar queries to what we were looking at just a minute ago on the deadlock advisor from here, why don’t we take a look? You hit the back button, that gives me the opportunity to point out our related links up top where we have other reports that are related. So we haven’t clicked on the deadlocks by hour. Why don’t we use that related link to go there? And oh, we’re gonna have to show more, because this deadlock work was all done a week or so ago, and you can see that on Sunday, February 16 and Monday, February 17, there were a lot of deadlocks going on here. And we can then take those dates and times and correlate them to the normal deadlocking history. And what what’s useful with this is going to be able to use this over time in order to be able to see how are we improving? Or how are we getting worse on the deadlocks?
Mitchell Glasscock
So, this is another one. This is the instance level overview. So this is the deadlocking reports from all your databases. But we’ve also included the deadlocks by hour as a database level report so that we can go and look, we should see a little bit less blocking. So there was, there was a little sprinkle of one and twos over in this past week, in some other databases. But we know that Big Stuff is not having any current blocking in the past week, it had a lot of blocking, like you said, Steve, on February 16 and 17th.
Steve Stedman
Yep, which was the days that we were working on those deadlocking reports the most.
Mitchell Glasscock
The last one would be the deadlocked objects.
Steve Stedman
Oh, look at that, table A and Table B, the same ones we were seeing showing up in the in the actual queries themselves. And it’s interesting, because they’re fairly balanced there, but we can see that, yeah, those are the two tables that we’re concerned with here for deadline if we were but if you had a bigger database that had 1000s of tables, you might see that a much bigger list of tables here that your deadline is occurring on, or you might just see the same two tables that are your whatever your two biggest tables are that are the. The biggest problem cause are there for deadlocks. But with all of this, the hope is that this will give everybody the detail to be able to see where are deadlocks happening, what’s going on with them, what tables are they happening on? What were the queries running those tables? And give you the opportunity to be able to go in and figure out how to fix that code, or how to improve it. Like I said, that’s a whole other session. We could talk for an hour about, probably, is just how to fix deadlines, for sure, but we’ve got how to find them.
Mitchell Glasscock
So that covers all of our deadlock reports that we’ve added for this latest update. So let’s move into the new email reporting tool or the email alerting tool.
Steve Stedman
Oh, this is awesome. This is something that I’ve wanted for a couple of years, and I have not had the time myself to take it on. And now that Mitch is full time developer and working on Database Health Monitor, it’s pretty awesome that he’s been able to get this one to get this one done.
Mitchell Glasscock
Yeah, I’m pretty excited about this. This is, this is a lot of good work that’s gone into this, and we hope that we can expand it in the future, just to get tons of alerts in there, a lot of user configurable alerts, and flush this tool out to be something really awesome. Do we want to give an overview of this.
Steve Stedman
Yeah, why don’t we just start by, yeah, I guess I can give a little bit of an overview. I mean, the whole idea here is that when you’re using Database Health Monitors, there’s a whole bunch of stuff that you can see or find or discover while you’re just poking around. Yeah. Okay, so basically, there’s a lot of stuff in Database Health Monitor that you can go and find and discover, but not all of it’s going to necessarily just jump out and say, Hey, there’s a problem here you need to be aware of right now. And that’s the idea behind the alerting system, is to catch things that may be happening when you’re not actually logged in and viewing Database Health Monitor and trigger an email so you can get a notice that there’s something that requires your attention, right?
Mitchell Glasscock
And that’s one of the big things, is you don’t have to have Database Health Monitor open to get these email alerts. As long as your SQL Server instance is running and your jobs are running in the background. If something triggers and you’ve configured it, it will send you an email and you’ll be notified.
Mitchell Glasscock
So the email alerting system to access the email alerting system, you’re going to right click on any instance that you have and go to configure email alerting system. It will pull up this dialog box and it shows you the nine alert types that we currently have running, or these are the alert types that are shipped with our first version. The alert ID is a predetermined alert ID that we’ve given to these ones. The alert type is a short description. Alert running shows an X or a check, the X indicates that it’s not currently running, meaning that it will never be alerted on and if it’s checked, that means it is currently running. It’s going into the background job. It’s being ran every minute. And if it ever does hit, then it will send the email alert. The alert description is a longer description that determine or that shows what the alert is doing in the background and what it’s checking for. And then we have the configure button which you use. So let’s do the CPU usage. This brings up a new dialog box, and this is how you configure the alert. So as I said before, the alert ID is a predetermined number that is not editable. The time between checks is how often you want the T SQL for this alert to be ran. So the background job runs every minute. But if I wanted the CPU usage to be checked every five minutes, I would set it to five, or if I want it to be checked every 30 minutes, change it to 30. So that’s one of the configuration values that you can change.
Steve Stedman
Just a question on that. Mitchell, yep, on the alert interval. I mean, these alerts, they all run pretty quick, right? I mean, none of them are going to put as much load on the system, so running them frequently should not ever be a problem. Should it?
Mitchell Glasscock
No, it’s just a matter of how often you determine it to be necessary to be ran. All the alerts are very lightweight. They should not cause any slowdown at all. Okay, great. Thank you. Yep, the time between Alerts is how often the email actually gets sent. So if I have this set to be checked every five minutes, and it gets triggered, and it stays triggered for an hour, this will alert every 10 minutes throughout that hour. So it’ll send me six emails instead of 12 throughout the hour. If I set that to five, so the time between alerts cannot be set lower than the time between checks. We have made this so that you can’t actually enable the alert. If you do try and set the time between alerts lower than the time between checks, it’ll help walk you through the process. If you ever try and do something that it won’t like.
Steve Stedman
Let’s say we have the time between check set at five and the time between alerts set to 30. Then that would mean that if we are in an alert state, I will get an email every 30 minutes just reminding me that, hey, it’s still in an alert state. Right, correct. Okay, what happens? And on this one, we’re looking at CPU usage. What happens if the CPU usage changes in that period? Do we get any alert that it changed? Or is it just simply the every 30 minute alert?
Mitchell Glasscock
so the threshold for this CPU usage right now is anything greater than 80% so if my CPU is cranking at 80% or higher, it’s going to alert. If so, the first time it alerts is as soon as it goes over that threshold, because it’s checking every five minutes. If that threshold changes, either goes above what it was, if it was at 81% and it goes to 85% it’s going to send another email. It won’t wait for the 30 minutes. It will will send another email saying that the threshold has changed and it’s gone higher or lower, whatever it has gone. If the threshold stays the same, it’ll wait for that 30 minutes. But if the threshold keeps creeping up, it will, if it keeps creeping up or down, it will keep sending emails saying, like, Hey, this is going up. This. This keeps going up. You need to look at this.
Steve Stedman
Yeah, that’s great, because if I get an alert that says it’s at 99% and then I’m five minutes later, it says it’s at 92% and then five minutes after that, it says it’s 85% Well, I know things are improving at that point, right?
Mitchell Glasscock
And then it will also send an alert. Since it’s checking every five minutes, if it goes back down below 80, it will send the alert. If the CPU usage goes down below 80, the all clear will stop, yep, it will send an all clear email. There and it will stop the alerting at that goes into the threshold greater than these. These two determine when the email alert is sent. Pretty much every single alert can be set with a threshold greater than and or a threshold less than so if you know that your normal operating range of CPU is somewhere between 30 to 60% you can set this to notify you when your CPU usage goes above 70% or less than 20% and we’ll send you an email alert whether it goes above or below at that point.
Steve Stedman
Okay, great.
Mitchell Glasscock
And then the next is the email addresses. This is you can add as many email addresses as you want. Just simply type in your email, fake email, and just keep adding them. You can add as many here as you determine, and at that point, once you’re ready to enable it, you just hit Enable Alert, and you’re all done. And now this one is running. So that’s the the configuration for these. The only difference some of them have these chosen period of time keywords in them. So for login attempts, we’ll look at that. This alert actually needs to look back a set amount of minutes. So for login attempts, if I wanted to know how many, or if I wanted to get an email alert if there have been more than four failed login attempts over the past 30 minutes, that’s where I set this configuration value. So the alert needs to look back 30 minutes to determine how many login attempts have failed. So this will reach into your error log and look for the specific login failures.
Steve Stedman
Okay. So then, for this one to work, since you’re looking at the error log, we would need to make sure that the SQL server setting for logging failed logins is actually turned on Correct, correct. Yep. Okay, got it, yep.
Mitchell Glasscock
And then same thing, you can set any email addresses that you want to send to, and then simply enable it. If you do, like I said, the dialog walks you through. So if I tried to enable this alert with an invalid number of minutes, it will tell you that you need to change that before actually enabling it.
Steve Stedman
One more question, if we go back to that dialog just before we move on to other areas here, I know this the first time you run this, this dialog looks a little bit different. I know we’re not seeing that here, but can you describe like, if what yeah, do with the email profile to begin with? So
Mitchell Glasscock
I think we have one or two that is not configured yet, one of the other instances, yeah, let me look. There we go. So the first time that this is not actually the first one that you might be seeing the first time that you open it. If you haven’t configured a SQL email profile or an email server profile, it’s going to prompt you to go and create one. And you need to go into SQL Server Management Studio and create that emailing profiler. We do have a video, there will be a link to the video on how to set that up in this dialog. So if you need help walking through that, once you do have the email profile configured, you can open this back up and you’re prompted to select the email profile that you want to use at that point to send the emails. Once you do that, it will build out the list for you, and at that point you’re ready to go. Okay,
Steve Stedman
great, great. I know that’s one of the other one of the things that with a lot of email tools and SQL Server, they don’t always do a great job of walking you through the process, and I think what we’ve got there does that pretty well. Yeah. The
Mitchell Glasscock
other thing that we’ve added is a new instance report, which is the email alert log, and this will show a history of all the alerts that have been triggered. So here are a bunch of alerts on my instance that I’ve been triggered. This alert log shows every single time the alert was hit. So for the disk space warning, it was checking every couple of minutes, and it logged some hits between the 10th and the 11th and. And these are all recorded, so you can go through and read the warning messages, the alert ID, the tag that was set for it. So these are my D drives, and you can read all the information that was ever logged by the system at that point. With that, it also goes with the alert statuses. This is when an alert has been triggered. It will show you the status and the last warning message. It’s similar to the log, but it doesn’t show every single warning message through here. It just shows it per the combination of alert ID and tag.
Steve Stedman
If we were at low disk space somewhere right now, you’d see a not, okay, there of something, right? Yeah,
Mitchell Glasscock
the alert status would be red. It would say alerting. And you would know that it would be trying to send emails at that point. And then you can also reach the configuration through this instance report as well.
Steve Stedman
Okay, so if somebody’s watching this and they don’t see any of these alert links in here, and there may be the haven’t updated to the latest version of database health monitor, but I think what we need to tell people is that you need to upgrade to the latest version. Currently, that’s 1118, in order to get all of these features. And this will continue to evolve over time, with more alerts and things like that. But if you’re running older versions, all of these features are not there, so
Mitchell Glasscock
upgrade to version 1118, now available on Stedman solutions.com to get all of these new features and keep an eye out for the new features that we’re looking to add in the coming months.
Steve Stedman
So do you have any other ideas, Mitch, of what other alert types might be coming in the future here, beyond what you have today.
Mitchell Glasscock
We have a list of some that we’re working on, going through a lot of the stuff that we’ve put into the quick scan, we also want to configure in the email alerting, especially a lot of the more pertinent stuff that might cause database failures. So keep an eye out. We’re definitely going to be pushing new alerts to the system in the next few updates.
Steve Stedman
One of them that I can think of that we just got in was the deadlock reporting we talked about a few minutes ago, that it would be great to be able to tie the deadlock alerting into here as well, so when a deadlock occurs, that the server can take out an email. So for sure, hopefully the next version will have that one.
Steve Stedman
All right. Well, that wraps up the alerting. So then what’s next?
Mitchell Glasscock
We have started to include Schema Drift with every install of Database Health Monitor, and it now has easy access through Database Health Monitor. Did you want to go into that?
Steve Stedman
Sure. Let’s take a look at that. Alrighty. So the loaded question, what is Schema Drift? Well, Schema Drift is the comparison tool to take two SQL Server databases and compare all of the different schema related objects in those databases. Now, it’s not going to go through and compare what data is in any table, but it’s going to go through and compare the structure of those tables. It’s going to go through and compare the structure or all the triggers that are there, all your foreign keys, all your functions, indexes, message types, and you can see the list down at the bottom there of all the things that are supported. And basically it’ll go through and show you what’s different. What’s different between a given SQL Server database and another one. Do we want to do a comparison here? Yeah, I can do a quick one. So what Mitch is doing is he’s going out and connecting to he’s just connected to his local development desktop, and he’s picked two different databases, testing and Adventure Works, data warehouse one, and then it hits the pair button, and all those check boxes down below determine what’s going to be scanned. If you wanted to scan less things, you could uncheck a few of them, but it’s done. It’s it’s run the comparison now, and we’ve got some tabs showing what’s identical between them. Okay, a few standard things that are here, that are. Go between them. What’s different? Well, so there’s a couple things here. Different implies that it exists on both sides, and it actually is. This is different in some way. Now only in source and only in destination. Yeah, those are different, but they’re just in one on one side or another. So if we look at only in source, and I don’t know if you click on something like that, large table, DPO, large table, little it’s a table there. It’ll show you. Here’s the schema for that table. Here’s the structure for that table. And if you wanted to then take it and migrate it to the other database, you could just right click on that table, on the list up top. Well, you can copy SQL from down below, or you could right click and say, add this table to the other database, and it will just take that create script and run it on the other database and move it over. You’ve also got the ability to right click with a copy all scripts, or copy all from grid, or copy diff only in source. You’ve got three different options there to copy either the source control or the source of those, or copy an entire list. It can be then pasted and used in like Excel or something like that, right?
Mitchell Glasscock
Then, similar to only in source, only in destination, shows the other side of what is only what schema is only on the other side. And this would be what’s in the adventure work state of warehouse, yep.
Steve Stedman
So this is a really great tool to use when you’re trying to figure out maybe what’s different between a development database and a test or test in production, or things like that. Or if maybe you’ve got some vendor supplied product and you’ve got a test or two different databases that they’ve provided, to figure out what’s different between those and see what’s new things like that included as part of Database Health Monitor. And it’s there for everyone, for you to use. While we have it in beta, eventually, we’ll probably spin it off as a standalone product on its own, and you can take a look at SchemaDrift.com for more information on that, if you’d like.
Mitchell Glasscock
Next up is a report that should be pretty exciting to you, Steve, this is the, let’s see the SQL Server health report.
Steve Stedman
Oh, yeah, yeah, this one, gosh, when this was added, like almost at right after our January release. Yeah, and go ahead and run that. And what it does is it goes through and scans a bunch of the instance level reports and puts this all together in one place. This is something that we do a lot of when we’re working on performance and health assessments, where we’re trying to go and capture the pertinent information. And once it puts it puts it all together in one big list, you can then export it to Word or export it as HTML and images, depending on how you want to use it. And if you hit it export to Word, it’ll give you a word document that contains all of these different instance level reports. If you just want to scroll up through this list, Mitch, we can just take a quick list. Yeah, we’ll go there. But this is something that we used to have to do, where we have to go and right click and copy an image, and then go and right click and copy something else and put this all together. Instead, we just throw it all into one big document, and then when we’re presenting findings like, well, look at this. We’ve got blocking on, gosh, that’s too small for my eyes on Thursday or Tuesday, Thursday, yeah, if we exported it to a Word document, and he’ll zoom in a little bit and see it better, but we’d be able to go through and find the things that are pertinent here that we need to talk about, and then kind of delete out those things that aren’t important. It’s also good if you’re in a position that you need to, like present to management what the overall health and status of your SQL Server is. It just includes a lot of stuff here. And again, this is much as test database. We’ve got some wacky different databases and CPU loads and stuff that would be what you’re seeing in production, but it gives you a really good idea of kind of what’s going on with the system and all these different things that are part of our instance level reports. One of the things that we missed in the February release was this report did not include the new deadlock reports in it. But once the next update is out, probably the March update, when you run this, it’ll also include the deadlocking information in it now as well. This feature was something that and being able to export it to an actual word file was something that I had wanted to do for a very long time, and I just hadn’t had the time myself until Mitch got involved and took over some of the other database self monitor development. So it would free me up to dive in and take on something like this. So thanks Mitch for that one.
Mitchell Glasscock
Of course. So next we have a couple new instance reports. These are some pretty cool reports. The first one is the replication publishers and subscribers. I believe 2022 has some replication on it.
Steve Stedman
So if you’ve worked with. Application. Most people I know that work with replication really despise the SQL Server Management Studio interface. For being able to go see what’s happening with replication, you need to go right click and go to replication monitor, click through all these different different publish publications and subscriptions and really to try to figure out what’s going on with the replication, we do a lot of replication work with our clients. And this is one that I built as a way, well, Mitch built it, but I kind of designed the upfront part. We worked on it together. And to go, be able to go and say, for a client that’s not maybe 20 or 30 replication publishers and subscribers, and to be able to quickly go and look and see, is this healthy? And we’re all we’ve only got two on the test server here. But if you look at this report and you scroll over to the right, one of the things you’ll see in there is the last sync so that one February 26 2025 at 10:25am, wow, that’s like two minutes ago. So we know that one is still sinking, and the one below it has no last sync date, so we don’t know on that one necessarily. And then this will report both on Merge and transactional replication, kind of give you the status of what’s going on there. We do a lot with merge replication, and this is incredibly valuable to be able to just take a quick look and say merge replication is working good on any given client, alrighty.
Mitchell Glasscock
And then the last instruments report that we added for this last release is the new disk latency by hour.
Steve Stedman
So George worked on this one, I think didn’t he? He did, yeah, so he’s not on the call today, so it’s going to be you or I talking about this one. So what disk latency is, is really kind of how much of a delay or how slow it is to get to the disk for specific types of events. And we’re using our standard by our reporting here, you can hit show, or maybe a couple times. We’ll zoom out a little bit, we can see some bigger ranges here. And what we’re really looking for is to understand, is our disk overwhelmed, is, is disc the problem when we’re looking at issues? And it might be that, well, gosh, on these specific dates, that we’ve got a higher latency, there was maybe a lot more load being run on a SQL Server, or maybe, I mean, there could be any number of things. And what we can do is we can look at different drive letters. I don’t know. This one has an E drive. They also have a T Drive, which happens to be where Temp DB is. So all this IO is associated with Temp DB because it’s on the T Drive. And then from there, we can look at read or write latency and determine are we more bound by reading, or are we more bound by writing? And that can help sometimes point out IO related issues might be that you’re looking at a specific disc and realizing, wow, this disc is has a lot more latency, or it’s a lot slower to access than what we thought it would be. And could be an indication that maybe things aren’t configured correctly, or you need a faster drive, or something like that, right?
Mitchell Glasscock
And just from looking at this, it looks like our reads are definitely a little bit more slowed on this system.
Steve Stedman
Yeah, and this specific test server is a virtual machine running with an ice SCSI attached hard drive, our virtual drive that is happens to be where all of our data files are. So that is not the optimal and running against a small Synology sand here. That’s not exactly a super high performance one that I have at my home office. So not exactly an optimal production environment, but it’s a great test environment, different points that we hit, different latency levels here.
Mitchell Glasscock
So that covers all of our instance reports. One new feature that we also added in this last release was these real time charts are now reconfigurable, so you’re able to order the charts in a way that you deem fit. If you’d rather see plan cash up at the top. It’s easy to just move it up a couple times, and every time that you log back in to Database Health Monitor, you will be able to see plan cash as your number one report here. So this is a little bit of ease of use, a little bit more user friendly, and we wanted to give people more control over the layout of their dashboards.
Steve Stedman
So can I just hide a report as well if I want to?
Mitchell Glasscock
We don’t have hiding reports built in yet, but that is a feature that is in the works and should be seen soon in the next release.
Steve Stedman
Oh, there’s the pre release info, sneak peeks. Okay, great. So what’s next?
Mitchell Glasscock
Let’s see here. So we have just shuffled some advisors around. Let’s look at 2022, and I know that there’s active. Are queries going on in here? Let’s look no active queries, but there should be blocking, no, no blocking in that one real time.
Steve Stedman
Oh, the blocking was only occurring on that one when you were trying to do stuff with.
Mitchell Glasscock
So we’ve added new advisors to the active queries and blocking reports that now allow you to kill the session inside a Database Health Monitor, instead of just looking at it in here and then going over to your management studio, you can now double click on any blocking and kill that session ID remote.
Steve Stedman
And the other thing I think that was added at the instance level blocking report was the ability to right click and copy all the session IDs that were in the blocking list so that we take those and copy and paste those and, like, kill 10 of them at once if you need to. Yeah, like, if you’re having serious blocking scenarios, hopefully you don’t get to that point, but if you do have serious blocking scenarios, that’s one of those things that you do have to occasionally do. The other one is we have the related links now on the instance level reports.
Mitchell Glasscock
So we kind of showed these during the deadlock stuff, but almost every instance report should have these, or every instance report does have these related links, and it should make navigation between your reports a lot easier. So if you’re looking at something and you want kind of a different view of it, or a report that might be similar, that might show you a little bit more information, we now put these related links to be able to jump between all of these.
Steve Stedman
So the inspiration for this originally came from the blocking queries report and the what is active report. And that’s one of those things that when people say there’s something going on, there’s load going on in my system, I want to understand what’s happening with it. Right now, I’ll typically use the blocking queries report and the what is active report, and flip back and forth between those to kind of catch what the problem areas might be. And before, used to have to hit the back button or go back to the main instance level report and then click on the other report, and it was kind of a pain to jump between those where. Now it’s just as quickly as just clicking a link at the top with those related links up there and jump from one to the other, and it’s really a heck of a lot faster now, pre release info right now this is only available on the instance level reports, but in the next update, the code is already done adding this into almost all of the database level reports too. Yeah. So if you go down on a database and open a report down there, related links won’t be there now, but in the next update, they will be there. So who knows, by the time this podcast goes out. We might be close, because we’re recording a couple weeks at a time. We might be close to that next update being there. Sweet. So right now, look out for Yeah. So version 1118 is the current version. The next version we release after 1118 we’ll have the database level links there too. Yeah. So if there’s no other items on your list, Mitch, I wanted to take a look at real quick. Can you just jump into the quick scan report? Yep. So in the previous from mine, yeah, that one has lots of good stuff in there, yeah. So in the previous episodes, we’ve talked about a lot of things where Mitch and George added different Quick Scan checks over the top, over time, but we never really kind of talked about big picture. What is the quick scan report? And so I just want to take a minute to talk about that. So what the quick scan report is is a quick way to scan your entire SQL Server and look for just over 200 different check items that we’ve come across over the years that relate to performance issues or stability issues or reliability issues, and every time we come up, or most every time we come across something when we’re working with different clients, and go, Wow, we should have been notified of that, or we it’d be great if we can catch that early next time we add them into the quick scan report so that they’re they are there in order to be able to know about in the future. So here, like you have no operators configured on line, 20 is going to tell you, Well, you’re not able to send email from SQL Server Agent. For instance, user database is on the C drive. Well, this is your development server, so that’s okay, but on a production server, that can be a problem, and then scrolling down through the list, there’s just a ton of different checks here. And when we work on a SQL Server regularly, we try and get this list to as close to zero items in it as possible, meaning we’ve wiped out or taken care of all of those possible issues or concerns.
Mitchell Glasscock
Yeah and when we look at someone’s instance for the first time, whether it’s for a client or we’re if we’re doing an assessment, this is one of the first things that we look at, and that way, we can determine the current health of their system and the next steps that we need to take to improve their systems and environment.
Steve Stedman
And if you watched last week’s episode where Derrick and I talked about 30 minute analysis of a SQL Server, you may remember the Quick Scan is one of those things we talked about in that process. Okay, great. Well, anything you want to add on any future stuff, Mitch, at this point?
Mitchell Glasscock
I don’t think I have anything else to add. Keep your eye out for some more alerts. I’m definitely excited to keep adding those. I don’t I think we went over quite a few sneak peeks for this one. So I’m definitely looking forward to the next update, just as much as I was looking forward to this update getting out.
Steve Stedman
Yep, and I know the one thing, I mean, we’re what, like, 14 years into the database, health monitor now, and a lot of it was just me trying to squeeze in spare time here and there to get it done. But the fact that you’ve been full time on this now for a while, it’s got a big, big impact on the amount of features and things that we’re able to add and improve over for sure.
Mitchell Glasscock
It’s definitely been an exciting project to work on.
Steve Stedman
Well, let’s see. I think that wraps up this week’s episode. Thanks for watching or listening today. We hope that you’ve enjoyed the episode. Join us next week, we’re going to have a 10 years at Stedman solutions anniversary episode where we’ll have a special anniversary gift for everyone to give away to all of our listeners. And I know the 10 year anniversary gift is generally diamond related. No, we will not be giving away diamonds next week, but it will be almost as good. And if you want more episodes, take a look at our YouTube podcast channel with this episode and all of our previous episodes. And eventually it’ll be future episodes too. At stedman.us/podcast YouTube, or you can go to YouTube, find Steve Stedman and look at our podcast under there. This is season two, episode nine, and thanks for watching 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 of future videos that we create.