The Incredible Shrinking Database: A Quiz

How well do you know how SQL Anywhere works, when it comes to using disk space? Here's a quiz to test your knowledge...

But first, the setup...

The following table shows 4 stages in the history of a SQL Anywhere 12.0.0.2601 database.

• The "File Size" column shows how big the main database file was at each stage, based the FileSize database property.

• The "Free Space In File" column show how much free space existed in the database file, based on FreePages database property.

• The "Disk Space Usage For All Tables" column shows how disk space was allocated for table data, blob extensions and indexes. These numbers were calculated by Foxhound based on data from the SYSTAB and SYSPHYSIDX tables.

• The "Table From Hell" column shows how disk space was allocated for The One Big Table in this database. These numbers were also calculated by Foxhound based on SYSTAB and SYSPHYSIDX.

• Stage 1 is the starting point, with over 3 million rows of data pretty much filling up all 475 megabytes of disk space in the main database file.

• Stage 2 was reached after a series of DELETE statements got rid of almost all of the rows in the Table From Hell, followed by a COMMIT and a CHECKPOINT.

• Stage 3 was reached after the SQL Anywhere server was stopped and restarted.

• Stage 4 was reached after the database was rebuilt via dbunload, dbinit and dbisql.

And now, the Quiz...

1. Why did the File Size more than double from 475M to 1.1G between stages 1 and 2?

2. Why did the Table From Hell's disk space usage only drop from 440M to 368M between stages 1 and 2 even though 97% of the rows were deleted?

   Hint: Don't pay too much attention to the "bytes per row" numbers. They are based on the disk space allocated to the table, not the actual data stored in each row. In other words, no, the rows didn't suddenly change in size, nor did the DELETEs just get rid of short rows.

3. Why did the Free Space In File only grow to 122M in stage 2 even though so much data was deleted?

4. Why did the File Size drop so dramatically between stages 2 and 3?

5. Why did the Free Space In File more than double in size between stages 2 and 3?

6. Why did the disk space allocated to the Table From Hell drop at all between stages 2 and 3?

7. Why is the file still roughly the same size in stage 3 that it was in stage 1, even though the Table From Hell only takes up half as much disk space?

8. Why did the disk space allocated to the Table From Hell drop so dramatically between stages 3 and 4?

9. Why did the File Size drop so dramatically between stages 3 and 4?

10. Why is the Free Space In File so small in stage 4?

Ten questions, ten points each. When I find out what all the answers are, I'll post them.

"WHAT!?!? What do you mean, you don't know all the answers?"

That's right.

And I'll give out 10 Bonus Points if you can point out which questions I'm not sure of.

Hint: I'm pretty sure of numbers 9 and 10.

...and I'm pretty sure the only people who will score 100 work in Engineering. Or 110 :)