SQL University – Capacity Planning Week

Welcome back to SQL University. This week the topic is capacity planning. As your DBA Coach it is my role to help you understand the different levels for the subject at hand. Capacity planning is a subject that applies to just about everything in life; how much money is left in your bank account, how much wood can you store for winter, or how much bacon can you eat for breakfast.

The term “capacity planning” is really just another way of saying “managing resources”. To me it typically meant that I needed to make certain there was enough disk space for the databases. This is a result of my being asked to provide a DASD report for my databases; such a report is only concerned with disk space requirements and not about CPU or memory. But as we enter into a brave new virtual world you should be able to see that you need to make certain you can effectively manage (and plan) for a variety of resource needs.

Enough of the intro, let’s roll.

tempdb

At this level you need to be aware of the fact that there is not an infinite amount of resources for your server. Oh, sure, you could always go buy more disk, more memory, or more CPUs, right? No, not always, because each of those items have limitations based upon your server type. At some point each one of those could be at a maximum. The only thing you need to do at this level is to be aware that such limitation exist.

That means if you decide to open SSMS and alter a table or index and two hours later you get an error message saying there isn’t enough room on the disk your first reaction shouldn’t be to blame someone else for there not being enough space for you to play mad scientist with that database. And as a DBA you need to be aware that people only shove data into their databases, they hardly ever delete anything. I swear they should make a television show about “data hoarders”, similar to those shows about people who collect everything and then end up crushed to death by a pile of magazines. Anyway, people collect data and hardly ever purge or archive, which means that as a DBA you need to do your best to make certain they have enough disk.

msdb

At this level you need to actively be taking measurements of your environment that answer some basic questions:

• How many servers do I administer?
• How many databases on those instances (count system databases, ’cause if they fail you will need to fix them as well, right?)
• How much disk space is needed for your data, logs, and backups?
• How much free space is there inside of your data files?

And you need to take these measurements on a regular basis, most likely monthly but you are free to do it as often as you like just as long as you are doing them. And you can add in measurements for memory and CPU utilization if you’d like right now, but I don’t want to overwhelm you with too much too soon. Besides, we talk about those later. Now, why are you doing all this?

Because without doing this you will not have any idea if any of your servers are about to run out of disk space. Well, OK, we already covered the fact that people don’t purge their data, so we do know that your servers are slowly filling with data. That’s fine, then these measurements will help you to understand just how fast they are filling up with data. And that, in turn, allows for you to put on your proactive hat and take corrective actions before any problems arise.

model

Capacity planning is a lot like loading your dishwasher. When it is empty and you only have a few dishes to put into it you arrange them in such a way that you think it will work best for the next dishes that get placed after the next meal. Except that someone decides to use some odd-shaped dish or pan and this means that you either need to rearrange the dishes right now or you just shove it in there for the time being and worry about rearranging it later. And then later comes and your spouse complains that you don’t know how to load the dishes but they have no idea the dishwasher has been a work in progress for the past three days and that you meant to rearrange things but never had time.

And that is exactly how everything in your enterprise works. People shove things into places, sometimes in the right places, but almost never in the correct order because no one ever knows the final view of the puzzle because the picture keeps changing. So you always try to get 90% of the way there and hope that is good enough.

If you want to be at the model level then you might as well focus on your dishwasher at home. If you can go for three months without having your spouse criticize you then you should be in good shape at work. For capacity planning that means you take your awareness, your measurements, and your experience and you use them to help you get the puzzle about 90% finished, and that you know that it can never be completely finished because things always change. Always.

master

At the master level you need to be thinking in more dimensions than just disk. You need to be thinking of things such as memory, CPU utilization, and even staffing requirements (it takes people to do the work still, right?) And once you start thinking in these extra dimensions you will be able to to venture into the world of virtualization, where all of these measurements are necessary in order to help organize and lead your enterprise into a virtual world.

But you can’t get here unless you have those measurements in place, and you can’t get here if you spend all your energy trying to make everything 100% perfect. Make your best guess based upon analysis of the facts at hand and just accept the fact that someone will dump 10GB of data in your lap one night and cause all of your planning to be wasted. It will happen. Often, actually.

resourcedb

I don’t have any links to share on this, unless you want me to link to Wikipedia. It’s not as if there are any DBAs that specialize in capacity planning (well, none that I am aware of at the time of this post going live). I am certain that many of my colleagues perform capacity planning, but I don’t see a lot of blog posts on the subject and it’s not like people are writing books solely focused on the subject (say…that gives me an idea…)

So, what to give you for a resource? How about ITIL? It’s better than a sleeping pill. And it gives you an idea of everything that goes into a proper capacity planning document (hint: it’s a lot).

Just start getting those measurements, without them you are just guessing as to what capacity you will need.