Three monthly checks for managing database capacity
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in November 2011
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation