In my time as a “reluctant DBA,” I managed database capacity on what I called an “exception basis.” If someone took exception to the way things were working -- like, they weren't -- I took a look.
But I’m trying to be more proactive these days, and that means looking at a smoothly running SQL Server system from time to time to make sure it’s going to keep running. I watch capacity the closest, because if capacity diminishes, more people take exceptions. With that in mind, here are the three questions I ask at the end of each month.
Are any databases getting full? A lot of folks will tell you that databases should have X% of free space. I don’t like that because some of my databases grow a lot in a month, while others sit with relatively little growth. Instead, I spend a few months charting database growth and coming up with a trend line.
I now make sure my databases have enough free space for at least three to four months of growth. When they start to near the two- or three-month mark, I’ll schedule a maintenance task to expand the database size. I hate allowing SQL Server to auto-grow the database. I usually leave the option turned on, but it has killed performance at critical times in the past, so I make sure it’s there just as a last-ditch option.
How’s performance? Another aspect of database capacity is the server’s capacity to support the workload my users throw at it. I’ll usually pull out Performance Monitor or another monitoring tool a few times a month, especially when I know users are hitting the databases especially hard. Our financial database, for example, works hard on the last few days of the month, when people are closing out the month or quarter.
If server performance starts nudging toward 70% on a regular basis -- that’s 70% of sustained CPU use, memory use, or network use -- it’s time to upgrade the server or move the databases to a server with more spare capacity. Spikes to 100% don’t bother me so long as they remain spikes; once the server stays at those higher levels, I know I’m in for trouble if I don’t address the impending database capacity issue.
What do backups look like? I have a limited window in which to make backups, so I have to look at how much I’m backing up and whether it’ll fit into each window. I’m responsible for a few databases that have ever-shrinking maintenance windows because they support 24/7 applications. In those cases, I’ve been gradually moving over to disk-to-disk “continuous backup” systems that actually back up changes at the disk block level as they happen. That’s created essentially infinite backup capacity; I don’t need to squeeze everything into a tape-related window.
Obviously, I have maintenance tasks that I (and by I, I mean SQL Server Agent) perform in addition to these checks, but these are the ones that help me make sure that SQL Server will continue to perform, and perform well, for the month ahead.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at www.ConcentratedTech.com.