When it comes to persisting database data to disk, there is one factor that has a huge impact: Physics. The disk rotates and the heads are moved in and out along the disk to read data. If the heads are in the wrong place they have to be moved in or out. If the data is not under the current head position, it has to wait until the disk comes around with the required position to read, write, or delete. As you write and delete, the data becomes fragmented. Fragmentation means you have more head movement or wait times for the desired data to come into the focus of the heads. So, physics plays a big part in the disk side of database performance.
Additionally, there are different kinds of disk access required in a database server. Transaction logs are generally sequential. New entries are added to the tail of the file. Database Data Files tend to be random access. You read data from different tables on different portions of the disks. So, keeping tables defragmented, and the data files themselves from being fragmented on the OS is essential. Different tables have different access patterns. Tables with blob data may be static. However, interspersing blob data with records can have a bad impact on performance.
The key question you want to ask yourself is, How many records can I read on each spin of the disk. The higher the number, the faster your database.
The last component in contention for all this disk access is tempdb. It likes to be on it’s own spindles. It likes to have multiple data files so different processes can access it concurrently. It likes to have a separate spindle for the tempdb transaction log file, just like any other database, because that is written to sequentially at the tail.
So, you use SQL Server on your laptop with large databases all the time when doing development. You have only one disk, and your performance is really quite good. Why does all of the physics, and knowing about the different disk access types used mean anything at all to me? That’s the topic for tomorrow. Care to share your experience, drop a comment or send an email to btaylor@sswug.org.
Cheers,
Ben