I had to explain to a colleague why database autogrow and autoshrink were a bad idea, so I did, he laughed, but got it, and so I went back to my desk and hammered this out – no editing, just hammering…
DATABASES AND FRAT HOUSES
Okay folks, so here’s a basic example of why you want to have your database the right size to start with, and why setting things to “autogrow” and “autoshrink” is a bad idea.
Let’s say you go to – oh, say, the Local University, and you live in a frat house with 39 of your closest friends and associates.
Let’s also say that this frat house has parties on a rather regular basis, like, every Friday and Saturday night, and about 100 people show up.
Now if the house is big enough, you set up tables, snacks, “beverages”, and music, and people have a good time, in addition to getting plastered, puking on the lawn and so on – (but that’s outside the house, so we’ll deal with puking on the lawn in another issue.)
So far so good, right? People have places to congregate, dance, chat, do all that other stuff that happens at frat parties, and so on. No problem… The house is big enough, might be a little crowded sometimes, might be full of hot, sweaty bodies during the party, but basically, it’s a party, everyone’s having a good time, and no one will remember much of it on Saturday morning.
Except for those guys lying on the front lawn, but again, that’s another story.
This is what happens if you make your database big enough to handle the data you’re planning on putting into it. Your application has room to do its thing, SQL has room to function when weekly maintenance is needed, and it can grow, make temp tables, and make calculations. It’s got room to fold, spindle, and mutilate the data all it wants, and it will do so happily.
Now let’s assume, for a moment, that for some reason – it might even be a good one, you didn’t set it up that way. You want your database to be small, and full, and you don’t quite understand that a database can be very large (think big Frat House) yet be very empty (think same Frat house on a Wednesday morning when everyone’s supposed to be in class).
So… you wanted it to be small, to “save space”. Let’s see what happens if we do that to our frat boys.
It’s Monday, you’ve got a nice little frat house with room for 40 of you in there, and everything’s cozy, but you decide to have a party to celebrate the upcoming weekend. Well, come Friday night, people start coming in the door, and pretty soon, you know what? You’ve got a bunch of hot, sweaty bodies in the house, and there really isn’t enoughroom to do much of anything… You can’t dance, you can’t even get to the “beverages”.
What to do? Well, you decide to assign 10 of the young guys to make the place bigger. They run off to Lowe’s with the frat checkbook and wide eyes and they get some building materials to remodel the place and make it bigger. At first, 10 guys being gone gives you a little more room, but then they come back and start working. Soon there’s sawdust and sheetrock dust flying everywhere. The plaster dust starts caking on the sweat, and it looks a little weird. They don’t move nearly as well as they could – in fact, they kind of start slowing down a bit.
It gets, in a word, weird.
So, let’s get back to the party. Now there are so many people showing up (the line goes out to the street) that the guys who are remodeling are told to make more room. So, they take over more and more rooms in the house. Soon, even the bathroom has been remodeled to the point that there’s nothing in it so that people attending the party can fit in there. The guys doing the remodeling shut off the plumbing, take out the toilet, sink and shower. All you see is the floor, walls, and people in this room that used to be the bathroom because everything else has been removed to make room for the crowd. Problem is, when the bathroom is needed, there is nowhere to “go”. So, suddenly someone from the frat house is calling for Porta Potties to be brought and put in the yard so people can “do” what they need to “do”.
Meanwhile, your remodeling crew has been so busy, they are asking for help, so 10 frat brothers go outside to help to put up the Porta-Potties. (there’s a lot of Porta-Potties, play along with me here) Problem is, someone discovers that in addition to having no bathroom now, the guys doing the remodeling have taken out the kitchen, so someone needs to call a caterer to help with the food because there’s nowhere to prepare it anymore. They set up the food the yard, too – opposite the Porta-Potties.
Eventually someone gets the bright idea to borrow a room in the neighboring frat house and part of the party moves over there. Soon things start spiraling out of control next door while other things in the original house are grinding to a halt. The cops are called in at 3:00 AM and need to intervene in both houses.
It gets ugly.
Saturday morning comes around and the University decides that all the remnants of the party (beverage containers, Porta-Potties, etc) need to be removed. The city gets wind of what happened, and they figure out that all this remodeling was done without a permit, and order it to be undone.
So now you have a mess. Guys who aren’t anywhere near awake are trying to reinstall plumbing, rip out sheetrock, and clean up after themselves – and they’ve got better things to do – like sleep – or study, or something…The next week, they decide to have another party and the same thing happens again… House is too small. Remodeling happens because of the party. Next morning, the remodeling is ripped out. . –After awhile, you’ve got a bunch of guys who are pretty thrashed, and who aren’t really good for anything, whether it’s studies, frat stuff, or any other college event. You have a frat house that’s in shambles from being rebuilt so many times, the front yard is now mush. People can’t get into the house because of the mud in the front yard, the leftover construction materials scattered all over the place, and so on. The neighbors are more than a little peeved, and the problem list goes on.
Get the idea?
If you make your database too small to start with – and you need to do something like rebuild the indexes on a weekly basis (Friday night party) – it’s going to grow. Log file’s going to grow, likely the database file is going to grow, and SQL is going to have to spend resources doing the actual growing. When it’s doing that, it’s putting other stuff on hold, and if you have it set to auto-grow by percent – that gets even worse. You start off, with, say, a 1 gig database. You want it growing by 10%. So when it fills up, next time it will grow to 1.1 gig database. No problem. When this thing grows to a 100 gig database, when it decides to grow, it’s going to have to grow by 10% of 100 gigs, as in 10 gigs. If your file system is fragmented, SQL is going to have to find 10 gigs worth of space scattered about your drive, and grow into that.
(this is our frat boys going into the neighbors house, putting the Porta-Potties and caterer out in the yard)
If that file system is fragmented – now your SQL file is also fragmented because it grew into a bad situation. Now any index rebuilding/defragging you do may help a little bit from the SQL end, but the file system is still going to have to write/log activity to the hard drive, and if your data and/or log files are scattered all over the drive, your drive head’s going to be getting frequent flyer miles flying back and forth just trying to read a single database file.
Performance suffers because all that time spent by the drive head flying back and forth takes time. The disk subsystem has to wait to do the read and/or write till the disk comes around again if the drive head wasn’t in the right spot for that initial read.
So… you end up with fragmented indexes within SQL – well, fragmented everything within SQL. You end up with the SQL files themselves being fragmented on the file system level – and you have to start there. (think all sorts of bits and pieces of SQL files strewn all over the drive like – ‘beverage’ containers)
Find out how big to make your database. Figure out what level of growth you’re going to have. Try to have it set to last as long as you can, a year is good. Once you’ve got that done- the drive will look very full (think huge frat house) but the database will have room to grow (think huge frat house with just a few guys in it – you’ll be able to have huge parties in there (think rebuilding indexes, doing large logged operations you might want to undo, etc.) without waking up the neighbors, without remodeling anything, and without having to wake up cops, the NOC or DBA’s in the middle of the night.
Fix that. Then when you’re done, get your SQL indexes taken care of.
By the time you’re done, your performance will improve tremendously as SQL knows where things are, the OS knows where to look for the things SQL wants it to find, – and you won’t have to deal with any drunken frat boys messing with your SQL server.