Tracking Server IO with Database Health Monitor

Follow along as Steve teaches you how to find and track IO on your SQL Server. We’ll look at IO by database, IO by drive and IO by hour.

Transcription of Video:

Welcome to this short video on database health monitor and analyzing IO. With what you learned in this lesson, you’ll be able to go and find which databases are using up the most IO out of your disk systems, as well as what times of the day are the biggest IO issues, or even what days of the week might have the most IO related issues. Now, I’m here at the server overview panel for one of my test servers here, we have a couple of reports, one of them is in the instance report here, the IO by database. If we click on that report, we can see and of course, that’s my test database. So it’s not really very accurate compared to what a real production database would be. But we have DB health history showing a lot of IO, the QuickScan, archive QuickScan history, Temp DB, both history and distribution are the top IO databases. And then things below that are pretty low. But these are based off of a percentage. I’m gonna use the backspace key here and jump over to the IO by drive. And here we can see that the C Drive has a majority of the IO. and the E drive has very little IO by comparison. Of course, this is my test server. So I don’t have all the proper like data log temp, TB and drives like that setup. So everything’s really hitting the C drive in this case. Okay, so now the question comes that we know from this chart that the C Drive has most of the IO. And we know from this chart, that DB health history and QuickScan, archive and QuickScan history are taking up a fair amount of IO. But how do we know like what’s going on on a daily basis with that. So let’s take a look here, what I’ve got on the left side in the tree view under the SQL Server Name, we’re going to expand the database health history and expand historic and under that there’s an IO by our report.

This was one that was added recently. So you always want to make sure that you’re keeping up to date on your database health monitor updates. If you’re not seeing this report here, it might be that you’re a little bit out of date. But as long as you have database health for monitor version 996 or newer, I should be able to see this report. So when I click on database health history, historic IO by hour, we can see that the columns are the days the rows are the hours. And we can see that day by day. This database has a lot of IO going, I mean a lot, it’s all relative. But for this little test server, it’s a lot compared to the others. And we can see that on busy times you’re getting about 3.1 megabit per second. And on slow times, we’re getting about 2.0 2.5 megabit per second. Part of the reason for this is this is a test database. And it’s got a whole lot of test data being processed in there. And more than just what database health history would normally be doing. If we go and look at like our email marketing database here and we look at the IO by hour, we can see that this one has very light IO, we can see that it’s about point O one megabit per second, and on slow times a few zeros. But then the high points are point six megabit per second, keep in mind that it’s a heatmap here on and color coded based from like white and blue up to red. Where red doesn’t mean it’s an issue, it just means that those are the points with the highest IO over time. And the white or the blue are the points with the lowest IO over time. And kind of the yellows and the greens are in the middle there between going from low to high.

Take a look at another one here quick scan history IO by hour we can see that this database, this is interesting. And that we can see bands going across it, where every day at 1am, or in the one an hour, it’s a much higher load than other hours in the night. And then we see, again, at the 1pm hour, there’s a higher load. So this would be indicative of a job that’s maybe running every 12 hours to do some type of work specifically, and would want to go in and try and track that down. If you’re using network attached storage, or even if it’s just regular old hard drives on a on a server, I mean, one of the bigger bottlenecks that you run into with SQL Server is oftentimes IO. And when you have a lot of load going on, or maybe you don’t have enough memory, your drives get slammed over time. And this will help you figure out like where the problem areas are. And sometimes, I mean, let’s just pretend that this 1am and 1pm slot was not 2.6 megabytes or 3.1 megabytes per second, and then it was much higher than that. And that was causing a problem. Well, we could use this to track down what’s going on at those times. And then figure out are there jobs that need to be moved around? A lot of the times what we see here is that people will schedule backups and check DB and index rebuilding and statistics, maintenance and other warehousing jobs and all that kind of stuff in the middle of the night. And you end up with sometimes like an hour or two where the server just can’t keep up with IO depending on the configuration and this will help show those times and will give you a good way to help distribute that out over time.

So really three different places. We’re looking here for server IO to figure out what’s going on. We have the IO by database, we have the IO by drive and then we have the IO by hour underneath each of the specific databases under the historic monitoring. To use this IO by hour, you have to have the historic monitoring turned on. As long as you have historic monitoring with these bar charts, showing up with historic weights at the top, then you should be recording that. As long as you’re on database health monitor version 996 or newer. You should have that IO by hour report showing up. This is one of those things that came from working with specific customers where we were running into some server IO issues during the night. And these types of reports were built specifically to help track down and find those problem areas. This wraps up a quick demo on some ways to do server IO related reporting using database health monitor. Alright, I’m Steve Stedman. Have a great day.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!