StackOverflow’s database servers are hosted in a colo facility. They pay by the byte for transfer in and out of their servers, so it’s really crucial to compress the database backups as much as possible. The smaller the backups, the less they pay. They’re already using backup compression, but they wanted to explore more ways to cut their backup sizes.
There’s two things DBAs can do to deflate their database backups, but both of these have serious implications. The only way this compression process will work is if you’ve got a second database server that can:
- Restore the production backups
- Do some work on them to decrease their size
- Back up the smaller databases
Notice I didn’t use the word “shrink” – nowhere in today’s discussion am I going to shrink the databases. Shrinking databases is evil. Instead, we’re going to do some things to lose the fat and keep the muscle.
Do not try what you’re about to read on your own production server. The only reason we can do these at StackOverflow is because we’ve got a second database server available. These techniques should never be used if you’ve only got one database server, because dropping your indexes and changing to 100% fill factor will both give you horrendous performance.
Consider taking this approach with a dedicated fire drill server. In a perfect world, you’ve got a server lying around that you’re using for fire drills: periodic restores of your databases. The whole reason we do backups is so that we can do restores, and you should be testing your backups as often as possible. In this scenario, we’re actually testing every single one of our backups.
Now, on to the shrin…I mean, compressing!
Drop All Non-Clustered Indexes
There’s two kinds of indexes – clustered, and non-clustered. Clustered indexes aren’t really indexes at all; they define how the table itself is laid out on disk. Dropping a clustered index doesn’t save space. Non-clustered indexes, on the other hand, take up space because they’re additional copies of parts of the table. Non-clustered indexes can help performance, but they can be rebuilt from scratch as long as you save the index definition first.
Which of these takes less space to back up:
- The string “CREATE INDEX IX_EmployeeName ON dbo.Employees (LastName, FirstName)”, or
- An index with thousands or millions of rows in it
The answer is the string. To minimize what we’re backing up, we need to:
- Script out all of the non-clustered indexes in the database
- Save those definitions to a table (or a stored procedure)
- Create a stored proc that will loop through those index definitions and recreate them later
- Drop the indexes
If our database’s content is, say, 30% indexes, we just dropped the size of our backup by 30%. The downside is that after the database is restored somewhere else, it’ll take longer for the database to be fully available, because we have to run the stored proc to recreate all our indexes. If we’re running SQL Server Enterprise Edition, our database will be online while the indexes are recreated.
Here’s a couple of scripts to generate T-SQL scripts for all of the indexes in a database:
To make these work, you’ll want to adapt them so that they insert the scripts into a table for later execution, and then drop the indexes. I know, I know, you’re going to want the script I’m using at StackOverflow, but I’m not quite comfortable publicizing that code just yet. It’s still a little duct-tape-y. I’ll revisit that in the future though.
This index removal task is extremely fast, only a matter of seconds, because SQL Server is just dumping metadata. The next step, however, is much more intensive.
Rebuild Tables with 100% Fill Factor
SQL Server stores data in pages, and you can think of these much like the pages in the phone book. If we tried to fill the pages completely full, then whenever we needed to add a single record, we’d have all kinds of shuffling to do. When we know we’re going to be adding a lot of data, we’re better off leaving some working area on each page so that we can add records without doing a lot of shuffling every time. The default amount of space we use on each page is known as the Fill Factor.
Normally, StackOverflow’s tables wouldn’t have a 100% fill factor because insert performance would be horrible. However, in our backup scenario, we want to cram all of the pages completely full. The solution is to rebuild all of the clustered indexes with a 100% fill factor. The lower your default fill factor, the more space you’ll save by rebuilding to 100% fill factor. This step takes a lot of IO crunching, and again, it only makes sense when you’ve got a second database server available to do all this work.
Our Results: 50-55% Smaller Backups
Cutting backup size in half means:
- Half the bandwidth expense
- Half the time to move backups across the wire for disaster recovery
- Half the long-term storage requirements
Your results will vary depending on your fill factor and your amount of indexes.